"BatchUpdate in Ado.net

In this we see what is BatchUpdate in Ado.net . Why it is used and how we can use it.

In the previous version of Ado.net DataAdapter sent command to the database one at a time , so if we want to update
large number of rows suppose 100 then DataAdapter would execute 50 separate operations against database. This decreases
the performance.

Ado.net provides a feature called Batch Update which can improve the performance of the data access layer by
reducing the number of roundtrips to the database. It can perform update for a set of row at a time.

For this DataAdapter has a property UpdateBatchSize. Setting this property a positive number causes update to the database
to be sent as batches of the specified size.

string connectionstring = "server=dell;initial catalog = employeedetail;uid = xyz;pwd= xyz";
SqlConnection con = new SqlConnection(connectionstring);
SqlDataAdapter adpt = new SqlDataAdapter("select * from Employee", con);
DataSet ds = new DataSet();
adpt.Fill(ds, "Employee");
SqlcommandBuilder cmd = new SqlcommandBuilder(adpt);
adpt.UpdateBatchSize = 5;

In this example we have set the BatchSize = 5 means at a time 5 rows can be updated.

Important Points:
1. Setting UpdateBatchSize= 1,disables batch updates.
2. Setting UpdateBatchSize= x,where x>1, sends x statement to the database at a time.
3. Setting UpdateBatchSize= 0,sends the maximum number of statements at a time alloted by server.


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: