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

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