Multiple Inserts in Single Round trip using ADO.NET Batch Update

Valid for Environment: .NET 2.0 (ADO.NET 2.0), SQL Server 2000 or above

Very often we come across a scenario where we need to execute same Insert or update commands with different values multiple times in one go. Say for example, show multiple records in updatable grid/tablular format allowing user to update them and then update them in database. Their are multiple ways to handle it and simplest being execute each DML command one after the other. The most resource consuming part of it is establishing connection for each DML command. Well, connection pooling helps a bit, but still one needs to request for connection from the connection pool. For details about connection pooling, refer to the article ADO.NET Connection Pooling at a Glance . Best solution in such a situation would be to establish the connection only once and perform multiple insert/update within it, and this is what is the target of this article. Let us see couple of mechanisms in which we can do this.

It was quite a cumbersome job until ADO.NET 2.0. But with ADO.NET 2.0 things got very simple as Adapter now supports multiple Insert/Update with the user defined batch size. We are going to limit our discussion to Insert functionality. For Insert, create the Command object with usual simple stored proc for Insert and specify that as the InsertCommand to the DataAdapter object. Along with this we need to specify the UpdateBatchSize which determines the number of Inserts to be processed in one network round trip. Follow the code below to have complete understanding.

First of all create the table and the stored proc in your SQL Server Instance:

CREATE TABLE Person
(        
          PersonId INT PRIMARY KEY,        
          PersonName VARCHAR(100)
 )

GO

CREATE PROCEDURE sp_BatchInsert ( @PersonId INT, @PersonName VARCHAR(100) ) 
AS 
BEGIN 
            INSERT INTO Person VALUES ( @PersonId, @PersonName); 
END 

        private void btnBatchInsert_Click(object sender, EventArgs e)

        {

            // Get the DataTable with Rows State as RowState.Added

            DataTable dtInsertRows = GetDataTable(); 

 

            SqlConnection connection = new SqlConnection(connectionString);

            SqlCommand command = new SqlCommand("sp_BatchInsert", connection);

            command.CommandType = CommandType.StoredProcedure;

            command.UpdatedRowSource = UpdateRowSource.None;

 

             // Set the Parameter with appropriate Source Column Name

            command.Parameters.Add("@PersonId", SqlDbType.Int, 4, dtInsertRows.Columns[0].ColumnName);            

            command.Parameters.Add("@PersonName", SqlDbType.VarChar, 100, dtInsertRows.Columns[1].ColumnName);

            

            SqlDataAdapter adpt = new SqlDataAdapter();

            adpt.InsertCommand = command;

            // Specify the number of records to be Inserted/Updated in one go. Default is 1.

            adpt.UpdateBatchSize = 2;

            

            connection.Open();

            int recordsInserted = adpt.Update(dtInsertRows);            

            connection.Close();

 

            MessageBox.Show("Number of records affected : " + recordsInserted.ToString());

 

        }