DataAdapter Instance code to perform batch update

This article describes the DataAdapter to perform batch update of the instance code, the need for friends can refer to, I hope to help you  

In previous versions of Ado.net, when the database was updated with changes in the DataSet, the DataAdapter Update method updates one row of the database at a time. Because the method iterates through the rows in the specified DataTable, each DataRow is checked to determine if it has been modified. If the row has been modified, the corresponding UpdateCommand, InsertCommand, or DeleteCommand will be invoked based on the RowState property value of the row. Each row update involves two-way data transfer between the network and the database.
    in Ado.net 2.0, DataAdapter exposes the UpdateBatchSize property. Setting the updatebatchsize to a positive integer value causes the update of the database to be sent in batches of the specified size. For example, if you set UpdateBatchSize to 10, 10 separate statements are grouped together and submitted as a batch. Setting UpdateBatchSize to 0 will cause DataAdapter to use the largest batch size the server can handle. If you set it to 1, the bulk update is disabled because one row is sent at a time.
    performing very large batches can degrade performance. Therefore, you should test the best batch size setting before implementing your application.
    using the UpdateBatchSize property
    When batch updates are enabled, DataAdapter UpdateCommand, The UpdatedRowSource property values for InsertCommand and DeleteCommand should be set to None or OutputParameters. The UpdatedRowSource property value for the FirstReturnedRecord or Both of the command is invalid when a bulk update is performed.
    The following procedure demonstrates how to use the UpdateBatchSize property. The procedure takes two parameters, a DataSet object that contains a column representing the ProductCategoryID and Name fields in the Production.productcategory table, an integer representing the batch size (number of rows in the batch). The code creates a new Sqldataadapter object to set its UpdateCommand, InsertCommand, and DeleteCommand properties. The code assumes that the DataSet object has modified the row. It sets the UpdateBatchSize property and performs the update.

Copy Code code as follows:

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 the


//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 * 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, "address");


SqlParameter Cityparam;


Cityparam = new SqlParameter ("@City", SqlDbType.VarChar, "City");


SqlParameter Regionparam;


Regionparam = new SqlParameter ("@Region", SqlDbType.VarChar, "Region");


SqlParameter Countryparam;


Countryparam = new SqlParameter ("@Country",


SqlDbType.VarChar, "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 is used to retrieve all employee


//information from the Northwind database and the Update command


//would be used to save changes 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 would 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 of


//Updated records since batch updates are incapable of


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


    }