Bulk upload into SQL Server using SQLBulkCopy and C#


In this article I am going to write about SQLBulkCopy and its major properties and methods. This article will give you the code for high performance transfer of rows from XML file to SQL server with SQLBulkCopy and C#.

SQLBulkCopy introduced as part of .Net framework 2.0. It is simple and easy tool to transfer complicated or simple data from one data source to other. You can read data from any data source as long as that data can be load to DataTable or read by IDataReader and transfer the data with high performance to SQL Server using SQLBulkCopy.

In real time applications every day millions of records get transferred from one data store to other. There are multiple ways to transfer the data like command prompt bcp utility of SQL Server, creating INSERT statements, creating SSIS packages and SQLBulkCopy. SQLBulkCopy gives you significant performance gain over other tools.


Features of SQLBulkCopy


SQLBulkCopy constructor

SQLBulkCopy initializes instance in four different way.

  1. Accepts already open SqlConnection for destination.
  2. Accepts connection string of SQLConnection. This constructor actually opens and initializes new instance of SQLConnection for destination.
  3. Accepts connection string of SQLconnection and enum value of SqlBulkCopyOptions. This constructor actually opens and initializes new instance of SQLConnection for destination.
  4. Accepts already opened SQLConnection and enum value of SqlBulkCopyOptions.
            
    SqlBulkCopy bulkCopy =
            new SqlBulkCopy(destinationConnection.ConnectionString,  
                SqlBulkCopyOptions.TableLock))
            
         

BatchSize

SQLBulkCopy BatchSize is integer property with default value of 0. It decides how many rows need to be send to the server in one batch. If you do not set any value for this property or set it as 0, all the records will be send in single batch.

Following example sets BatchSize property as 50.

                
    bulkCopy.BatchSize = 50;
                
            

ColumnMappings

SQLBulkCopy ColumnMappings is a collection of columns which needs to be map from source table to destination table's columns. You do not need to map the columns if column names are same. However it is very important to map the columns if column names are different. If matching SQLBulkCopy does not found the matching column it throws System.InvalidOperationException.

You can map the columns in different ways, giving both column names is easy and readable method.

Below code match the column OrderID from source table with column NewOrderID of destination column.

        
bulkCopy.ColumnMappings.Add("OrderID", "NewOrderID");   
        

Data Type issue while mapping the column

SqlBulkCopy is particular about matching column DataType. Both the columns has to be of same DataType. If you have nullable columns, you explicitly have to convert such columns into desired DataType.

Below code converts Null to varchar(2) and can be mapped to any varchar(2) column of destination table.

        
SELECT  CAST(ISNULL(ShipRegion,'') as varchar(2))
            as ShipRegion FROM Orders
        

Quick note: If you are having computed columns like SUM, AVG etc. make sure it returns in expected DataType. If your destination table expects columns with decimal(15,7) you will have to explicitly convert the source column as decimal(15,7) because SUM will by default return decimal(38,7).


DestinationTableName

It sets the name of destination table. The method WriteToServer will copy the source rows to this particular table.

Below code will set the destination table as "TopOrders".

        
bulkCopy.DestinationTableName = "TopOrders";    
            

NotifyAfter and SqlRowsCopied

NotifyAfter is an integer property with default value of 0 and SqlRowsCopied is an event. The value of NotifyAfter indicates when to raise event SqlRowsCopied.

The below code shows after processing 100 rows, event SqlRowsCopied will be executed.

                
bulkCopy.SqlRowsCopied += 
    new SqlRowsCopiedEventHandler(OnSqlRowsTransfer);
bulkCopy.NotifyAfter = 100;

private static void 
    OnSqlRowsTransfer(object sender, SqlRowsCopiedEventArgs e)
{
        Console.WriteLine("Copied {0} so far...", e.RowsCopied);
}
                
            

WriteToServer

WriteToServer is a method which actually processes your source table data to destination table. It accepts array of DataRows or DataTable or IDataReader. With DataTable you can also specify the state of the rows that needs to be processed.

The following code will process rows from sourceData DataTable which has RowState as Added to DestinationTable.

           
bulkCopy.WriteToServer(sourceData, DataRowState.Added);
           
        

Below is full code which Transfer data from BulkUploadOrders.xml which can be download from here. to Northwind Database. Northwind database can be download from here. Create a new table TopOrders and set the connection string to code.

    
static void Main(string[] args)
{
    string connectionString = @"<Your connection string>";
            
    DataSet ds = new DataSet();
    DataTable sourceData = new DataTable();
    ds.ReadXml(@"<enter path of BulkUploadOrders.xml>");
    sourceData = ds.Tables[0];
        
    // open the destination data
    using (SqlConnection destinationConnection =
                    new SqlConnection(connectionString))
    {
        // open the connection
        destinationConnection.Open();
        using(SqlBulkCopy bulkCopy =
                new SqlBulkCopy(destinationConnection.ConnectionString,  
                    SqlBulkCopyOptions.TableLock ))
        {
            bulkCopy.SqlRowsCopied += 
                new SqlRowsCopiedEventHandler(OnSqlRowsTransfer);
            bulkCopy.NotifyAfter = 100;
            bulkCopy.BatchSize = 50;
            bulkCopy.ColumnMappings.Add("OrderID", "NewOrderID");     
            bulkCopy.DestinationTableName = "TopOrders";
            bulkCopy.WriteToServer(sourceData);
        }
    }
}

private static void OnSqlRowsTransfer(object sender,
        SqlRowsCopiedEventArgs e)
{
    Console.WriteLine("Copied {0} so far...", e.RowsCopied);
}
    

Download source code.

Speak your mind :
Leave a comment for this article on dotnetbloogers.com
User profile picture on dotnetmentors.com

by peter piper at 4/15/2015 8:33:00 PM
I can't get your code to work . At this point "bulkCopy.WriteToServer(sourceData, DataRowState.Added);" I get an error that login for user xx failed
post comment on dotnetmentors.com
User profile picture on dotnetmentors.com

by Laxmikant at 4/15/2015 9:12:00 PM
Please check the connection string of source. You can just use SQLConnection object, set the connection string and try to open using connection.open() method.
post comment on dotnetmentors.com
User profile picture on dotnetmentors.com

by peter piper at 5/19/2015 3:44:00 AM
Thank you. Now working. One other question if I may. How to do the above with an xml file that has attributes ? Would really appreciate your guidance on this
post comment on dotnetmentors.com