BulkInsert

This method inserts all rows from the client application into the database in bulk. It is supported only for SQL Server.

Call Flow Diagram

The diagram below shows the flow when calling this operation.

Use Case

Use this method to insert rows at high speed. It leverages the native bulk operation from ADO.NET via the SqlBulkCopy class.

For inserting 1,000 or more rows, prefer this method over InsertAll.

Special Arguments

The isReturnIdentity and usePhysicalPseudoTempTable arguments are available for this operation.

isReturnIdentity controls whether newly generated identity values are set back on the data entities. Disabled by default.

usePhysicalPseudoTempTable controls whether a physical pseudo-table is created during the operation. Applies only when isReturnIdentity is true. Defaults to a temporary table (e.g., #TableName).

Do not enable usePhysicalPseudoTempTable when using parallelism. Always use the session-based non-physical pseudo-temporary table in parallel scenarios.

Identity Setting Alignment

When isReturnIdentity is enabled, the library adds a column named __RepoDb_OrderColumn to the pseudo-temporary table whenever an identity field is present in the target table. This column holds the index of each entity model from the IEnumerable object.

During the bulk operation, a DbParameter is created targeting this column with the entity’s index value, ensuring correct order alignment. The pseudo-temporary table result set is ordered by this column before insertion into the target table.

When assigning identity values back to the data models, the __RepoDb_OrderColumn value is used to locate the correct entity in the IEnumerable object, and the compiled identity-setter function assigns the value to the identity property.

Usability

The following example defines a method that produces a list of Person objects, then bulk-inserts 10,000 rows into the [dbo].[Person] table.

private IEnumerable<Person> GetPeople(int count = 1000)
{
    for (var i = 0; i < count; i++)
    {
        yield return new Person
        {
            Name = $"Person-{i}",
            SSN = Guid.NewGuid().ToString(),
            IsActive = true,
            DateInsertedUtc = DateTime.UtcNow
        };
    }
}

using (var connection = new SqlConnection(connectionString))
{
    var people = GetPeople(10000);
    var insertedRows = connection.BulkInsert(people);
}

To specify a batch size:

using (var connection = new SqlConnection(connectionString))
{
    var people = GetPeople(10000);
    var insertedRows = connection.BulkInsert(people, batchSize: 100);
}

By default, the batch size is 10, equal to the Constant.DefaultBatchOperationSize value.

DataTable

using (var connection = new SqlConnection(connectionString))
{
    var people = GetPeople(10000);
    var table = ConvertToDataTable(people);
    var insertedRows = connection.BulkInsert<Person>(table);
}

Dictionary/ExpandoObject

using (var sourceConnection = new SqlConnection(sourceConnectionString))
{
    var result = sourceConnection.QueryAll("Person");
    using (var destinationConnection = new SqlConnection(destinationConnectionString))
    {
        var insertedRows = destinationConnection.BulkInsert("Person", result);
    }
}

DataReader

using (var sourceConnection = new SqlConnection(sourceConnectionString))
{
    using (var reader = sourceConnection.ExecuteReader("SELECT * FROM [dbo].[Person];"))
    {
        using (var destinationConnection = new SqlConnection(destinationConnectionString))
        {
            var rows = destinationConnection.BulkInsert<Person>(reader);
        }
    }
}

To bulk-insert via DataEntityDataReader:

using (var connection = new SqlConnection(connectionString))
{
    var people = GetPeople(10000);
    using (var reader = new DataEntityDataReader<Person>(people))
    {
        var insertedRows = connection.BulkInsert<Person>(reader);
    }
}

Column Mappings

Add column mappings using the BulkInsertMapItem class.

var mappings = new List<BulkInsertMapItem>();

// Add the mappings
mappings.Add(new BulkInsertMapItem("SourceId", "DestinationId"));
mappings.Add(new BulkInsertMapItem("SourceName", "DestinationName"));
mappings.Add(new BulkInsertMapItem("SourceIsActive", "DestinationIsActive"));
mappings.Add(new BulkInsertMapItem("SourceDateInsertedUtc", "DestinationDateInsertedUtc"));

// Execute
using (var connection = new SqlConnection(connectionString))
{
    var people = GetPeople(10000);
    var insertedRows = connection.BulkInsert(people,
        mappings: mappings);
}

Bulk Copy Options

Pass a SqlBulkCopyOptions value via the options argument.

using (var connection = new SqlConnection(connectionString))
{
    var people = GetPeople(10000);
    var insertedRows = connection.BulkInsert(people,
        options: SqlBulkCopyOptions.KeepIdentity);
}

Targeting a Table

To target a specific table, pass the literal table name.

using (var connection = new SqlConnection(connectionString))
{
    var people = GetPeople(10000);
    var insertedRows = connection.BulkInsert("[dbo].[Person]", people);
}

Table Hints

Pass a table hint via the hints argument.

using (var connection = new SqlConnection(connectionString))
{
    var people = GetPeople(10000);
    var insertedRows = connection.BulkInsert(people,
        hints: "WITH (TABLOCK)");
}

Or use the SqlServerTableHints class.

using (var connection = new SqlConnection(connectionString))
{
    var people = GetPeople(10000);
    var insertedRows = connection.BulkInsert(people,
        hints: SqlServerTableHints.TabLock);
}