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

SqlBulkCopy in ADO.NET 2.0, SqlBulkCopy Class in C#, SqlBulkCopy help, SqlBulkCopy guide, SqlBulkCopy class, SqlBulkCopy tutorial

, 28 Sep 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
SqlBulkCopy in ADO.NET 2.0, SqlBulkCopy Class in C#, SqlBulkCopy help, SqlBulkCopy guide, Transferring Data Using SqlBulkCopy - SqlBulkCopy - Copy Table Data Between SQL Servers at High Speeds, perform bulk copies with .NET 2.0's SqlBulkCopy class, SqlBulkCopy tutorial

Introduction

SqlBulkCopy is a new feature in ADO.NET 2.0 that gives you DTS-like speeds when you need to programmatically copy data from one database to another. That allows you to perform copy operation for a large amount of data between a source data store and a destination data table.

Background

I was facing a problem in my last application. I had to work on data migration. It was from FoxPro to SQL Server 2005. There are millions of records in each table. Another problem is that some tables contain more than 150 columns. There was no concept of normalization in the FoxPro database and I also have to manage that. I came across SqlBulkCopy class in Stem.Data.sqlclient.

This class helps me solve my problem a lot. So I have made up my mind to share the basic ideas of this class with you.Copying a large amount of data from a source data store to a destination table in SQL database by using a traditional approach has performance effect because you need to call the database several times. There were some ways to solve this issue but now by having ADO.NET 2.0 in hand, you can perform a bulk copy and reduce the number of database accesses to improve performance and speed. SqlBulkCopy is the heart of bulk copy in ADO.NET 2.0 and SqlBulkCopyColumnMapping and SqlBulkCopyColumnMappingCollection objects assist it in this way. Later I'll discuss these objects in more detail.

Using the Code

SqlBulkCopy

SqlBulkCopy is the object that helps you to perform a bulk copy. You can use a DataReader or DataTable as source data store (you can load your data from SQL database, Access database, XML or ... into these objects easily) and copy them to a destination table in database.

To accomplish this task, SqlBulkCopy uses a collection of SqlBulkCopyColumnMapping objects which will be saved as its SqlBulkCopyColumnMappingCollection property. SqlBulkCopyColumnMapping maps a column in data source to a table in destination table via their name or index.

SqlBulkCopy has some important properties that you should be aware of to be able to use it:

  • BatchSize: This integer value specifies the number of rows that should be copied in each attempt to copy to database. This value has direct effect on the number of accesses to database.
  • BulkCopyTimeOut: The number of seconds that system should wait to let SqlBulkCopy to copy rows.
  • ColumnMappings: A ReadOnly SqlBulkCopyColumnMappingCollection. You need to use its Add() method to add a new SqlBulkCopyColumnMapping object to its collection.
  • DestinationTableName: String value of destination table's name.
  • NotifyAfter: SqlRowsCopied event handler will be called when the number of rows specified in this property have been copied.

This object also has four overloads. You can pass a SqlConnection (or a connection string) plus an optional SqlBulkCopyOptions and SqlTransaction to its constructor. Latest two parameters can change the behavior of SqlBulkCopy object. Using SqlBulkCopyOptions enumerator, you can specify that for example SqlBulkCopy keeps identities or check constraints and some other options. Using SqlTransaction you can pass an external SqlTransaction and your SqlBulkCopy uses this transaction in all parts of the process.

SqlBulkCopy also has a SqlRowsCopied event handler that triggers when the specific number of DataRows have been copies. You specified this value via NotifyAfter property. This handler is helpful when you want to be aware of your process (for instance showing it via a ProgressBar to the end user).

Useful Method

The last thing that should be mentioned about SqlBulkCopy object is its WriteToServer() method. This method can get an array of DataRows, a DataTable or a DataReader and copies their content to destination table in database.

SqlBulkCopyColumnMapping

SqlBulkCopyColumnMapping is the object that maps your source columns to destination columns in a bulk copy. A SqlBulkCopyColumnMapping can get the source and destination column names or ordinals via its properties or its constructor. It has these properties:

  • SourceColumn: String value of source column's name
  • SourceOrdinal: Integer value of source column's index
  • DestinationColumn: String value of destination column's name
  • DestinationOrdinal: Integer value of destination column's index

One of SourceColumn and SourceOrdinal and one of DestinationColumn and DestinationOrdinal should be set. Also you can set these properties via constructor which is an easier way.

Note that if your source and destination columns have the same names, it's not required to use SqlBulkCopyColumnMapping objects because SqlBulkCopy can do its job automatically.

Write a Sample Application

Let's have a look at an example. I think the sample application you'll see in a moment can cover all you need to use SqlBulkCopy.

NOTE: If you have the same table column on both sides meaning source and destination, then I will suggest that you should not mention SqlBulkCopyColumnMapping properties because it performs all tasks automatically.

PerformBulkCopy() method is used because both side columns are the same.

