My Adventures in Coding

May 27, 2012

C# – Bulk copying data into MS SQL Server with DataTables

Filed under: c#,SQL Server 2005 — Brian @ 9:45 pm
Tags: , , ,

Recently, we were asked to start pulling data daily from a number of sources (e.g., several REST APIs), aggregating the data, and saving it to a database to be used for generating reports. As usual we want to make sure the application is easy to test (We need to make sure those stats are correct!), but we also need to ensure it performs well because we will be adding possibly hundreds of thousands of rows daily to a number of different tables every time this job runs. We were worried that the bottleneck in this application would be in running all those insert statements against our MS SQL Server database. However, we were in luck, .NET has a handy feature called DataTables.

A DataTable is basically an in memory representation of an MS SQL Server table. DataTables 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 SqlBulkCopy 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 DataTables. That’s all!

The following is a simple example where we are saving daily sales figures for each sales person.

Create the table

CREATE TABLE [dbo].[DailySalesStats](
	[Date] [smalldatetime] NOT NULL,
	[SalesPersonId] [int] NOT NULL,
	[TotalSales] [int] NOT NULL,
 CONSTRAINT [PK_DailySalesStats] PRIMARY KEY CLUSTERED 
(
	[Date] ASC,
	[SalesPersonId] ASC
)) ON [PRIMARY]

Example of writing sales stats to the dbo.DailySalesStats table using a DataTable and SqlBulkCopy

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

namespace DataTableExample
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a datatable with three columns:
            DataTable dailySalesStats = new DataTable("DailySalesStats");

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

            // Create Column 2: SalesPersonId
            DataColumn salesPersonIdColumn = new DataColumn();
            salesPersonIdColumn.DataType = Type.GetType("System.Int32");
            salesPersonIdColumn.ColumnName = "SalesPersonId";

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

            // Add the columns to the dailySalesStats DataTable
            dailySalesStats.Columns.Add(dateColumn);
            dailySalesStats.Columns.Add(salesPersonIdColumn);
            dailySalesStats.Columns.Add(totalSalesColumn);

            // Let's populate the datatable with our stats, which could come from REST APIs, etc.
            // You can add as many rows as you want here!

            // Create a new row
            DataRow dailySalesStatsRow = dailySalesStats.NewRow();
            dailySalesStatsRow["Date"] = DateTime.Now.ToString("yyyy-MM-dd");
            dailySalesStatsRow["SalesPersonId"] = 1;
            dailySalesStatsRow["TotalSales"] = 2;

            // Add the row to the dailySalesStats DataTable
            dailySalesStats.Rows.Add(dailySalesStatsRow);

            // Copy the DataTable to SQL Server
            using(SqlConnection dbConnection = new SqlConnection("Data Source=dbhost;Initial Catalog=dbname;Integrated Security=SSPI;Connection Timeout=60;Min Pool Size=2;Max Pool Size=20;"))
            {
                dbConnection.Open();
                using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
                {
                    s.DestinationTableName = dailySalesStats.TableName;
                    foreach (var column in dailySalesStats.Columns)
                        s.ColumnMappings.Add(column.ToString(), column.ToString());
                    s.WriteToServer(dailySalesStats);
                }
            }

            // That's it, we are done!
        }
    }
}

The Results

select * from dbo.DailySalesStats

Date                SalesPersonId  TotalSales
2012-05-27 00:00:00	   1	        2

Overall I have been really happy with the DataTable and SqlBulkCopy features in .NET. It is a fast and easy way to load a lot of data into a MS SQL Server database and is a good fit for our statistics gathering job!

About these ads

3 Comments »

  1. It helps me to insert data from Excel to sql 2005 and a nice post
    Thank you Brian

    Comment by Asif Iqbal — August 29, 2012 @ 6:33 am | Reply

    • Thanks, I am glad it helped!

      Comment by Brian — August 29, 2012 @ 8:10 am | Reply

  2. nice article, we can also look at http://www.morgantechspace.com/2014/04/Insert-DataTable-into-SQL-Table-in-C-Sharp.html

    Comment by Dada — May 4, 2014 @ 12:44 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 26 other followers

%d bloggers like this: