ADO.NET Cookbook

9.13. 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

Use the RowUpdating event raised by the DataAdapter to build a single batched SQL statement that gets executed using the ExecuteNonQuery( ) method.

The sample code contains three event handlers:

Form.Load

Sets up the sample by creating a DataAdapter based on a SELECT statement of CategoryID, CategoryName, and Description fields of the Categories table in the Northwind database. A CommandBuilder is created to supply updating logic. A method is attached to the RowUpdating event of the DataAdapter. A new table is created and filled with the schema and data from the Categories table from the Northwind database. The properties of the AutoIncrement CategoryID field are set up. Finally, the default view of the table is bound to the data grid on the form.

Update Button.Click

Calls the Update( ) method of the DataAdapter. The DataAdapter.RowUpdating handler (described next) builds a batch SQL update string, which is executed using the ExecuteScalar( ) method after Update( ) is called.

DataAdapter.RowUpdating

Is called before each row is updated by the DataAdapter. The SQL command to be used to update the row by the DataAdapter is retrieved from the CommandText property of the Command object. The parameters for the Command are iterated ...