Bulk Data Inserts

Summary

In this blog post I’m going to demonstrate how to do a bulk data insert using ADO. I’ll be adding code to my helper set of tools so that you can use the ADO database code at my GitHub account to write clean ADO database methods.

Bulk Inserts

The C# SqlClient library contains a method called SqlBulkCopy, which is described in this blog post: Bulk Insert into SQL Server using SqlBulkCopy in C# 

The first thing I’m going to do is add a static helper object to contain all of this code:

DataColumn dateColumn = new DataColumn();
dateColumn.DataType = Type.GetType("System.DateTime");
dateColumn.ColumnName = "SaleDate";
prodSalesData.Columns.Add(dateColumn);

I wanted to contain this in one helper method in order to avoid a lot of repetition. So I created this object/method to handle it:

public static class DataSetHelpers
{
    public static void AddDataColumn(this DataTable dataTable, 
       string fieldName, string dataType)
    {
        DataColumn dataColumn = new DataColumn();
        dataColumn.DataType = Type.GetType(dataType);
        dataColumn.ColumnName = fieldName;
        dataTable.Columns.Add(dataColumn);
    }
}

Inside my ADODatabaseContext object, I added this method:

public void BulkInsert(DataTable dataTable)
{
    using (SqlBulkCopy s = new SqlBulkCopy(_db))
    {
        s.DestinationTableName = dataTable.TableName;
        s.BulkCopyTimeout = CommandTimeOut;

        foreach (var column in dataTable.Columns)
        {
            s.ColumnMappings.Add(column.ToString(), 
               column.ToString());
        }

        s.WriteToServer(dataTable);
    }
}

Now the bulk insert consists of two parts: The DataTable definition and the DataSet populate. You can create a custom method in your code to populate each row with one method call. This would be specific to the table you are populating, I’m going to leave that part expanded. Here’s the object I used to bulk insert into a table named “Store”:

public class CreateStoreRecords
{
    private DataTable StoreData;

    private void SetupDataTable()
    {
        StoreData = new DataTable("Store");

        StoreData.AddDataColumn("Name", "System.String");
        StoreData.AddDataColumn("Address", "System.String");
        StoreData.AddDataColumn("City", "System.String");
        StoreData.AddDataColumn("State", "System.String");
        StoreData.AddDataColumn("Zip", "System.String");
    }

    public void InsertRecords()
    {
        SetupDataTable();

        using (var db = new ADODatabaseContext("Server=sqlserver;
          Integrated Security=True"))
        {
            DataRow StoreList = StoreData.NewRow();
            StoreList["Name"] = "Toys R Us";
            StoreList["Address"] = "123 Main St";
            StoreList["City"] = "Chicago";
            StoreList["State"] = "IL";
            StoreList["Zip"] = "12345";
            StoreData.Rows.Add(StoreList);

            StoreList = StoreData.NewRow();
            StoreList["Name"] = "Target";
            StoreList["Address"] = "5th Ave";
            StoreList["City"] = "New York";
            StoreList["State"] = "NY";
            StoreList["Zip"] = "33333";
            StoreData.Rows.Add(StoreList);

            db.BulkInsert(StoreData);
        }
    }
}

If you want to create the store table in MS SQL Server to match my sample code, then you can use this code in a query window to generate it:

CREATE TABLE [dbo].[Store](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
    [Address] [varchar](50) NULL,
    [City] [varchar](50) NULL,
    [State] [varchar](50) NULL,
    [Zip] [varchar](50) NULL,
 CONSTRAINT [PK_Store] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

You’ll have to replace the connection string to match your server and access rights (assuming you’re not using integrated security).

Where to Get The Code

You can download the sample code from above at my GitHub account by clicking here.