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 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 UpdateStatus.SkipCurrent 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 9-16.

Example 9-16. File: CustomAdapterBatchUpdateForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Text; using System.Data; using System.Data.SqlClient; private const String CATEGORIES_TABLE = "Categories"; private const String CATEGORYID_FIELD = "CategoryID"; private DataTable dt; private SqlDataAdapter da; private SqlCommandBuilder cb; private StringBuilder sb; // . . . private void CustomAdapterBatchUpdateForm_Load(object sender, System.EventArgs e) { String sqlText = "SELECT CategoryID, CategoryName, Description " + "FROM Categories"; // Fill the categories table for editing. da = new SqlDataAdapter(sqlText, ConfigurationSettings.AppSettings["Sql_ConnectString"]); // CommandBuilder supplies updating logic. cb = new SqlCommandBuilder(da); // Handle the RowUpdating event to batch the update. da.RowUpdating += new SqlRowUpdatingEventHandler(da_RowUpdating); // Create table and fill with orders schema and data. dt = new DataTable(CATEGORIES_TABLE); da.FillSchema(dt, SchemaType.Source); // Set up the autoincrement column. dt.Columns[CATEGORYID_FIELD].AutoIncrementSeed = -1; dt.Columns[CATEGORYID_FIELD].AutoIncrementStep = -1; // Fill the DataSet. da.Fill(dt); // Bind the default view of the table to the grid. dataGrid.DataSource = dt.DefaultView; } private void updateButton_Click(object sender, System.EventArgs e) { // Create a new SQL statement for all updates. sb = new StringBuilder( ); // Update the data source. da.Update(dt); if(sb.Length > 0) { // Create a connection command with the aggregate update command. SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings["Sql_ConnectString"]); SqlCommand cmd = new SqlCommand(sb.ToString( ), conn); // Execute the update command. conn.Open( ); cmd.ExecuteScalar( ); conn.Close( ); // Refresh the DataTable. dt.Clear( ); da.Fill(dt); } } private void da_RowUpdating(object sender, SqlRowUpdatingEventArgs e) { // Get the command for the current row update. StringBuilder sqlText = new StringBuilder(e.Command.CommandText.ToString( )); // Replace the parameters with values. for(int i = e.Command.Parameters.Count - 1; i >= 0; i--) { SqlParameter parm = e.Command.Parameters[i]; if(parm.SqlDbType == SqlDbType.NVarChar parm.SqlDbType == SqlDbType.NText) // Quotes around the CategoryName and Description fields sqlText.Replace(parm.ParameterName, "'" + parm.Value.ToString( ) + "'"); else sqlText.Replace(parm.ParameterName, parm.Value.ToString( )); } // Add the row command to the aggregate update command. sb.Append(sqlText.ToString( ) + ";"); // Skip the DataAdapter update of the row. e.Status = UpdateStatus.SkipCurrentRow; }

Discussion

When a DataAdapter is used to update the data source with changes made to disconnected data in a DataSet or DataTable , a RowUpdating event is raised before the command to update each changed row executes. The event handler receives the SqlRowUpdatingEventArgs argument containing information about the event. Table 9-5 lists the properties of SqlRowUpdatingEventArgs used to access information specific to the event.

Table 9-5. SqlRowUpdatingEventArgs properties

The UpdateStatus is set to ErrorsOccurred when an error occurs while updating a row; otherwise it is set to Continue . UpdateStatus can be used to specify what to do with the current and remaining rows during an update. Table 9-6 describes the UpdateStatus enumeration values.

Table 9-6. UpdateStatus enumeration values

To batch the update commands generated by the DataAdapter , the solution does the following in the RowUpdating event handler for each row updated:

• Gets the CommandText that will be used to update the row in the data source.
• Replaces the parameters in the CommandText with the parameter values applying required delimiters to each value. Appends the result to the batch command text.
• Sets the UpdateStatus of the Command to SkipCurrentRow so that the update for the row is not performed.

Once all of the rows have been processed, execute the assembled batch command text against the data source using the ExecuteScalar( ) method of a Command object.

The solution delimits the string values for the CategoryName and Description fields in the Categories table from the Northwind database used in this example. Ensure that strings, dates, and any other fields are properly delimited when values are substituted for parameter names in the DataAdapter.RowUpdating event handler. Delimit column and table names as well, if necessary.

Although this solution uses the CommandBuilder to generate the updating logic for the DataAdapter , the solution remains fundamentally the same if you use your own custom updating logic. One thing to keep in mind: the solution code iterates in reverse order through the parameters collection so that parameters are replaced correctly if there are more than nine parameters; if they were processed in forward order, parameter @p1 would cause the replacement for parameter @p10 , @p11 , and so on. When using custom updating logic, consider the potential problems that might occur if one parameter name is the start of another parameter name when replacing the parameters with the values in the DataRow.RowUpdating event handler.

Ensure that you set the AutoIncrementSeed and AutoIncrementStep properties prior to filling the DataSet ; otherwise, the seed will be incorrect starting at one less than the largest AutoIncrement field value retrieved from the database.