SQL Bulk Copy with C#.NET

Introduction

Programmers usually need to transfer production data for testing or analyzing. The simplest way to copy lots of data from any resources to SQL Server is BulkCopying. .NET Framework 2.0 contains a class in ADO.NET "System.Data.SqlClient" namespace: SqlBulkCopy. The bulk copy operation usually has two separated phases.

In the first phase, you get the source data. The source could be various data platforms such as Access, Excel, SQL.. You must get the source data in your code wrapping it in a DataTable, or any DataReader class which implements IDataReader. After that, in the second phase, you must connect the target SQL database and perform the bulk copy operation.

The bulk copy operation in .NET is a very fast way to copy large amount of data somewhere to SQL Server. The reason for that is the Bulkcopy SQL Server mechanism. Inserting all data row by row, one after the other is a very time and system resources consuming. But the bulkcopy mechanism process all data at once. So the data inserting becomes very fast.

Solution Walkthrough

While you are programming for bulk copy, first open a connection for the source data. In this sample, we are connecting a SQL Server named SQLProduction. We are using SqlConnectionStringBuilder to build our connection string.

Hide   Copy Code

// Establishing connectionSqlConnectionStringBuilder cb = new SqlConnectionStringBuilder(); cb.DataSource = "SQLProduction"; cb.InitialCatalog = "Sales"; cb.IntegratedSecurity = true;SqlConnection cnn = new SqlConnection(cb.ConnectionString);  

Then we are retrieving data from the source with SqlCommand and SqlDataReader classes. 

Hide   Copy Code

// Getting source dataSqlCommand cmd = new SqlCommand("SELECT * FROM PendingOrders",cnn); cnn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); 

Now we have a data in rdr variable. It's time to initialize a SqlBulkCopy object and copy the data. The SqlBulkCopy class needs a connection to copy data into a SQL server. You can establish a second connection explicitly or the class will do it for you. We are using the second alternative with creating a SqlBulkCopy object. We are passing a connection string as a parameter in constructor method.

Hide   Copy Code

// Initializing an SqlBulkCopy objectSqlBulkCopy sbc = new SqlBulkCopy("server=.;database=ProductionTest;" +                                  "Integrated Security=SSPI"); 

OK. The sbc object is ready to copy. Now you must tell the object the destination table name, start the copying process calling WriteToServer method and pass the method the SqlDataReader variable rdr as parameter.

Hide   Copy Code

// Copying data to destinationsbc.DestinationTableName = "Temp"; sbc.WriteToServer(rdr); 

At the end, close all SqlConnection, SqlDataReader and SqlBulkCopy objects.

Hide   Copy Code

// Closing connection and the otherssbc.Close(); rdr.Close(); cnn.Close(); 

That's all. Just a few lines and in a few seconds...

Hide   Copy Code

// Establishing connectionSqlConnectionStringBuilder cb = new SqlConnectionStringBuilder(); cb.DataSource = "SQLProduction"; cb.InitialCatalog = "Sales"; cb.IntegratedSecurity = true;SqlConnection cnn = new SqlConnection(cb.ConnectionString);  // Getting source dataSqlCommand cmd = new SqlCommand("SELECT * FROM PendingOrders",cnn); cnn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); // Initializing an SqlBulkCopy objectSqlBulkCopy sbc = new SqlBulkCopy("server=.;database=ProductionTest;" +                                  "Integrated Security=SSPI"); // Copying data to destinationsbc.DestinationTableName = "Temp"; sbc.WriteToServer(rdr); // Closing connection and the otherssbc.Close(); rdr.Close(); cnn.Close(); 

History

• 25^th January, 2007: Initial version

License

This article has no explicit license attached to it, but may contain usage terms in the article text or the download files themselves. If in doubt, please contact the author via the discussion board below. A list of licenses authors might use can be found here.