Link Search Menu Expand Document

BinaryBulkInsert


This method is used to insert multiple rows towards the database by bulk. It is only supporting the PostgreSQL RDBMS.

Call Flow Diagram

The diagram below shows the flow when calling this operation.

Use Case

This method is very useful if you would like to insert multiple rows towards the database in a very speedy manner. It is high-performant in nature as it is using the real bulk operation natively from the Npgsql library (via the NpgsqlBinaryImporter class).

If you are working to insert range of rows from 1000 or more, then use this method over the InsertAll operation. Alternatively, you can also use the BinaryImport operation.

Special Arguments

The identityBehavior and pseudoTableType arguments were provided on this operation.

The identityBehavior is used to define a value whether an identity property of the entity/model will be kept, or, the newly generated identity values from the database will be returned after the operation.

The pseudoTableType is used to define a value whether a physical pseudo-table will be created during the operation. By default, a temporary table is used.

It is highly recommended to use the BulkImportPseudoTableType.Temporary value in the pseudoTableType argument when working with parallelism.

Usability

Simply pass the list of the entities when calling this operation.

using (var connection = new NpgsqlConnection(connectionString))
{
    var people = GetPeople(1000);
    var insertedRows = connection.BinaryBulkInsert<Person>(people);
}

It returns the number of rows inserted into the underlying table.

And below if you would like to specify the batch size.

using (var connection = new NpgsqlConnection(connectionString))
{
    var people = GetPeople(1000);
    var insertedRows = connection.BinaryBulkInsert<Person>(people, batchSize: 100);
}

If the batchSize argument is not set, then all the items from the collection will be sent together.

You can also target a specific table by passing the literal table name like below.

using (var connection = new NpgsqlConnection(connectionString))
{
    var insertedRows = connection.BinaryBulkInsert("[dbo].[Person]", people);
}

DataTable

Below is the sample code to bulk-insert via data table.

using (var connection = new NpgsqlConnection(connectionString))
{
    var people = GetPeople(1000);
    var table = ConvertToDataTable(people);
    var insertedRows = connection.BinaryBulkInsert("[dbo].[Person]", table);
}

Dictionary/ExpandoObject

Below is the sample code to bulk-insert via Dictionary<string, object> or ExpandoObject.

var people = GetPeopleAsDictionary(1000);

using (var connection = new NpgsqlConnection(destinationConnectionString))
{
    var insertedRows = connection.BinaryBulkInsert("[dbo].[Person]", people);
}

DataReader

Below is the sample code to bulk-insert via DbDataReader.

using (var sourceConnection = new NpgsqlConnection(sourceConnectionString))
{
    using (var reader = sourceConnection.ExecuteReader("SELECT * FROM [dbo].[Person];"))
    {
        using (var destinationConnection = new NpgsqlConnection(destinationConnectionString))
        {
            var insertedRows = destinationConnection.BinaryBulkInsert("[dbo].[Person]", reader);
        }
    }
}

Or via DataEntityDataReader class.

using (var connection = new NpgsqlConnection(connectionString))
{
    using (var reader = new DataEntityDataReader<Person>(people))
    {
        var insertedRows = connection.BinaryBulkInsert("[dbo].[Person]", reader);
    }
}

Physical Temporary Table

To use a physical pseudo-temporary table, simply pass the BulkImportPseudoTableType.Temporary value in the pseudoTableType argument.

using (var connection = new NpgsqlConnection(connectionString))
{
    var insertedRows = connection.BinaryBulkInsert("[dbo].[Person]",
        people,
        pseudoTableType: BulkImportPseudoTableType.Physical);
}

By using the actual pseudo physical temporary table, it will further help you maximize the performance over using the normal temporary table. However, you need to be aware that the table is shared to any call, so parallelism may fail on this scenario.