EF6 Bulk Insert

2 views
AutomationFrameworkSoftwareSql

Entity Framework can be slow when inserting data using a foreach loop for example.
This Method uses the SqlBulkCopy class to speed things up.

void BulkInsert(string connection, string tableName, IList list)
{
using (var bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.BatchSize = list.Count;
bulkCopy.DestinationTableName = tableName;
var table = new DataTable();
var props = TypeDescriptor.GetProperties(typeof(T))
                          //Dirty hack to make sure we only have system data types 
                          //i.e. filter out the relationships/collections
                          .Cast()
                          .Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))
                          .ToArray();
foreach (var propertyInfo in props)
{
    bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
    table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
}
var values = new object[props.Length];
foreach (var item in list)
{
    for (var i = 0; i < values.Length; i++)
    {
        values[i] = props[i].GetValue(item);
    }
    table.Rows.Add(values);
}
bulkCopy.WriteToServer(table);
}
}
bd5e475