June 13, 2011, 3:54 a.m.
posted by pitbull
The New Batched Update Features
The bulk copy classes discussed in the previous section provide the most efficient way to load a set of data into SQL Server. A related capability added to ADO.NET 2.0 enables more efficient updating of data from a DataSet (via a DataAdapter) by batching inserts, updates, and deletes.
Using batched updates can provide huge improvements in performance because they require only one round-trip to the database to perform all the updates in the batch. Without this feature, each update is sent to the database individually.
In this section of this chapter we look at the new functionality implemented by the DataAdapter to support batched updates. The DataAdapter, which is used to submit updates from a DataSet, exposes a property named UpdateBatchSize. As you'll guess from the name, this property sets the number of commands that will be executed as a single batch when the Update method is executed.
Obviously, this relies on the target database or data source supporting batch command execution. If it does not, the commands can only be executed one at a time. All the classes in ADO.NET that support batch execution will take advantage of this feature where the database supports it. However, if it is not supported by the target data source, they silently degrade and use single command execution—without requiring any modification to the code or producing any change in the results visible to the user.
Batch Command Execution with a DataSet and DataAdapter
To demonstrate batch command execution in its simplest form, we look first at the way it works with a DataSet and DataAdapter. The DataAdapter uses Command objects that are assigned to the UpdateCommand, Insert Command, and DeleteCommand properties to perform the updates in the database. All that's required to change the behavior of these Command objects to use batched updates is to set the DataAdapter object's UpdateBatchSize property.
By default, this property is set to 1, giving behavior that is the same as in ADO.NET version 1.x. Each update is sent to the database individually. If the UpdateBatchSize property is set to 0, the DataAdapter will instruct the Command objects to execute all the updates for the current Update method call as a single batch. Other positive values specify the number of commands to execute as a batch before starting a new batch.
Using the DataAdapter.UpdateBatchSize Property
The results of changing the UpdateBatchSize value can be seen in the example page we provide with all the samples you can download for this book. It loads some rows from the database into a table in a DataSet, changes some of the values in these rows, and then displays the rows. Then it calls the Update method of the DataAdapter to push the changes back into the database. The default, when the UpdateBatchSize property is set to 1, is shown in Figure.
In order to show how the statements are being sent in a batch, our example code handles the RowUpdated event of the DataAdapter. Each time this event is raised as the Update method is executing, it displays details of the event in a Label control at the bottom of the page in Figure. When the value of UpdateBatchSize is 1 (the default), the RowUpdated event is raised as the UpdateCommand for each row is executed.
Figure shows that setting the UpdateBatchSize property to 0 causes the RowUpdated event to be called only once, when the complete batch has been executed to update all the rows. Details of the row being updated are no longer available—the properties of the "argument instance" sent to the event handler contain only the number of rows affected. Meanwhile, setting the UpdateBatchSize property to an intermediate value causes the updates to be executed in more than one batch.
The Code for the Example Page
Much of the code in the example page uses standard and straightforward ADO.NET techniques to connect to a database, extract some rows, and change some values in these rows. To make it easier to see what is actually relevant, that code isn't listed here.
The important step in the process comes next (see Listing 2.10). The current setting of the option buttons at the top of the page is converted to an Integer and assigned to the UpdateBatchSize property of the DataAdapter. Then the code adds the event handler named OnRowUpdated to the DataAdapter and calls the Update method (using a CommandBuilder to create the update statements). This process is repeated each time the page is loaded in response to clicking the Go button on the page, which simply resubmits the page.
... ' set the batch size for the updates from ' the selected option button oDA.UpdateBatchSize = Decimal.Parse(optMaxRows.SelectedValue) ' add event handlers for Updated events AddHandler oDA.RowUpdated, _ New SqlRowUpdatedEventHandler(AddressOf OnRowUpdated) ' create an auto-generated command builder that will ' build commands to update, insert, and delete rows Dim oCmdBuilder As New SqlCommandBuilder(oDA) ' push changed rows back into database oDA.Update(oDataSet, "Orders")
The event handler named OnRowUpdated is shown in Listing 2.11. It attempts to read and display the value of the OrderID column in the current row, then displays the number of rows affected by the update. When the UpdateBatchSize property is set to a value other than 1, the number of rows affected reflects the number of rows in the batch.
If the event has been fired after all the rows have been updated, at the end of the batch update, there is no row available. The Try..Catch construct prevents the exception that is raised from halting execution.
Sub OnRowUpdated(oSender As Object, oArgs As SqlRowUpdatedEventArgs) Try Label1.Text &= "RowUpdated event fired for row " _ & oArgs.Row("OrderID") & " " Catch End Try Label1.Text &= "Updated " & oArgs.RecordsAffected.ToString() _ & " row(s)<br />" End Sub
Viewing the Batch Execution
There is another way to see exactly what is going on when the DataSet (or any other class for that matter) interacts with SQL Server. Open Profiler from the SQL Server section in your Start menu, or from the Tools menu in Enterprise Manager. Select File | New | Trace to start a new trace, and in the Events tab of the Properties for the new trace select the Transactions and Stored Procedures sections. Then run your code, and you'll see the SQL statement that is being generated appear each time.
When the example page in Figure is executed with the batch size set to 1, each update can be seen executing as a separate process within the sp_executesql stored procedure that SQL Server uses to execute a SQL statement (see Figure).
However, as you can see in Figure, setting the UpdateBatchSize to a value greater than the number of rows that have been changed results in a single execution of the sp_executesql stored procedure, with all the updates applied in one batch.