Bulk upload into SQL Server using SQLBulkCopy and C#

Features of SQLBulkCopy

1. SQLBulkCopy constructor

   SQLBulkCopy initializes instance in four different ways.

   • Accepts already open SqlConnection of destination.
   • Accepts connection string of SQLConnection. This constructor actually opens and initializes a new instance of SQLConnection for the destination.
   • Accepts connection string of SQLconnection and enum value of SqlBulkCopyOptions. This constructor actually opens and initializes a new instance of SQLConnection for the destination.
   • Accepts already opened SQLConnection and enum value of SqlBulkCopyOptions.

   
       SqlBulkCopy bulkCopy =
       new SqlBulkCopy(destinationConnection.ConnectionString,  
           SqlBulkCopyOptions.TableLock))
   
   

2. BatchSize

   SQLBulkCopy BatchSize is an integer property with a default value of 0. It decides how many rows need to be sent 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 sent in a single batch.

   The following example sets BatchSize property as 50.

   
       bulkCopy.BatchSize = 50;
   
   

3. ColumnMappings

   SQLBulkCopy ColumnMappings is a collection of columns that needs to be map from the source table to destination table's columns. You do not need to map the columns if column names are the 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 an easy and readable method.

   The below code matches the column OrderID from the source table with column NewOrderID of the destination column.

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

   Data Type issue while mapping the column

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

   Below code converts Null to varchar(2) and can be mapped to any varchar(2) column of the 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).

4. DestinationTableName

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

   The below code will set the destination table as "TopOrders".

   
       bulkCopy.DestinationTableName = "TopOrders";    
   
   

5. NotifyAfter and SqlRowsCopied

   NotifyAfter is an integer property with a 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);
   }
               
               

6. WriteToServer

   WriteToServer is a method that actually processes your source table data to the destination table. It accepts an array of DataRows or DataTable or IDataReader. With DataTable you can also specify the state of the rows that need 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 a complete code that 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);
   }
       
   

Source Code on Github