Using SqlBulkCopy with ASP.NET 2.0

• Download source - 2.21 KB

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