Shulou(Shulou.com)06/03 Report--

This article is about how to use DataAdapter to perform batch updates. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

In previous versions of ADO.NET, when the database was updated with changes in DataSet, DataAdapter's Update method updated one row of the database at a time. Because this 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 appropriate UpdateCommand, InsertCommand, or DeleteCommand is called based on the value of the RowState property 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 attribute. Setting UpdateBatchSize to a positive integer value causes updates to the database to be sent in batches of the specified size. For example, if UpdateBatchSize is set to 10, 10 separate statements are grouped together and submitted as a batch. Setting UpdateBatchSize to 0 causes DataAdapter to use the maximum batch size that the server can handle. If it is set to 1, batch updates are disabled because one row at a time is sent.

Executing very large batches may degrade performance. Therefore, you should test the best batch size setting before implementing the application.

Use the UpdateBatchSize property

When batch updates are enabled, the UpdatedRowSource property values for UpdateCommand, InsertCommand, and DeleteCommand for DataAdapter should be set to None or OutputParameters. The UpdatedRowSource property value of the command's FirstReturnedRecord or Both is not valid when performing a batch update.

The following procedure demonstrates how to use the UpdateBatchSize property. This procedure takes two parameters, a DataSet object that contains columns that represent the ProductCategoryID and Name fields in the PRoduction.ProductCategory table, and an integer that represents the batch size (the number of rows in the batch). This code creates a new SqlDataAdapter object and sets its UpdateCommand, InsertCommand, and DeleteCommand properties. This code assumes that the DataSet object has modified the row. It sets the UpdateBatchSize property and performs the update.

The copy code is 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 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 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 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 + "

"

}

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 () + "

"

}

Thank you for reading! This is the end of this article on "how to use DataAdapter to perform batch updates". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.