Write Data to SQL Server Tables Using SqlBulkCopy

SqlBulkCopy is a new feature in ADO .NET 2.0 that speed up a copy operation for a large amount of data from your .NET application to SQL Server tables.

The SqlBulkCopy class can be used to write data only to SQL Server tables from different types of data source, as long as the data can be loaded to a DataTable instance or read with an IDataReader instance.

SqlBulkCopy contains an instance method WriteToServer, which is used to transfer data from a source to the destination table. SqlBulkCopy uses a collection of SqlBulkCopyColumnMapping objects to map a column in a data source to a field in the destination table.

There are some essential properties of SqlBulkCopy that you should be aware of:
  § BatchSize: Number of rows SqlBulkCopy will copy from data source to the destination table.
  § BulkCopyTimeOut: The number of seconds that system should wait to complete the copy operation.
  § ColumnMappings: You can use its Add() method to add in a new SqlBulkCopyColumnMapping object to its collection.
  § DestinationTableName
  § NotifyAfter: SqlRowsCopied event handler will be triggered when the number of rows specified has been copied. This is very helpful if you want to be aware the progress of a copy operation, showing completed % in a progress bar, for example.


Sample Codes
Here, I will demonstrate how to import data from CSV source file into database.

…
using System.Data.SqlClient;
…
…
private void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e) {
    MessageBox.Show(String.Format("{0} rows have been copied.", e.RowsCopied.ToString()));
}

private void CopyDataToDestinationTable()
{

    string strconn = "
server=localhost;Database=Transaction;User Id=sa;Password=123456;";
    OleDbConnection conn = new OleDbConnection();
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\;Extended Properties=\"text;HDR=Yes;FMT=CSVDelimited\"";

    try {
        conn.Open();

        OleDbCommand myCommand = conn.CreateCommand();

        string commandString = "select * from source.csv";
        myCommand.CommandType = CommandType.Text;
        myCommand.CommandText = commandString;

        OleDbDataReader dataReader = myCommand.ExecuteReader();
        dataReader.Read();

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(strconn)) {

            bulkCopy.DestinationTableName = "Products";
            bulkCopy.BulkCopyTimeout = 100000;
            bulkCopy.NotifyAfter = 1000;
            bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
            bulkCopy.ColumnMappings.Add("Brand", "Brand");
            bulkCopy.ColumnMappings.Add("Model", "Model");
            bulkCopy.ColumnMappings.Add("ProductName", "ProductName");

            // call WriteToServer which starts import
            bulkCopy.WriteToServer(dataReader);
        }
    }
    catch (Exception ex) {
        MessageBox.Show(ex.Message);
    }
    finally {
        conn.Close();
    }
}

Well, that’s it. Feel free to ask if there is any problem. Cheers!