Batch support in ADO.NET 2.0 allows a DataAdapter to group insert/update/delete operations from a DataSet or DataTable to the server, instead of sending one operation at a time. The reduction of round trips to the server usually results in major performance gains. Batch updates are supported for the .NET data providers for SQL Server (SqlClient) and Oracle (OracleClient).
-
DataAdapter and Batch Updates. In the previous version of ADO.NET, when updating a database with changes from a DataSet, the Update method of a DataAdapter performs updates to the database one row at a time.
As the method iterates through the rows in the specified DataTable, it examines each DataRow to see if the row has been modified. If the row has been modified, the method calls the appropriate UpdateCommand, InsertCommand or DeleteCommand, depending on the value of the RowState property for that row. Every row update involves a network round-trip to the database.
In ADO.NET 2.0, the DataAdapter exposes an UpdateBatchSize property. Setting the property to a positive integer value causes updates to the database to be sent as batches of the specified size. For example, setting the UpdateBatchSize to 10 will group 10 separate statements and submit them as a single batch. Setting the UpdateBatchSize to 0 will cause the DataAdapter to use the largest batch size that the server can handle. Setting it to 1 disables batch updates as rows are sent one at a time.
-
Event Behavior Changes with Batch Updates. The DataAdapter class has two update-related events i.e. RowUpdating and RowUpdated. In previous versions of ADO.NET, and when batch processing is disabled, each of these events is generated once for each row processed. RowUpdating is generated before the update occurs, and RowUpdated is generated after the database update is completed.
When batch processing is enabled, multiple rows are updated in a single database operation. Therefore, only one RowUpdated event occurs for each batch, whereas the RowUpdating event occurs for each row processed.
When batch processing is disabled, the two events are fired with one-to-one interleaving where one RowUpdating event and one RowUpdated event fire for a row, then one RowUpdating and one RowUpdated event fire for the next row, until all of the rows are processed.
-
Accessing Updated Rows. When batch processing is disabled, the row being updated can be accessed using the Row property of the RowUpdatedEventArgs class. When batch processing is enabled, a single RowUpdated event is generated for multiple rows.
-
Handling Data Errors. Batch execution has the same effect as the execution of each statement. Statements are executed in the order that the statements were added to the batch. Errors are handled in batch mode as they are, when batch mode is disabled. Each row is processed separately. Only rows that have been successfully processed in the database will be updated in the corresponding DataRow within the DataTable.
The data provider and the back-end database server determine which SQL constructs are supported for batch execution. An exception may be thrown if a non-supported statement is submitted for execution.
From:Microsoft