BinaryImport

This method inserts multiple rows into the database in bulk. It is supported only for PostgreSQL.

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 the Npgsql library via the NpgsqlBinaryImporter class.

For inserting 1,000 or more rows, prefer this method over InsertAll. The BinaryBulkInsert operation is also available as an alternative.

Special Arguments

The keepIdentity argument controls whether the identity property of the entity/model is preserved during the operation.

Usability

Pass the list of entities to the operation.

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

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

To specify a batch size:

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

If batchSize is not set, all items in the collection are sent at once.

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

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

DataTable

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

Dictionary/ExpandoObject

var people = GetPeopleAsDictionary(1000);

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

DataReader

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

Or via DataEntityDataReader.

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