batch update For Sql

Use Batch Update of ADO.NET 2.0 DataAdapter Object

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

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 stored proc in your SQL Server Instance:

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

Now refer to the C# code below:

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());

 

}

Well, first thing we all developers do is check using SQL Profiler. And to our surprise it shows 4 different RPC requests sent to SQL Server. This is how it looks there :

Do not Panic. The difference in not in the way your statements are executed at the Server, the difference is in terms of how your request(s) are sent to the server. In simple words, with UpdateBatchSize as 2 ( in this case ), it just means that request for insertion of 2 rows will be grouped together and sent to the database server in single network round trip. You can probably use some other tools like “Netmon” etc to have a closer look. So use the UpdateBatchSize appropriately

The DataAdapter has two update-related events: RowUpdating and RowUpdated. Only one RowUpdated event occurs for each batch, whereas the RowUpdating event occurs for each row processed. You may also like to look at the Exceptional handling part of it. Explore them.

 

-----reference the Neeraj Saluja