Multiple Inserts in Single Database Roundtrip Using SQLBulkCopy


This article explains how to use SQL Bulk Copy to take records from DataTable object and insert multiple records in SQL Server database in single round trip

Valid for Environment: NET 2.0 or above on SQL Server 2005 database 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.

With ADO.NET 2.0 we got the programming interface for Bulk Copy which provides quite simple and straight forward mechanism to transfer the data from one SQL server instance to another, from one table to another, from DataTable to SQL Server 2005 database, from DataReader to SQL Server 2005 database and many more.

To start with It, first of all create a simple Person table as below in your database


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


SqlBulkCopy belongs to System.Data.SqlClient namespace and it is as simple as ADO.NET Command object when it comes to programming it. Let us see it working:


private void btnSQLBulkCopyInsert_Click(object sender, EventArgs e)
{
// Get the DataTable

DataTable dtInsertRows = GetDataTable();

using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString))
{
sbc.DestinationTableName = "Person";

// Number of records to be processed in one go

sbc.BatchSize = 2;

// Map the Source Column from DataTabel to the Destination Columns in SQL Server 2005 Person Table

sbc.ColumnMappings.Add("PersonId", "PersonId");

sbc.ColumnMappings.Add("PersonName", "PersonName");


// Number of records after which client has to be notified about its status

sbc.NotifyAfter = dtInsertRows.Rows.Count;


// Event that gets fired when NotifyAfter number of records are processed.

sbc.SqlRowsCopied+=new SqlRowsCopiedEventHandler(sbc_SqlRowsCopied);

// Finally write to server

sbc.WriteToServer(dtInsertRows);

sbc.Close();

}
}

void sbc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
MessageBox.Show("Number of records affected : " + e.RowsCopied.ToString());

}



The code above is very simple and quite self explanatory.

Key Notes :

BatchSize and NotifyAfter are two different properties. Former specify the number of records to be processed in one go while later specifies the number of records to be processed after which client needs to be notified.
Reference:

No better place than MSDN. Refer to http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx for details on SqlBulkCopy
Refer http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy_properties.aspx to get details on all properties on SqlBulkCopy.


Comments

Author: Sebastian13 Jun 2008 Member Level: Gold   Points : 1

I was looking for the details based on this topic.Thanks for sharing it with me.

Guest Author: Yulia28 May 2013

Man Interested Hi, i read your blog from time to time and I own a similar one and i was just wondering if you get a lot of spam responses? If so how do you prevent it, any plugin or anything you can advise? I get so much lately it's driving me insane so any help is



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: