"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;
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.


Comments

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