Performing Batch Updates with a DataAdapter 

When updating a database with changes from a DataSet in previous versions of ADO.NET, the Update method of a DataAdapter performed updates to the database one row at a time. As it iterated through the rows in the specified DataTable, it examined each DataRow to see if it had been modified. If the row had been modified, it called the appropriate UpdateCommand, InsertCommand, or DeleteCommand, depending on the value of the RowState property for that row. Every row update involved a network round-trip to the database.

In ADO.NET 2.0, the DataAdapter exposes an UpdateBatchSize property. Setting the UpdateBatchSize to a positive integer value causes updates to the database to be sent as batches of the specified size. For example, setting the UpdateBatchSize to 10 will group 10 separate statements and submit them as single batch. Setting the UpdateBatchSize to 0 will cause the DataAdapter to use the largest batch size that the server can handle. Setting it to 1 disables batch updates, as rows are sent one at a time.

Executing an extremely large batch could decrease performance. Therefore, you should test for the optimum batch size setting before implementing your application.

Using the UpdateBatchSize Property

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

The following procedure demonstrates the use of the UpdateBatchSize property. The procedure takes two arguments, a DataSet object that has columns representing the ProductCategoryID and Name fields in the Production.ProductCategory table, and an integer representing the batch size (the number of rows in the batch). The code creates a new SqlDataAdapter object, setting its UpdateCommand, InsertCommand, and DeleteCommand properties. The code assumes that the DataSet object has modified rows. It sets the UpdateBatchSize property and executes the update.

Public Sub BatchUpdate( _
  ByVal dataTable As DataTable, ByVal batchSize As Int32)
    ' Assumes GetConnectionString() returns a valid connection string.
    Dim connectionString As String = GetConnectionString()

    ' Connect to the AdventureWorks database.
    Using connection As New SqlConnection(connectionString)
        ' Create a SqlDataAdapter.
        Dim adapter As New SqlDataAdapter()

        'Set the UPDATE command and parameters.
        adapter.UpdateCommand = New SqlCommand( _
          "UPDATE Production.ProductCategory SET " _
          & "Name=@Name WHERE ProductCategoryID=@ProdCatID;", _
          connection)
        adapter.UpdateCommand.Parameters.Add("@Name", _
          SqlDbType.NVarChar, 50, "Name")
        adapter.UpdateCommand.Parameters.Add("@ProdCatID",  _
          SqlDbType.Int, 4, " ProductCategoryID ")
        adapter.UpdateCommand.UpdatedRowSource = _
          UpdateRowSource.None

        'Set the INSERT command and parameter.
        adapter.InsertCommand = New SqlCommand( _
          "INSERT INTO Production.ProductCategory (Name) VALUES (@Name);", _
  connection)
        adapter.InsertCommand.Parameters.Add("@Name", _
          SqlDbType.NVarChar, 50, "Name")
        adapter.InsertCommand.UpdatedRowSource = _
          UpdateRowSource.None

        'Set the DELETE command and parameter.
        adapter.DeleteCommand = New SqlCommand( _
          "DELETE FROM Production.ProductCategory " _
          & "WHERE ProductCategoryID=@ProdCatID;", connection)
        adapter.DeleteCommand.Parameters.Add("@ProdCatID", _
           SqlDbType.Int, 4, " ProductCategoryID ")
        adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None

        ' Set the batch size.
        adapter.UpdateBatchSize = batchSize

        ' Execute the update.
        adapter.Update(dataTable)
    End Using
End Sub

public static void BatchUpdate(DataTable dataTable,Int32 batchSize)
{
    // Assumes GetConnectionString() returns a valid connection string.
    string connectionString = GetConnectionString();

    // Connect to the AdventureWorks database.
    using (SqlConnection connection = new 
      SqlConnection(connectionString))
    {

        // Create a SqlDataAdapter.
        SqlDataAdapter adapter = new SqlDataAdapter();

        // Set the UPDATE command and parameters.
        adapter.UpdateCommand = new SqlCommand(
            "UPDATE Production.ProductCategory SET "
            + "Name=@Name WHERE ProductCategoryID=@ProdCatID;", 
            connection);
        adapter.UpdateCommand.Parameters.Add("@Name", 
           SqlDbType.NVarChar, 50, "Name");
        adapter.UpdateCommand.Parameters.Add("@ProdCatID", 
           SqlDbType.Int, 4, "ProductCategoryID");
         adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

        // Set the INSERT command and parameter.
        adapter.InsertCommand = new SqlCommand(
            "INSERT INTO Production.ProductCategory (Name) VALUES (@Name);", 
            connection);
        adapter.InsertCommand.Parameters.Add("@Name", 
          SqlDbType.NVarChar, 50, "Name");
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

        // Set the DELETE command and parameter.
        adapter.DeleteCommand = new SqlCommand(
            "DELETE FROM Production.ProductCategory "
            + "WHERE ProductCategoryID=@ProdCatID;", connection);
        adapter.DeleteCommand.Parameters.Add("@ProdCatID", 
          SqlDbType.Int, 4, "ProductCategoryID");
        adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;

        // Set the batch size.
        adapter.UpdateBatchSize = batchSize;

        // Execute the update.
        adapter.Update(dataTable);
    }
}

See Also

Concepts

Handling Batch Update-Related Events and Errors
Updating Data Sources with DataAdapters

Other Resources

Performing Batch Operations Using DataAdapters