Posted by: Cirilo Meggiolaro | 04/6/2009

Tip of the day #174 – SqlBulkCopy class

Bulk copies are common when you need to populate large amounts of data in a database table. A common use for that is when you have a physical file that you need to open and save its content to the database.

.NET Framework provides an easy way to load data into a SQL Server table using bulk operations. The SqlBulkCopy class available under the System.Data.SqlClient namespace has a good set of options to help you out on that kind of operation.

Constructor overloads

Four constructor overloads are available for the SqlBulkCopy class:

  • public SqlBulkCopy(SqlConnection connection);
  • public SqlBulkCopy(string connectionString);
  • public SqlBulkCopy(string connectionString, SqlBulkCopyOptions copyOptions);
  • public SqlBulkCopy(SqlConnection connection, SqlBulkCopyOptions copyOptions, SqlTransaction externalTransaction);

The first and second overloads are pretty straightforward to understand since they accept either a SqlConnection or a connection string respectively but you may have noticed the use of a SqlBulkCopyOptions enumerator as parameter on the third overload. This is a bit flag enumerator (bit flag enumerators are described on Tip #97) that defines the behavior of the copy process. The following options are available:

  • Default: Use the default values for all options;
  • KeepIdentity: Preserve source identity values. When not specified, identity values are assigned by the destination;
  • CheckConstraints: Check constraints while data is being inserted. By default, constraints are not checked;
  • TableLock: Obtain a bulk update lock for the duration of the bulk copy operation. When not specified, row locks are used;
  • KeepNulls: Preserve null values in the destination table regardless of the settings for default values. When not specified, null values are replaced by default values where applicable;
  • FireTriggers: When specified, cause the server to fire the insert triggers for the rows being inserted into the database;
  • UseInternalTransaction: When specified, each batch of the bulk-copy operation will occur within a transaction. If you indicate this option and also provide a SqlTransaction object to the constructor, an ArgumentException occurs.

The last overload also accepts a SqlTransaction object that defines the transaction model for the copy process.

Properties

  • int BatchSize: Gets or sets the number of rows in each batch. At the end of each batch, the rows in the batch are sent to the server;
  • int BulkCopyTimeout: Gets or sets the number of seconds for the operation to complete before it times out;
  • SqlBulkCopyColumnMappingCollection ColumnMappings: Gets a list of SqlBulkCopyColumnMapping objects that represents the relationship between the source and destination tables;
  • string DestinationTableName: Gets or sets the name of the destination table on the database;
  • int NotifyAfter: Gets or sets the number of rows to be processed before generating a notification event.

Event

  • SqlRowsCopiedEventHandler SqlRowsCopied: The event is raised every time the number of rows copied to the database table reaches the number of rows set on the NotifyAfter property. The event arguments object contains two important properties:
    • long RowsCopied: returns the number of rows copied during the current bulk copy operation;
    • bool Abort: If you set to true, the bulk copy stops and an OperationAbortedException is raised;

Methods

  • void Close(): Close the SqlBulkCopy instance;
  • void WriteToServer(DataRow[] rows): Copies all copies from the array of data rows to the database table;
  • void WriteToServer(DataTable table): Copies all copies from the DataTable to the database table;
  • void WriteToServer(IDataReader reader): Copies all copies from the IDataReader object to the database table;
  • void WriteToServer(DataTable table, DataRowState rowState): Copies all copies from the DataTable to the database table that match with the DataRowState.

How to…

The example I am going to build is a console application that based on a DataTable object will perform a bulk copy to the table shown below:

Picture 1 - Destination table

Picture 1 - Destination table

  • Open Visual Studio 2008;
  • Create a new console application called BulkCopyConsole;
  • Open Program.cs file on the Solution Explorer window if it’s not opened yet;
  • Let’s start creating a method called GenerateBulkData that populates and retrieves a DataTable;
  • Create an instance of the DataTable class called dt;
  • Add four columns to your DataTable:
    • source_id: integer
    • source_name: string;
    • source_active: boolean;
    • source_date: datetime.
  • Create a loop that goes from 1 to 5000 and add rows to the data table. The code will be similar to the following:

/// <summary>
/// Generates a DataTable with the content
/// to be copied into the table.
/// </summary>
/// <returns>DataTable with the content to be copied to the database.</returns>
private static DataTable GenerateBulkData()
{
    DataTable dt = new DataTable();
    dt.Columns.Add(“source_id”, typeof(int));
    dt.Columns.Add(“source_name”, typeof(string));
    dt.Columns.Add(“source_active”, typeof(bool));
    dt.Columns.Add(“source_date”, typeof(DateTime));

    for (int i = 1; i <= 5000; i++)
    {
        dt.Rows.Add(i, “Item “ + i, (i % 2 == 0), DateTime.Now.AddDays(i));
    }

    return dt;
}

You may have noticed that the DataTable created on this method has an extra column that does not exist in the database table shown on picture 1. I did that on purpose to show you how to map the columns between the source and destination tables. Let’s move on.

  • Create a method called InsertBulk that accepts a DataTable as parameter;
  • Create an instance of the SqlConnection class;
  • Create an instance of the SqlBulkCopy class using one of the constructor overloads available. For this example, I am going to pass the SqlConnection object created on the previous step as parameter;
  • Define the destination table name by setting the DestinationTableName to dbo.bulk_table;
  • Specify the buffer size by setting the BatchSize property to 100. Changing this value will affect performance. Make some tests to find the best value for your operation;
  • In this example we are going to receive notifications from time to time during the bulk process. We need to perform two tasks to accomplish that:
    • Define an interval based on a number of rows that dictates how often the notification event is raised. You define this interval by setting the NotifyAfter property. In this example, I am setting to 10;
    • Create an event handler for the SqlRowsCopied event so every time the copy reaches the number defined by the NotifyAfter property the event will be raised.
  • If your target and destination tables have the same column structure with the same names and sequence you may skip this step but it is a good practice to always generate a map between columns in both tables. The SqlBulkCopy object has a property called Mappings that is a collection of mappings between columns in both target and destination tables. You may add new items to the collection by using one of the five overloads available for the Add method:
    • SqlBulkCopyColumnMapping Add(SqlBulkCopyColumnMapping bulkCopyColumnMapping);
    • SqlBulkCopyColumnMapping Add(int sourceColumnIndex, int destinationColumnIndex);
    • SqlBulkCopyColumnMapping Add(int sourceColumnIndex, string destinationColumn);
    • SqlBulkCopyColumnMapping Add(string sourceColumn, int destinationColumnIndex);
    • SqlBulkCopyColumnMapping Add(string sourceColumn, string destinationColumn);
  • Let’s use for this example the last overload that accepts two strings containing the column names. We need to add three mapping objects to the collection that represent the id, name and active column. Defining that makes the Date property to be ignored;
  • Open the connection;
  • Invoke one of the WriteToServer method overloads to start the copy. In our example we are passing the DataTable object that the method receives as parameter;
  • Close the SqlBulkCopy instance;
  • Close the connection;
  • It’s done! The code must be similar to the following:

/// <summary>
/// Copies all rows from the
/// DataTable to the database table.
/// </summary>
/// <param name=”dt”>Source table.</param>

private static void InsertBulk(DataTable dt)
{
    /// Creates an instance of the SqlConnection object.
    using (SqlConnection conn = new SqlConnection(@”Data     Source=SERVERNAME;Initial Catalog=DBNAME;Integrated Security=true;”))
    {
        /// Creates an instance of the SqlBulkCopy object.
        
using (SqlBulkCopy bulk = new SqlBulkCopy(conn))
        {
            /// Specifies the destination table.
            bulk.DestinationTableName = “dbo.bulk_table”;

            /// Defines the number of rows to send to the server each time.
            /// Main property to define performance.

            bulk.BatchSize = 100;

            /// Defines the number of rows to be processed
            /// before raising a notification event.

            bulk.NotifyAfter = 10;

            /// Adds an event handler that will be raised when the
            /// number of rows processed reach the number specified
            /// by the NotifyAfter property above.

            bulk.SqlRowsCopied += new             SqlRowsCopiedEventHandler(bulk_SqlRowsCopied);

            /// Generates the mapping between the
            /// source destination tables.

            bulk.ColumnMappings.Add(“source_id”, “id”);
            bulk.ColumnMappings.Add(“source_name”, “name”);
            bulk.ColumnMappings.Add(“source_active”, “active”);

            /// Opens connection.
            conn.Open();

            /// Starts the bulk copy process.
            bulk.WriteToServer(dt);

            /// Closes the bulk copy instance.
            bulk.Close();

            /// Closes the database connection.
            conn.Close();
        }
    }
}

/// <summary>
/// SqlRowsCopied event handler.
/// </summary>
/// <param name=”sender”>Sender object.</param>
/// <param name=”e”>Event arguments.</param>
private static void bulk_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
    Console.WriteLine(“[{0}] {1} have been copied to the database table.”,     DateTime.Now, e.RowsCopied);
}

Advertisements

Responses

  1. Hi,

    I happened to see your post find it quite informative. I would like to share a link where a software engineer has shared a tip on “SqlBulkCopy In .NET”.

    Here is the link:
    http://www.mindfiresolutions.com/SqlBulkCopy-In-NET-215.php

    Hope you find it useful and of assistance.

    Thanks,
    Bijayani

  2. Thanks for sharing the link Bijayani!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: