Bulk insert records into SQL Server with DataTable in C#(c sharp) using SqlBulkCopy

Recently I got a task to upload heavy data to SQL server database. Data was in XML files and I am to upload that files in the database. Size of XML file was big. First I tried to fetch data and pull into my Data Base one by one. I faced performance issue due to continues inserting process. So decided to read XML file in the data table and upload this data table to SQL server database using SqlBulkCopy class. It was much better to copy data table to the database using bulk copy operation in c sharp.

The data table is being used to store SQL server data in memory for local use. So we can hold XML file data in Data table and bulk copy complete data of data table in the database with in seconds.

Below is a simple example for this.

First Create a table for testing it.

 CREATE TABLE [dbo].[YourDataTable](
      [TimeStamp] [smalldatetime] NOT NULL,
      [Name] [nvarchar(1000)] NOT NULL,
      [Total] [int] NOT NULL)

Now we can suppose that your XML file also will be with Same nodes. TimeStamp, Name and Total

Below is a method which takes data table as input and uploads that table in the database. So we can Read XML file by using DataTable.ReadXml method and pass that data table to this method. So now data table will be having a table with same columns as XML file was having nodes. TimeStamp, Name and Total. We also need to set the name of DataTable same as the Table name in the database. So I am setting name in the first line of code. This is a sample method so I am writing everything in it. You can modify it as per requirement.

private void BulkUpload(DataTable dt)
{
    dt.TableName="YourDataTable";
    string constr="your connection string";
    using(SqlConnection connection=new SqlConnection(constr))
    {
      connection.Open();
      //CreatingTranscationsothatitcanrollbackifgotanyerrorwhileuploading
      SqlTransaction trans=connection.BeginTransaction();
     //Start bulkCopy
     using(SqlBulkCopy bulkCopy=new SqlBulkCopy(connection,
     SqlBulkCopyOptions.TableLock|
     SqlBulkCopyOptions.FireTriggers,
     trans))
     {
       //Setting timeout to 0 means no time out for this command will not timeout until upload complete.
     //Change as per you
     bulkCopy.BulkCopyTimeout=0;
     bulkCopy.DestinationTableName=dt.TableName;
     //write the data in the "dataTable"
     bulkCopy.WriteToServer(dt);
    }
   }
}

Above method will dump Data table in SQL server. But we have to take care some common mistakes as below.

1. Name of the table in the database should match with the name of the data table.

2. This will work only with SQL server database.

3. Name of Columns in the database should match Columns name of the data table.

4. Name mapping of table name and columns name is case sensitive. Should be same with case matching.

Sometimes we face the issue that columns name on SQL server and data table does not match (case mismatching or having the different name or we need to upload data of selected columns in database and want to skip other columns

In both above case we can map column names of data table with names in sql server databas

private void BulkUpload(DataTable dt)
{
    dt.TableName="YourDataTable";
    string constr="yourconnectionstring";
    using(SqlConnection connection=new SqlConnection(constr))
    {
      connection.Open();
      //CreatingTranscationsothatitcanrollbackifgotanyerrorwhileuploading
      SqlTransaction trans=connection.BeginTransaction();
     //Start bulkCopy
     using(SqlBulkCopy bulkCopy=new SqlBulkCopy(connection,
     SqlBulkCopyOptions.TableLock|
     SqlBulkCopyOptions.FireTriggers,
     trans))
     {
       //Setting timeout to 0 means no time out for this command will not timeout until upload complete.
     //Change as per you
     bulkCopy.BulkCopyTimeout=0;
     //Setting We are mapping only 2 columns so it will upload only for these
     bulkCopy.ColumnMappings.Add(“TimeStamp”,”TimeStamp”); bulkCopy.ColumnMappings.Add(“Name”,”Name”); bulkCopy.DestinationTableName=dt.TableName; //write the data in the "dataTable" bulkCopy.WriteToServer(dt); } } }