UPDATE and INSERT operations use values extracted from the Order business entity. The test regimen runs four query modes. This first mode example is a typical nonparameterized T-SQL batch statement for UPDATE operations that replace all existing data for an order:

SET XACT_ABORT ON 
SET NOCOUNT ON 
BEGIN TRAN 
UPDATE Orders SET CustomerID = 'MEREP', 
   EmployeeID = 2, OrderDate = 
   '10/28/1996', 
   RequiredDate = '11/25/1996', 
   ShippedDate = '11/25/1996', ShipVia 
      = 2, 
   Freight = 15.66, ShipName = 'Mère 
      Paillarde', 
   ShipAddress = '43 rue St. Laurent', 
   ShipCity = 'Montréal', ShipRegion = 
      'Québec', 
   ShipPostalCode = 'H1J 1C3', 
   ShipCountry = 'Canada' 
   WHERE OrderID = 234330 
DELETE FROM [Order Details] WHERE 
   OrderID = 234330 
INSERT [Order Details] 
   VALUES(234330, 4, 17.6, 10, 0) 
INSERT [Order Details] 
   VALUES(234330, 17, 31.2, 70, 0.05) 
INSERT [Order Details] 
   VALUES(234330, 62, 39.4, 28, 0) 
COMMIT TRAN
SET XACT_ABORT OFF
SET NOCOUNT OFF

SQL Server generates a new query plan for each execution of the preceding statement unless the plan is cached. If you use a small number of test orders—say, 100 or so—caching obscures the substantial performance hit that regenerating query plans causes.

The SQLClient.SQLCommand object supports named parameters for T-SQL, which are much less prone to coding errors than OLE DB's sequential ? parameter value substitution method. IDENT_CURRENT('TableName') is the key to retrieving the new identity value of the Orders.OrderID value. This is the T-SQL INSERT statement with named parameters:

SET XACT_ABORT ON 
SET NOCOUNT ON 
BEGIN TRAN 
INSERT Orders (CustomerID, EmployeeID, 
   OrderDate, RequiredDate, 
      ShippedDate, 
   ShipVia, Freight, ShipName, 
      ShipAddress, 
   ShipCity, ShipRegion, 
      ShipPostalCode, 
   ShipCountry) 
VALUES(@CustomerID, @EmployeeID, 
   @OrderDate, @RequiredDate, @ShippedDate, 
   @ShipVia, @Freight, @ShipName, 
   @ShipAddress, @ShipCity, 
      @ShipRegion, 
   @ShipPostalCode, @ShipCountry) 
INSERT [Order Details] 
VALUES(IDENT_CURRENT('Orders'), 
   @ProductID0, @UnitPrice0, 
   @Quantity0, @Discount0) 
INSERT [Order Details] 
VALUES(IDENT_CURRENT('Orders'), 
   @ProductID1, @UnitPrice1, 
   @Quantity1, @Discount1) 
INSERT [Order Details] 
VALUES(IDENT_CURRENT('Orders'), 
   @ProductID2, @UnitPrice2, 
   @Quantity2, @Discount2) 
COMMIT TRAN
SET XACT_ABORT OFF
SET NOCOUNT OFF

Most DBAs demand stored procedures for table UPDATEs and INSERTs, but parameterized batch statements demonstrate slightly better performance with UPDATE operations and don't require deployment across multiple servers. Table consistency dictates executing a stored procedure for each INSERT operation within the scope of an SQLClient.SQLTransaction object—until a future version of SQL Server supports array parameters, which Microsoft promises for the Yukon release. The stored procedure for inserting an Orders row returns the IDENT_CURRENT value.