Use DataAdapter to Perform Batch update

In earlier versions of ADO. NET, when you use changes in DataSet to Update a database, the DataAdapter Update method updates a row of the database each time. Because this method cyclically accesses the rows in the specified able, it checks Each DataRow and determines whether it has been modified. If the row has been modified, the corresponding UpdateCommand, InsertCommand, or DeleteCommand will be called Based on the RowState attribute value of the row. Each row update involves two-way data transmission between the network and the database.
In ADO. NET 2.0, DataAdapter discloses the UpdateBatchSize attribute. Setting UpdateBatchSize to a positive integer will send database updates in batches of the specified size. For example, if you set UpdateBatchSize to 10, 10 independent statements are combined and submitted as a batch. Setting UpdateBatchSize to 0 will cause the DataAdapter to use the maximum batch size that the server can process. If this parameter is set to 1, batch update is disabled because one row is sent each time.
Executing a very large batch may reduce the performance. Therefore, the best batch size setting should be tested before the application is implemented.
Use the UpdateBatchSize attribute
After batch update is enabled, the UpdatedRowSource attribute values of DataAdapter's UpdateCommand, InsertCommand, and DeleteCommand should be set to None or OutputParameters. When batch update is executed, the value of the FirstReturnedRecord or Both UpdatedRowSource attribute of the command is invalid.
The following procedure demonstrates how to use the UpdateBatchSize attribute. This process uses two parameters, one DataSet object, which contains columns representing the ProductCategoryID and Name fields in the PRoduction. ProductCategory table, and one integer representing the batch size (number of rows in the batch ). This Code creates a new SqlDataAdapter object and sets its UpdateCommand, InsertCommand, and DeleteCommand attributes. This Code assumes that the DataSet object has modified rows. It sets the UpdateBatchSize attribute and performs an update.
Protected void btnUpdateAddress_Click (object sender, EventArgs e)
{
SqlDataAdapter EmpAdapter = new SqlDataAdapter ();
DataTable EmpDT = new DataTable ();
SqlConnection DBConSelect = new SqlConnection ();
SqlConnection DBConUpdate = new SqlConnection ();
SqlCommand SelectCommand = new SqlCommand ();
SqlCommand UpdateCommand = new SqlCommand ();
// Using different connection objects for select and updates from
// Northwind database.
DBConSelect. ConnectionString =
ConfigurationManager. ConnectionStrings ["DSN_NorthWind"]. ConnectionString;
DBConUpdate. ConnectionString =
ConfigurationManager. ConnectionStrings ["DSN_NorthWind"]. ConnectionString;
// Reading all records from the Employees table
SelectCommand. CommandText = "SELECT top 500 * from employees ";
SelectCommand. CommandType = CommandType. Text;
SelectCommand. Connection = DBConSelect;

UpdateCommand. CommandText = "update employees set Address = @ Address," +
"City = @ City, Region = @ Region, Country = @ Country ";
UpdateCommand. CommandType = CommandType. Text;
UpdateCommand. Connection = DBConUpdate;
SqlParameter AddressParam;
AddressParam = new SqlParameter ("@ Address ",
SqlDbType. VarChar, 15, "Address ");
SqlParameter CityParam;
CityParam = new SqlParameter ("@ City", SqlDbType. VarChar, 15, "City ");
SqlParameter RegionParam;
RegionParam = new SqlParameter ("@ Region", SqlDbType. VarChar, 15, "Region ");
SqlParameter CountryParam;
CountryParam = new SqlParameter ("@ Country ",
SqlDbType. VarChar, 15, "Country ");
UpdateCommand. Parameters. Add (AddressParam );
UpdateCommand. Parameters. Add (CityParam );
UpdateCommand. Parameters. Add (RegionParam );
UpdateCommand. Parameters. Add (CountryParam );
// Setting up Data Adapter with the Select and Update Commands
// The Select command will be used to retrieve all employee
// Information from the Northwind database and the Update command
// Will be used to save changes back to the database
EmpAdapter. SelectCommand = SelectCommand;
EmpAdapter. UpdateCommand = UpdateCommand;
EmpAdapter. Fill (EmpDT );
DBConSelect. Close ();
// Looping through all employee records and assigning them the new
// Address
Foreach (DataRow DR in EmpDT. Rows)
{
DR ["Address"] = "4445 W 77th Street, Suite 140 ";
DR ["City"] = "Edina ";
DR ["Region"] = "Minnesota ";
DR ["Country"] = "USA ";
}
// Adding an event handler to listen to the RowUpdated event.
// This event will fire after each batch is executed
EmpAdapter. RowUpdated + = new SqlRowUpdatedEventHandler (OnRowUpdated );
LblCounter. Text = "";
EmpAdapter. UpdateBatchSize = 100;
// It is important to set this property for batch processing
// Updated records since batch updates are incapable
// Updating the source with changes from the database
UpdateCommand. UpdatedRowSource = UpdateRowSource. None;
Try
{
DBConUpdate. Open ();
EmpAdapter. Update (EmpDT );
}
Catch (Exception ex)
{
LblCounter. Text + = ex. Message + "<Br> ";
}
Finally
{
If (DBConUpdate. State = ConnectionState. Open)
{
DBConUpdate. Close ();
}
}
}
Private void OnRowUpdated (object sender, SqlRowUpdatedEventArgs args)
{
LblCounter. Text + = "Batch is processed till row number =" +
Args. RowCount. ToString () + "<br> ";
}