ADO. NET batch update

ADO. NET batch update

Batch update operations

In the previous version of ADO. NET, the Update method of SqlDataAdapterde will call an Update operation for each row in the DataSet.

. In ADO. NET2.0, you can set the UpdateBatchSize attribute and execute multiple updates in a single step.

In this way, the efficiency of data update can be improved.

The default value of. UpdataBatchSize is 1, so that the default update behavior is consistent with that of the previous version of ADO. NET.

Code experience

Public Form1 ()

{

Conn = new SqlConnection (ConfigurationManager. ConnectionStrings ["AWConnectionString"]. ConnectionString );

DAdapt = new SqlDataAdapter ("SELECT ProductID, Name, ListPrice FROM Production. Product", conn );

InitializeComponent ();

}

SqlConnection conn;

SqlDataAdapter dAdapt;

DataSet dSet = new DataSet ();

StringBuilder logString = new StringBuilder ("");

Private void batchUpdateForm_Load (System. Object sender, System. EventArgs e)

{

DAdapt. RowUpdating + = new System. Data. SqlClient. SqlRowUpdatingEventHandler (OnRowUpdating );

DAdapt. RowUpdated + = new System. Data. SqlClient. SqlRowUpdatedEventHandler (OnRowUpdated );

}

Private void getDataButton_Click (System. Object sender, System. EventArgs e)

{

DAdapt. Fill (dSet, "Product ");

ProductGrid. DataSource = dSet. Tables ["Product"];

}

Private void updateDataButton_Click (System. Object sender, System. EventArgs e)

{

SqlCommandBuilder cb = new SqlCommandBuilder (dAdapt );

LogString. Remove (0, logString. Length );

// Enable batching by setting batch size! = 1.

DAdapt. UpdateBatchSize = int. Parse (batchSizeTextBox. Text );

// Execute the update.

DAdapt. Update (dSet. Tables ["Product"]);

MessageBox. Show (logString. ToString ());

}

// Handler for the RowUpdating event

Public void OnRowUpdating (object sender, SqlRowUpdatingEventArgs e)

{

LogString. AppendLine ("Starting row update ");

}

// Handler for RowUpdated event

Public void OnRowUpdated (object sender, SqlRowUpdatedEventArgs e)

{

LogString. AppendLine ("Completed row update ");

}