Click here to Skip to main content
15,444,203 members
Articles / Web Development / ASP.NET
Posted 25 Dec 2008


21 bookmarked

Using SqlBulkCopy with ASP.NET 2.0

Rate me:
Please Sign up or sign in to vote.
3.78/5 (11 votes)
25 Dec 2008CPOL2 min read
An article to explain the use of SqlBulkCopy class in ASP.NET


Microsoft SQL Server includes a popular command-prompt utility named bcp for moving data from one table to another, whether on a single server or between servers. The SqlBulkCopy class lets you write managed code solutions that provide similar functionality. There are other ways to load data into a SQL Server table (INSERT statements, for example), but SqlBulkCopy offers a significant performance advantage over them.

The SqlBulkCopy class can be used to write data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.

Transferring Data

SqlBulkCopy contains an instance method WriteToServer which is used to transfer the data from the source to the destination. WriteToServer method can perform action of DataRow[] array, DataTable and DataReader. Depending on the situation, you can choose the container you like but in most cases, choosing DataReader is a good idea. This is because DataReader is a forward-only, read-only stream. It does not hold the data and thus is much faster than DataTable and DataRows[]. The code below is used to transfer the data from the source table to the destination table.

Using the Code

The following application demonstrates how to load data using the SqlBulkCopy class. In this example, a SqlDataReader is used to copy data from source table to destination table. I have used 'master' database of Microsoft SQL Server 2005.

string strConnection = ConfigurationManager.AppSettings["conStr"].ToString();

SqlConnection sourceconnection = new SqlConnection(strConnection);
SqlCommand cmd = new SqlCommand("Select * from MSreplication_options");
cmd.Connection = sourceconnection;
SqlDataReader reader = cmd.ExecuteReader();

//Connect to Destination DataBase
SqlConnection destinationConnection = new SqlConnection(strConnection);

Point to be noted here is that I am using same 'connectionString' as I am copying data to the same server.

I now have to copy data using bulk copy feature:

SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection);
bulkCopy.DestinationTableName = "destination";


In the end, we closed 'reader' and then we can close our connection to the SQL server. In this way, data from one table was copied to other in the fastest possible way. It is also possible to use a single instance of 'SqlBulkCopy' for performing multiple bulk copy operations. This technique is more efficient than using separate 'SqlBulkCopy' instances for each operation.

One more technique is to perform bulk copy in a transaction. Using this technique, one can perform multiple bulk copy operations along with other database operations, i.e. update, delete, etc. and as one is using transaction, it can be easily committed and rolled back.

Points of Interest

Besides bulk copy, ADO.NET version 2.0 has plenty of new features. Here I am listing some of them:

  1. Multiple Active Results Sets (MARS) - allows application to have multiple 'SqlDataReader' open a connection where each instance of 'SqlDataReader' is started from separate command.
  2. Batch processing - another feature included to enhance application performance is batch processing in which updates to database from 'Dataset' are done in batches.
  3. Data tracing - an interesting feature is built-in data tracing supported by .NET data providers.


  • 24-Dec-2008 Initial post


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Team Leader
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

GeneralMy vote of 5 Pin
gkrishna255-Dec-10 23:03
Membergkrishna255-Dec-10 23:03 
GeneralMy vote of 2 Pin
hammerstein052-Feb-10 7:34
Memberhammerstein052-Feb-10 7:34 
GeneralMy vote of 1 Pin
Rob Graham26-Dec-08 4:57
MemberRob Graham26-Dec-08 4:57 
GeneralRe: My vote of 1 Pin
Aman Bhullar27-Dec-08 5:59
MemberAman Bhullar27-Dec-08 5:59 
GeneralRe: My vote of 1 Pin
Rob Graham28-Dec-08 4:25
MemberRob Graham28-Dec-08 4:25 
GeneralRe: My vote of 1 Pin
Aman Bhullar28-Dec-08 5:48
MemberAman Bhullar28-Dec-08 5:48 
GeneralRe: My vote of 1 Pin
Rob Graham28-Dec-08 8:35
MemberRob Graham28-Dec-08 8:35 
GeneralSimple and Smart Pin
Member 313707825-Dec-08 17:28
MemberMember 313707825-Dec-08 17:28 
AnswerRe: Simple and Smart Pin
Rob Graham26-Dec-08 4:58
MemberRob Graham26-Dec-08 4:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.