Sometimes you need to copy a lot of data into a Microsoft SQL Server table and you want the process the process to be as fast as possible. A SSIS task would be a good choice but sometimes you want to keep the process into your own application (for all kind of good and bad reasons).

ADO.Net exposes a feature perfect to fit this requirement. This feature is contained into the SQLBulkCopy class (from the System.Data.SqlClient namespace) and it will quickly copy the content of a DataTable or a DataReader to a Microsoft SQL server table.

Demo code

This month, the demo code is provided in both VB and C#. It has been built using Visual Studio 2008 but it would work with Visual Studio 2005 (and up).

You will need to have access to SQL Server database 2005 or better (Express edition can be used).

What is the SqlBulkCopy class?

This little known class can only be used to write data to Microsoft SQL Server tables. It is available since the .Net Framework 2.0 (Visual Studio 2005).

This class performs a single operation against the database in a non-transacted (by default) way which performs better but doesn’t provide any way of rolling back. This is why you will mostly find this mechanism to load data into staging tables where the content is wiped before a new batch is imported.

Regarding transactions, you can still create your own ADO.Net transaction and perform the bulk copy in it but you may lose one of the biggest advantages of this feature: the speed. For more details on transactions, see Transaction and Bulk Copy Operations (ADO.NET).

Setting up the test database

Because I don’t want you to mess your existing database, I provide you the script to create a new database and a new set of tables that we will use to test the feature.

--1. Create the demo database
--CREATE DATABASE DemoBulkCopy –- Uncomment this line to create the database
USE DemoBulkCopy

