Bulk Inserts

This scenario demonstrate how to perform bulk inserts. In order to improve performance, this uses database-specific APIs rather than SQL.

is used for large collections where SQL isn't appropriate. operations on collections of 1,000 to 100,000 objects. Some ORMs require special handling for collections of this size. Others use the same pattern seen in .

Smaller collections should be handled with a batch insert. This is described in CRUD Operations on Multiple Objects and Batch Inserts with Large Collections

Scenario Prototype

public interface IBulkInsertScenario<TEmployeeSimple>
   where TEmployeeSimple : class, IEmployeeSimple, new()
{
    /// <summary>
    /// Gets a collection of Employee rows by their name. Assume the name is not unique.
    /// </summary>
    int CountByLastName(string lastName);

    /// <summary>
    /// Insert a large collection of Employee rows.
    /// </summary>
    void BulkInsert(IList<TEmployeeSimple> employees);

    /// <summary>
    /// Insert a large collection of Employee rows using a DataTable.
    /// </summary>
    /// <param name="employees"></param>
    void BulkInsert(DataTable employees);
}

For ORMs that do not support bulk inserts from objects, this mapping function is provided.

[SuppressMessage("Design", "CA1000")]
public static DataTable CopyToDataTable(IEnumerable<IEmployeeSimple> employees)
{
    if (employees == null || !employees.Any())
        throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

    var result = new DataTable();
    result.Columns.Add("CellPhone", typeof(string));
    result.Columns.Add("EmployeeClassificationKey", typeof(int));
    result.Columns.Add("FirstName", typeof(string));
    result.Columns.Add("MiddleName", typeof(string));
    result.Columns.Add("LastName", typeof(string));
    result.Columns.Add("OfficePhone", typeof(string));
    result.Columns.Add("Title", typeof(string));

    foreach (var employee in employees)
    {
        var row = result.NewRow();
        row["CellPhone"] = employee.CellPhone;
        row["EmployeeClassificationKey"] = employee.EmployeeClassificationKey;
        row["FirstName"] = employee.FirstName;
        row["MiddleName"] = employee.MiddleName;
        row["LastName"] = employee.LastName;
        row["OfficePhone"] = employee.OfficePhone;
        row["Title"] = employee.Title;

        result.Rows.Add(row);
    }

    return result;
}

ADO.NET

ADO only supports bulk inserts from a DataTable.

public void BulkInsert(IList<EmployeeSimple> employees)
{
    if (employees == null || employees.Count == 0)
        throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

    var dataaTable = Utilities.CopyToDataTable(employees);

    using (var con = OpenConnection())
    using (var sbc = new SqlBulkCopy(con))
    {
        sbc.DestinationTableName = "HR.Employee";
        foreach (DataColumn? column in dataaTable.Columns)
            sbc.ColumnMappings.Add(column!.ColumnName, column.ColumnName);
        sbc.WriteToServer(dataaTable);
    }
}

public void BulkInsert(DataTable employees)
{
    if (employees == null)
        throw new ArgumentNullException(nameof(employees), $"{nameof(employees)} is null.");

    using (var con = OpenConnection())
    using (var sbc = new SqlBulkCopy(con))
    {
        sbc.DestinationTableName = "HR.Employee";
        foreach (DataColumn? column in employees.Columns)
            sbc.ColumnMappings.Add(column!.ColumnName, column.ColumnName);
        sbc.WriteToServer(employees);
    }
}

Chain

public void BulkInsert(IList<EmployeeSimple> employees)
{
    m_DataSource.InsertBulk(employees).Execute();
}

public void BulkInsert(DataTable employees)
{
    m_DataSource.InsertBulk(EmployeeTableName, employees).Execute();
}

Dapper

Dapper does not have support for bulk inserts.

DbConnector

Bulk inserts are an ADO.NET "data provider specific" implementation therefore DbConnector is not involved or necessary.

public void BulkInsert(IList<EmployeeSimple> employees)
{
    if (employees == null || employees.Count == 0)
        throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

    DataTable dtEmployees = Utilities.CopyToDataTable(employees);

    //This implementation is ADO.NET "data provider specific" therefore DbConnector is not involved
    using (var con = OpenConnection())
    using (var sbc = new SqlBulkCopy(con))
    {
        sbc.DestinationTableName = "HR.Employee";
        foreach (DataColumn? column in dtEmployees.Columns)
            sbc.ColumnMappings.Add(column!.ColumnName, column.ColumnName);
        sbc.WriteToServer(dtEmployees);
    }
}

public void BulkInsert(DataTable employees)
{
    if (employees == null)
        throw new ArgumentNullException(nameof(employees), $"{nameof(employees)} is null.");

    //This implementation is ADO.NET "data provider specific" therefore DbConnector is not involved
    using (var con = OpenConnection())
    using (var sbc = new SqlBulkCopy(con))
    {
        sbc.DestinationTableName = "HR.Employee";
        foreach (DataColumn? column in employees.Columns)
            sbc.ColumnMappings.Add(column!.ColumnName, column.ColumnName);
        sbc.WriteToServer(employees);
    }
}

Entity Framework 6

Entity Framework does not have support for bulk inserts.

Entity Framework Core

Entity Framework Core does not have support for bulk inserts.

LINQ to DB

LinqToDB only supports bulk inserts from a collection of objects.

Note the use of BulkCopyType.ProviderSpecific.

public void BulkInsert(IList<Employee> employees)
{
    var options = new BulkCopyOptions() { BulkCopyType = BulkCopyType.ProviderSpecific };
    using (var db = new OrmCookbook())
        db.BulkCopy(options, employees);
}

public void BulkInsert(DataTable employees)
{
    Assert.Inconclusive("Bulk insert using a DataTable is not supported.");
}

For more information see Bulk Copy (Bulk Insert)

LLBLGen Pro

LLBLGen Pro does not have support for bulk inserts.

NHibernate

NHibernate does not have support for bulk inserts.

RepoDb

public void BulkInsert(IList<EmployeeSimple> employees)
{
    if (employees == null || employees.Count == 0)
        throw new ArgumentException($"{nameof(employees)} is null or empty.", nameof(employees));

    this.BulkInsert<EmployeeSimple>(employees);
}

public void BulkInsert(DataTable employees)
{
    if (employees == null)
        throw new ArgumentNullException(nameof(employees), $"{nameof(employees)} is null.");

    this.BulkInsert<EmployeeSimple>(employees, rowState: DataRowState.Added);
}

ServiceStack

ServiceStack does not have support for bulk inserts.