[ Team LiB ] Recipe 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. | Team LiB Recipe 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 Sets up the sample by creating a DataAdapter based on a SELECT statement of CategorylD 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 CategorylD field are set up. Finally the default view of the table is bound to the data grid on the form. Update Calls the Update method of the DataAdapter. The handler described next builds a batch SQL update string which is executed using the ExecuteScalar method after Update is called. 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 over and each parameter variable in the update statement is replaced with the value for that parameter. Single quote delimiters are added around the string values. Finally the statement is added to a StringBuilder object and the Status property of the Command is set to row so that the data source is not updated by the DataAdapter. Instead the update is performed by executing the batch SQL statement created by this event handler. The C code is shown in Example .
