ADO.NET 3.5 Cookbook, 2nd Edition

10.18. Performing Batch Updates with a DataAdapter

Problem

When you use a DataAdapter to perform updates, it makes a separate round trip to the server for each row. You want to batch all of the updates into a single call to the server to improve performance.

Solution

In ADO.NET 1.0, a DataAdapter could only submit one pending change to a database at a time. ADO.NET 2.0 added support for sending batch updates using either a SqlDataAdapter or an OracleDataAdapter; as of ADO.NET 3.5, batch updates are not supported using the OleDbDataAdapter and the OdbcDataAdapter. Two solutions are demonstrated: one that uses the batch update capabilities of the DataAdapter introduced in ADO.NET 2.0 and another that is a custom approach. The custom approach can be used with ADO.NET 1.0 and with .NET data providers that do not support DataAdapter batch updates such as the OLE DB and ODBC data providers.

The first solution uses the batch update capabilities introduced in ADO.NET 2.0 to the DataAdapter class in both the SQL Server and Oracle data providers.

The solution uses a table named BatchUpdateAuto in the database AdoDotNet35Cookbook. The following T-SQL statement creates the table:

	USE AdoDotNet35Cookbook
	GO
	CREATE TABLE BatchUpdateAuto(
	    Id int NOT NULL PRIMARY KEY,
	    Field1 nvarchar(50) NULL,
	    Field2 nvarchar(50) NULL )

Add three records to the BatchUpdateAuto table by executing the following T-SQL batch:

 USE AdoDotNet35Cookbook GO INSERT INTO BatchUpdateAuto VALUES (1, 'Field1.1', 'Field2.1'); INSERT ...