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