Wednesday, December 31, 2008

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!

5 comments:

Anonymous said...

i also want to display progress in percentage while transfer.

is it possible? and if yes then how?

Anonymous said...

I am trying to import data from excel sheet to SQL Server. I am using the same way as you are but I got this error message "The given value of type String from the data source cannot be converted to type nvarchar of the specified target column."

The datatype I used in the table is nvarchar while the data read from excel is String.

Please help.

ejuly on April 9, 2009 at 4:02 PM said...

Is it possible that the column length is not big enough to hold all the values?

Yogesh on August 17, 2011 at 5:21 PM said...

Is There any special rights required on target database? like dbcreator ...etc
what are the minmum privelages required on target database?

xiaoyu on August 17, 2011 at 8:31 PM said...

Hi Yogesh, I didnt try it out, may be you can share with me if you did.

 

Get paid for your opinions! Click on the banner above to join Planet Pulse. Its totally free to sign up, and you can earn UNLIMITED. Find out more by visiting PLANET PULSE.
July Code Blog Copyright © 2010 Blogger Template Designed by Bie Blogger Template