Bulk Insert a .NET List to Database

• Download source code - 357.1 KB

Introduction

The SqlBulkCopy class provides an efficient means to import data into a SQL Server database. Even so, out of the box, it supports data import only from one of the following types:

• DataTable
• DataRow[]
• IDataReader

In this article, we will look at a simple yet efficient mechanism to bulk insert data from any IList<T> type.

Background

There are times when we may need to bulk upload information available in .NET objects to the DB. The SqlBulkCopy class lets one efficiently bulk load a SQL Server table with data from another source. However, to make use of this, we need to either implement IDataReader on every such type that needs to be bulk persisted or rebuild the data as a DataTable (DataRow[]).

Creating a DataTable version of the object data may not be desirable, especially when the data is huge.

On the other hand, implementing IDataReader on a type presents with an intimidating 33 methods to define:

• GetName(int i):string
• GetDataTypeName(int i):string
• GetFieldType(int i):Type
• GetValues(object[] values):int
• GetBoolean(int i):bool
• GetByte(int i):byte
• GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length):long
• GetChar(int i):char
• GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length):long
• GetGuid(int i):Guid
• GetInt16(int i):short
• GetInt32(int i):int
• GetInt64(int i):long
• GetFloat(int i):float
• GetDouble(int i):double
• GetString(int i):string
• GetDecimal(int i):decimal
• GetDateTime(int i):DateTime
• GetData(int i):IDataReader
• IsDBNull(int i):bool
• this[int i]:object
• this[string name]:object
• Close():void
• GetSchemaTable():DataTable

Luckily, only the following handful of methods need to be defined for a bulkcopy:

• Dispose():void
• GetValue(int i):object
• IsDBNull
• GetOrdinal(string name):int
• FieldCount:int
• Read():bool
• Depth:int
• IsClosed:bool
• RecordsAffected:int

The DataReaderAdapter class presented here saves the effort of implementing this reduced set of methods as long as the data is presented as an IList<T>. The DataReaderAdapter wraps around the list and exposes itself as an IDataReader that can be directly consumed by the SqlBulkCopy’s ‘WriteToServer’ method.

Using the Code

Using the DataReaderAdapter is quite simple:

• Instantiate a DataReaderAdapter with an IList of the type that needs to be persisted.
• Pass the DataReaderAdapter to the SqlBulkCopy.WriteToServer() method.

//Generate a list of 10,000 Customer records
var customers = _fixture.CreateMany<Customer>(10000).ToList();
var customerDr = new DataReaderAdapter<Customer>(customers);
 
using (var connection = new SqlConnection(_connectionString))
{
    connection.Open();
    var bulkCopy = new SqlBulkCopy(connection)
    {
        DestinationTableName = "[dbo].[Customer]",
        BatchSize = 1000
    };
    bulkCopy.WriteToServer(customerDr);
    bulkCopy.Close();
}

Running the Tests

Pre requisites

1. Execute the Db script files under the AdapterTests/DbScripts folder on a SQL Server database.
   • Create_Table_Customer.sql
   • Create_Table_Skus.sql
2. Modify the connection string in app.config to point to the above database.

The test cases demonstrate the following scenarios:

Should InsertListToDb

When the properties of the .NET type and the DB column names match exactly in terms of the Names and the order in which they are defined.

Should InsertListToDbWithColumnMappings

When the names of properties of the .NET type do not match with the DB column names.

In this case, the SqlBulkCopy needs to be provided with a list of column mappings that help it identify which properties go to what columns in DB.

History

• 15^th January, 2018: Initial version