Improve performance using ADO.NET 2.0 batch update feature

Introduction

When you use SqlDataAdapter for performing updates, the SqlDataAdapter propagates the updates one by one. That means if there are 100 rows to be updated the SqlDataAdapter will execute 100 separate operations against the database. As you might have guessed this is not efficient while dealing with large number of rows. Fortunately SqlDataAdapter allows you to execute updates in batches. You can specify the batch size i.e. number of rows to be treated as a single batch via UpdateBatchSize property.

There are also two events associated with this that you can use � RowUpdating and RowUpdated. In normal situations i.e. in the absence of batch update, both of these events are raised for each and every row being updated. For example, if we are updating 100 rows, both of these rows are raised for 100 times. However, when you turn on the batch update behavior, the RowUpdating event is raised as in previous case but RowUpdated event is raised after the entire batch is updated. If we set the batch size to 10 then RowUpdating will be raised for 100 times where as RowUpdated will be raised only for 10 times.

Following code illustrates use of this feature:

class Program
{
private static int updating = 0;
private static int updated = 0;
static void Main(string[] args)
{
SqlConnection cnn = new SqlConnection(
"data source=.\\sqlexpress;
initial catalog=northwind;integrated security=true");
SqlDataAdapter da = new 
SqlDataAdapter("select * from customers", cnn);
da.RowUpdating += new 
SqlRowUpdatingEventHandler(da_RowUpdating);
da.RowUpdated += new 
SqlRowUpdatedEventHandler(da_RowUpdated);
DataSet ds = new DataSet();
da.Fill(ds, "mycustomers");
foreach (DataRow row in ds.Tables[0].Rows)
{
string country = row["country"].ToString();
//simulate row change
row["country"] = country;
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update customers set 
country=@country where customerid=@custid";
SqlParameter p1 = new 
SqlParameter("@country", SqlDbType.VarChar);
p1.SourceColumn = "country";
SqlParameter p2 = new 
SqlParameter("@custid", SqlDbType.VarChar);
p2.SourceColumn = "customerid";
cmd.Parameters.Insert(0, p1);
cmd.Parameters.Insert(0, p2);
cmd.UpdatedRowSource = UpdateRowSource.None;
da.UpdateCommand = cmd;
da.UpdateBatchSize = 10;
da.Update(ds, "mycustomers");
Console.WriteLine("Updating :" + updating);
Console.WriteLine("Updated :" + updated);
Console.ReadLine();
}
static void da_RowUpdating
(object sender, SqlRowUpdatingEventArgs e)
{
updating++;
}
static void da_RowUpdated
(object sender, SqlRowUpdatedEventArgs e)
{
updated++;
}
}

Notice how we have attached event handlers to the RowUpdating and RowUpdated events. These event handlers simply increment two integer variables. Then we set the UpdatedDataSource property of the SqlCommand property to enumerated value of UpdateRowSource.None. The UpdatedRowSource property can control how the values returned from the data source are mapped back to the DataSet. Setting this property to a value of None is necessary when using batch update feature of DataAdapter. Finally, we set the UpdateBatchSize property of the SqlDataAdapter to 10 indicating that we want to update 10 rows as a batch. If you run this application you will notice that the variable updating holds the value equal to the number of rows in the table because this event is raised each time a row is being updated. On the other hand the variable update will hold value equal to (number of rows in the table) / (batch size).

Summary

In ADO.NET 2.0, the DataAdapter provides a property called UpdateBatchSize which allows you to execute queries in a batch. This significantly reduces the database roundtrips and hence is more efficient in terms of performance.