Click here to Skip to main content
Click here to Skip to main content

Using SqlBulkCopy with ASP.NET 2.0

, 25 Dec 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
An article to explain the use of SqlBulkCopy class in ASP.NET

Introduction

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);
sourceconnection.Open();
SqlCommand cmd = new SqlCommand("Select * from MSreplication_options");
cmd.Connection = sourceconnection;
SqlDataReader reader = cmd.ExecuteReader();

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

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";
bulkCopy.WriteToServer(reader);
reader.Close();

sourceconnection.Close();
destinationConnection.Close();

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.

History

  • 24-Dec-2008 Initial post

License

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

Share

About the Author

Aman Bhullar
Team Leader
India India
No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 Pinmembergkrishna256-Dec-10 0:03 
GeneralMy vote of 2 Pinmemberhammerstein052-Feb-10 8:34 
GeneralMy vote of 1 PinmemberRob Graham26-Dec-08 5:57 
GeneralRe: My vote of 1 PinmemberAmandeep Singh Bhullar27-Dec-08 6:59 
GeneralRe: My vote of 1 PinmemberRob Graham28-Dec-08 5:25 
GeneralRe: My vote of 1 PinmemberAmandeep Singh Bhullar28-Dec-08 6:48 
GeneralRe: My vote of 1 PinmemberRob Graham28-Dec-08 9:35 
GeneralSimple and Smart PinmemberMember 313707825-Dec-08 18:28 
AnswerRe: Simple and Smart PinmemberRob Graham26-Dec-08 5:58 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141216.1 | Last Updated 25 Dec 2008
Article Copyright 2008 by Aman Bhullar
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid