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
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.
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.
|
| Author: Sebastian 13 Jun 2008 | Member Level: Gold Points : 1 |
I was looking for the details based on this topic.Thanks for sharing it with me.
|