public class mySqlBulkCopy
{
private static void PerformBulkCopy()
{
    string connectionString =
            @"Server=localhost;Database=Northwind;Trusted_Connection=true";
    // get the source data
    using (SqlConnection sourceConnection =
            new SqlConnection(connectionString))
    {
        SqlCommand myCommand =
            new SqlCommand("SELECT * FROM Customer", sourceConnection);
        sourceConnection.Open();
        SqlDataReader reader = myCommand.ExecuteReader();

        // open the destination data
        using (SqlConnection destinationConnection =
                    new SqlConnection(connectionString))
        {
            // open the connection
            destinationConnection.Open();

            using (SqlBulkCopy bulkCopy =
            new SqlBulkCopy(destinationConnection.ConnectionString))
            {
                bulkCopy.BatchSize = 500;
                bulkCopy.NotifyAfter = 1000;
                bulkCopy.SqlRowsCopied +=
                    new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
                bulkCopy.DestinationTableName = " Customer_Backup";
                bulkCopy.WriteToServer(reader);
            }
        }
        reader.Close();
    }
}
// this method is used if you want to map columns with different names
// you can also use the index of column by using SourceOrdinal
private static void PerformBulkCopyMyDifferentSchema()
{
    string connectionString = @"Server=
        localhost;Database=Northwind;Trusted_Connection=true";
    DataTable sourceData = new DataTable();
    // get the source data
    using (SqlConnection sourceConnection =
                    new SqlConnection(connectionString))
    {
        SqlCommand myCommand =
            new SqlCommand("SELECT *
            FROM Customer", sourceConnection);
        sourceConnection.Open();
        SqlDataReader reader = myCommand.ExecuteReader();
        // open the destination data
        using (SqlConnection destinationConnection =
                    new SqlConnection(connectionString))
        {
            // open the connection
            destinationConnection.Open();
            using (SqlBulkCopy bulkCopy =
                new SqlBulkCopy(destinationConnection.ConnectionString))
            {
                bulkCopy.ColumnMappings.Add("CustomerID","ID");
                bulkCopy.ColumnMappings.Add("CustomerName", "Name");
                bulkCopy.ColumnMappings.Add("CustomerPoint", "Points");
                bulkCopy.DestinationTableName = " CustomerPoints";
                bulkCopy.WriteToServer(reader);
            }
        }
        reader.Close();
    }
}
}
}

Summary

If you are familiar with SQLHELPER class, then you can improve your performance more efficiently. In this article, I talked about a newly added feature in ADO.NET 2.0, SqlBulkCopy, which helps you to bulk copy large amounts of data between data source and data table and improve your performance.

History

  • 28th September, 2007: 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

idreeskhan
Web Developer
Pakistan Pakistan
I'm from Pakistan and have two years of experience in C# and Asp.net in visual stdio 2005 ,2008, 2010 and SQL Server 2000/2005/2008

Comments and Discussions

 
QuestionCan I use SQLBulkCopy to copy data from and to sybase database PinmemberHoda sss20-Oct-09 1:19 
AnswerRe: Can I use SQLBulkCopy to copy data from and to sybase database Pinmemberidreeskhan20-Oct-09 2:34 
GeneralYou mention SqlHelper class Pinmembervalamas9-Jun-09 15:12 
GeneralRe: You mention SqlHelper class Pinmemberidreeskhan9-Jun-09 18:40 
Hi
Thanks for your question.
Actually not to specific because i have hard coded each thing in my code at DAL Level. I have not used proper coding standard. I mean application Layer ,Business logic layer ,and Data Access Layer.
That is why i asked about code it in DAL(Data Access Layer). And I think SQLHelper is best for DAL.Because it is simple and easy to Learn and also easy to Use/Implement.
Big Grin | :-D
Regard
Muhammad Idrees
GeneralMy vote of 1 Pinmemberteichgraf22-Mar-09 23:59 
GeneralSQLBulkCopy PinmemberHemant.Kamalakar20-Oct-07 2:25 
GeneralRe: SQLBulkCopy Pinmemberidreeskhan21-Oct-07 20:21 
QuestionRe: SQLBulkCopy PinmemberHemant.Kamalakar21-Oct-07 23:11 
GeneralRe: SQLBulkCopy PinmemberPratik.Patel14-Nov-08 5:31 
QuestionCan SqlBulkCopy copy data to the local temporary table which is dynamicly generated in a store procedure? Pinmemberdddd2181-Oct-07 5:57 
Answerthat is to say, how can I use SqlBulkCopy to copy data to the local temporary table which is dynamicly generated in a store procedure? Pinmemberdddd2181-Oct-07 6:14 
GeneralRe: that is to say, how can I use SqlBulkCopy to copy data to the local temporary table which is dynamicly generated in a store procedure? Pinmemberidreeskhan2-Oct-07 0:29 
AnswerRe: Can SqlBulkCopy copy data to the local temporary table which is dynamicly generated in a store procedure? Pinmemberidreeskhan2-Oct-07 0:27 
GeneralRe: Can SqlBulkCopy copy data to the local temporary table which is dynamicly generated in a store procedure? Pinmemberdddd2182-Oct-07 0:59 
GeneralNice Article Pinmemberrilov1-Oct-07 4:43 
GeneralRe: Nice Article [modified] Pinmemberidreeskhan1-Oct-07 20:30 
GeneralRe: Nice Article Pinmemberrilov2-Oct-07 3:40 

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
Web01 | 2.8.141216.1 | Last Updated 29 Sep 2007
Article Copyright 2007 by idreeskhan
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid