Performing batch updates using S
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.
