Inserting Records Into SQL Server Using SqlBulkCopy

by Joe Finsterwald

This code sample applies to the .NET 2.0 framework and SQL Server 2000 / 2005. Examples are in C#.

Contents

1. The Problem
2. The Solution
3. The Trouble with SqlBulkCopy
4. An Example Using Transactions
5. Conclusion

The Problem:

You’re trying to insert a large number of records into a SQL Server table over a slow connection using a synchronous thread.

The solution:

With the advent of .NET 2.0 Microsoft has incorporated SQL Server’s bcp command line utility into the framework. The System.Data.SqlClient.SqlBulkCopy class provides significant performance increases over regular inserts. It also works with the System.Data.SqlClient.SqlTransaction class.

Aside from offering DTS-like speed the SqlBulkCopy class is pretty flexible. SqlBulkCopy's WriteToServer method takes the following types as arguments:

• DataRow[]
• IDataReader
• DataTable

The Trouble with SqlBulkCopy

All the convenience of SqlBulkCopy doesn't come for free. Unfortunately, SqlBulkCopy only works with SQL Server 2000 or 2005. Moreover, if you want to wrap SqlBulkCopy in a transaction you can only use the SqlTransaction class. SqlBulkCopy does not support distributed transactions.

An Example Using Transactions

This example wraps SqlBulkCopy in a SqlTransaction. Notice how I use the BatchSize property to set how many records to process in a transaction. If you don't stipulate how many records to process in a batch then the system will send all the records in a single transaction which will likely result in a connection timeout.

using System;
using System.Data;
using System.Data.SqlClient;

namespace Coreweb.Example
{
   public class SqlBulkCopyExample
   {
      private const int BATCH_SIZE = 200;
      private string m_dbConnectionString;

      public SqlBulkCopyExample(string dbConnString) 
      {
         m_dbConnectionString = dbConnString;
      }

      public void DataTableBulkUpload(DataTable dataTable, string targetTable)
      {
         using (SqlConnection connection = 
            new SqlConnection(m_dbConnectionString))
         {
            connection.Open();
            SqlTransaction transaction = connection.BeginTransaction();

            try
            {
               // BulkUpload
               using (SqlBulkCopy copy = new SqlBulkCopy(connection, 
                  SqlBulkCopyOptions.Default, transaction))
               {
                  copy.DestinationTableName = targetTable;
                  copy.BatchSize = BATCH_SIZE;

                  copy.WriteToServer(dataTable);
               }

               transaction.Commit();
            }
            catch
            {
               transaction.Rollback();
               throw;
            }

            connection.Close();
         }
      }
   }
}

Conclusion:

My advice is that you shouldn’t cut corners, but if loose coupling isn’t critically important then SqlBulkCopy provides both performance and flexibility. In the event that you can't use SqlBulkCopy consider other options such as asynchronous threading.