"BatchUpdate in Ado.net Samples and examples

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;
 adpt.Update("Employee");

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.

Post Comment:

Notify me by email when others post comments to this article.

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:	Sign In to fill automatically.
Email:	(Will not be published, but required to validate comment)
	Type the numbers and letters shown on the left.