Tuesday, 27 August 2013

Bulk Insert into SQL Server using SqlBulkCopy in C#

    Recently, I was in need to fetch and insert data in a specific interval (day or hour) from another DataBase system. First I tried to fetch row and pull into my DataBase one by one. But when I test with huge data, I have faced performance issue due to continues inserting process. Then I googled and find better way with SQL Bulk Copy operation in C# by using .NET DataTable.

A .NET DataTable is basically an in memory representation of an MS SQL Server table. DataTable allow you to create the table in memory, add rows to it, edit values in specific columns of a row, etc, until all the data is exactly what you want. Once the DataTable is ready, it is just a simple statement to insert all the data at once. So rather than hundreds of thousands of insert statements, it is just one bulk copy, and rather than taking minutes or longer to run, it just takes seconds to dump all the data into MS SQL Server. Also, because the data is all in memory, it makes it very easy to test all of our stats. We simply pass in the data we would receive and assert on the values in the DataTable.

The following code is a simple example where we are saving daily prodcut sales data for each product.

Create SQL Table

CREATE TABLE [dbo].[ProductSalesData](
 [SaleDate] [smalldatetime] NOT NULL,
 [ProductName] [nvarchar](1000) NOT NULL,
 [TotalSales] [int] NOT NULL)

Bulk Insert into MS SQL Server using SqlBulkCopy in C# with DataTable

using System;
using System.Data;
using System.Data.SqlClient;

namespace SqlBulkInsertExample
{
    class Program
    {
      static void Main(string[] args)
        {
            DataTable prodSalesData = new DataTable("ProductSalesData");

            // Create Column 1: SaleDate
            DataColumn dateColumn = new DataColumn();
            dateColumn.DataType = Type.GetType("System.DateTime");
            dateColumn.ColumnName = "SaleDate";

            // Create Column 2: ProductName
            DataColumn productNameColumn = new DataColumn();
            productNameColumn.ColumnName = "ProductName";

            // Create Column 3: TotalSales
            DataColumn totalSalesColumn = new DataColumn();
            totalSalesColumn.DataType = Type.GetType("System.Int32");
            totalSalesColumn.ColumnName = "TotalSales";

            // Add the columns to the ProductSalesData DataTable
            prodSalesData.Columns.Add(dateColumn);
            prodSalesData.Columns.Add(productNameColumn);
            prodSalesData.Columns.Add(totalSalesColumn);

            // Let's populate the datatable with our stats.
            // You can add as many rows as you want here!

            // Create a new row
            DataRow dailyProductSalesRow = prodSalesData.NewRow();
            dailyProductSalesRow["SaleDate"] = DateTime.Now.Date;
            dailyProductSalesRow["ProductName"] = "Nike";
            dailyProductSalesRow["TotalSales"] = 10;

            // Add the row to the ProductSalesData DataTable
            prodSalesData.Rows.Add(dailyProductSalesRow);

            // Copy the DataTable to SQL Server using SqlBulkCopy
            using (SqlConnection dbConnection = new SqlConnection("Data Source=ProductHost;Initial Catalog=dbProduct;Integrated Security=SSPI;Connection Timeout=60;Min Pool Size=2;Max Pool Size=20;"))
            {
                dbConnection.Open();
                using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
                {
                    s.DestinationTableName = prodSalesData.TableName;

                    foreach (var column in prodSalesData.Columns)
                        s.ColumnMappings.Add(column.ToString(), column.ToString());

                    s.WriteToServer(prodSalesData);
                }
            }
        }
    }
}

The Output is

select * from dbo.ProductSalesData

SaleDate               ProductName  TotalSales
27/08/2013 00:00:00    Nike         10

Related Articles:

Import CSV File Into SQL Server Using SQL Bulk Copy
How to read data from csv file in c# 
Get current time on a remote system using C# 
Convert DateTime to Ticks and Ticks to DateTime in C#
Convert Object To Byte Array and Byte Array to Object in C#
Add or Remove programs using C# in Control Panel 
Show balloon tooltip c#


Thanks,
Morgan
Software Developer

Advertisements
Advertisements

6 comments:

  1. Your create isn't quite right:
    CREATE TABLE [dbo].[ProductSalesData](
    [SaleDate] [smalldatetime] NOT NULL,
    [ProductName] [nvarchar](1000) NOT NULL,
    [TotalSales] [int] NOT NULL)

    ReplyDelete
  2. You iterating every column on "prodSalesData" and you are not doing any individual operation on columns, so I think you dont need separate objects for every column. Therefore, you can consider below approach;
    DataTable prodSalesData= new DataTable();
    table.Columns.Add("SaleDate", typeof(DateTime));
    table.Columns.Add("ProductName", typeof(string));
    table.Columns.Add("TotalSales", typeof(int));

    ReplyDelete
  3. Thank you very much! Man you have saved me a lot of time!

    ReplyDelete
  4. Will the foreach loop work if the destination table has auto generated key column like "ID"?

    ReplyDelete