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.
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
- Execute the Db script files under the AdapterTests/DbScripts folder on a SQL Server database.
- Create_Table_Customer.sql
- Create_Table_Skus.sql
- 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
- 15th January, 2018: Initial version