--2. Create the destination tables
IF EXISTS (SELECT * FROM dbo.sysobjects 
 WHERE id = object_id(N'[dbo].[EmployeeMatchingColumns]') 
 AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    DROP TABLE [dbo].[EmployeeMatchingColumns]

CREATE TABLE [dbo].[EmployeeMatchingColumns](
    [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL
) 

IF EXISTS (SELECT * FROM dbo.sysobjects 
 WHERE id = object_id(N'[dbo].[EmployeeDifferentColumns]') 
 AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    DROP TABLE [dbo].[EmployeeDifferentColumns]

CREATE TABLE [dbo].[EmployeeDifferentColumns](
    --[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    [Prenom] [nvarchar](50) NOT NULL,
    [Nom] [nvarchar](50) NOT NULL
)

--3. Query the tables
SELECT * FROM EmployeeMatchingColumns
SELECT * FROM EmployeeDifferentColumns ORDER BY Prenom 

My demo data source

The data source for my demo is a datatable created and filled dynamically in code.

The 3-fields (ID, FirstName, LastName) datatable contains 4 or 100,000 rows according to the value of the parameter.

Private Function CreateDataSource(ByVal pLargeDataTable As Boolean) As DataTable
    Dim dt As New DataTable("Person")
    With dt
        .Columns.Add("ID", GetType(Integer))
        .Columns.Add("FirstName", GetType(String))
        .Columns.Add("LastName", GetType(String))

        'Add rows
        .LoadDataRow(New Object() {-1, "Joe", "Dalton"}, True)
        .LoadDataRow(New Object() {-2, "Jack", "Dalton"}, True)
        .LoadDataRow(New Object() {-3, "Willam", "Dalton"}, True)
        .LoadDataRow(New Object() {-4, "Averell", "Dalton"}, True)

        If pLargeDataTable Then
            Dim intID As Integer = 5
            Do While intID <= 100000
                .LoadDataRow(New Object() {intID * -1, "FN" + intID.ToString, "Dalton"}, True)
                intID += 1
            Loop
        End If
    End With

    Return dt
End Function

This method is used for the 3 demos presented in this article.

First demo

The first demo is a simple one in which we will copy a datatable to a SQL Server table with an exact schema in both source and destination. Call it coincidence; the dynamically created datatable has exactly the same columns as a table named EmployeeMatchingColumns.

This is the simplest scenario where we can use the SQLBulkCopy (but also the one we almost never met).

The steps to use the SQLBulkCopy class are always the same:

• Create the data source
• Open a connection to the destination database
• Instantiate the SQLBulkCopy object
• Set the properties of the SQLBulkCopy object
• Call the WriteToServer method
• Close the SQLBulkCopy object

It seems a lot but in fact, it is not as complex as it may appear!

This is the complete code for the first demo:

'1. Create a datasource
Dim dt As DataTable = Me.CreateDataSource(False)

' Open the destination connection. 
Using cnDestination As SqlConnection = New SqlConnection(Me.ConnectionString)
    cnDestination.Open()

    '2. Instantiate a Bulk Copy object
    Using objBulkCopy As SqlBulkCopy = New SqlBulkCopy(cnDestination)
        '3. Set the Bulk Copy object properties
        objBulkCopy.DestinationTableName = "dbo.EmployeeMatchingColumns"

        Try
            '4. Write from the source to the destination.
            objBulkCopy.WriteToServer(dt)
            '5. Close the Bulk Copy object
            objBulkCopy.Close()

            MessageBox.Show("Bulk Copy completed", "Process completed", _
                            MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", _
                            MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Using
End Using

Because the first demo has identical structure for both source and destination, the only property we need to set is the name of the destination table.

If you run this code and query the database using SSMS, you will find your 4 rows inserted into the EmployeeMatchingColumns table.

Second demo

As I told you before, the first scenario (both source and destination tables with exactly the same schema) is almost never met in real life.

For my second demo, I will have a scenario a bit more realist. We will reuse the same source datatable (with columns ID, FirstName, and LastName). This source table will be copied to a table having a different schema. The destination will only have 2 columns: Prenom (French word for FirstName) and Nom (French word for LastName). We won’t have anything in the destination table to fit the ID column.

The recipe is still the same. The only thing that will differ is that we will have to match the columns manually using the ColumnMappings collection (specifying the name of the source and destination columns). You might also notice that I have changed the name of my destination table to EmployeeDifferentColumns.

This is my complete code for this second scenario:

'1. Create a datasource
Dim dt As DataTable = Me.CreateDataSource(False)

' Open the destination connection. 
Using cnDestination As SqlConnection = New SqlConnection(Me.ConnectionString)
    cnDestination.Open()

    '2. Instantiate a Bulk Copy object
    Using objBulkCopy As SqlBulkCopy = New SqlBulkCopy(cnDestination)
        '3. Set the Bulk Copy object properties
        objBulkCopy.DestinationTableName = "dbo.EmployeeDifferentColumns"

        objBulkCopy.ColumnMappings.Clear()
        objBulkCopy.ColumnMappings.Add("FirstName", "Prenom")
        objBulkCopy.ColumnMappings.Add("LastName", "Nom")

        Try
            '4. Write from the source to the destination.
            objBulkCopy.WriteToServer(dt)
            '5. Close the Bulk Copy object
            objBulkCopy.Close()

            MessageBox.Show("Bulk Copy completed", "Process completed", _
                            MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", _
                            MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Using
End Using

Third demo

The last demo I am presenting is exactly the same as the second one but with much more volume (100,000 rows).

Because I have a larger volume, I also introduce you to the single event this object can raise: SqlRowsCopied. This event can be used to indicate progress to the user.

There are very few modifications to the code of the second demo. The first modification is that we pass True to the CreateDataSource method to get 100,000 rows. Another modification is that we need to set the NotifyAfter property (which is the number of rows to copy before triggering the event). Finally, we need to add a handler to handle the event. These 2 rows have to be somewhere between the instantiation of the object and the call to the WriteToServer method (I put mine after the ColumnsMapping).

objBulkCopy.NotifyAfter = 1000
AddHandler objBulkCopy.SqlRowsCopied, AddressOf RowsCopied

The event handler in my demo is as simple as showing the number of rows copied in a Label control:

Private Sub RowsCopied(ByVal sender As System.Object, ByVal e As SqlRowsCopiedEventArgs)
    lblRowsCopied.Text = e.RowsCopied.ToString("#,##0")
End Sub

When I run this third demo on my computer which also runs the destination SQL Server database, copying 100,000 rows takes less than 2 seconds. Your mileage here may vary according to the number of fields you are processing and the latency of your network (if your database is on a server).

Conclusion

This class is very specific to a set of requirements but it does its job in a very efficient way.

You can even go a bit further with this class if you like. For example, it is possible that a single source datatable can me split to 2 destination tables.

This article introduced you the the SQLBulkCopy class and how to use it in your own application.