| [ Team LiB ] |
|
Recipe 9.13 Performing Batch Updates with a DataAdapterProblemWhen 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. SolutionUse 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:
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;
}
DiscussionWhen 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.
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.
To batch the update commands generated by the DataAdapter, the solution does the following in the RowUpdating event handler for each row updated:
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. |
| [ Team LiB ] |
|