BinaryBulkUpdate

This method is used to update the existing rows from 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 update existing rows from 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 update range of rows from 1000 or more, then use this method over the UpdateAll operation.

Special Arguments

The qualifiers, keepIdentity and pseudoTableType arguments were provided on this operation.

The qualifiers is used to define the qualifier fields to be used in the operation. It usually refers to the WHERE expression of the SQL Statement. If not given, the primary column will be used.

The keepIdentity is used to define a value whether the identity property of the entity/model will be kept during 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 updatedRows = connection.BinaryBulkUpdate<Person>(people);
}

It returns the number of rows updated 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 updatedRows = connection.BinaryBulkUpdate<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 updatedRows = connection.BinaryBulkUpdate("[dbo].[Person]", people);
}

DataTable

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

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

Dictionary/ExpandoObject

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

var people = GetPeopleAsDictionary(1000);

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

DataReader

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

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

Or via DataEntityDataReader class.

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

Field Qualifiers

By default, this operation is using the primary column as the qualifier. You can override the qualifiers by simply passing the list of Field object in the qualifiers argument.

using (var connection = new NpgsqlConnection(connectionString))
{
    var updatedRows = connection.BinaryBulkUpdate<Person>(people,
        qualifiers: e => new { e.LastName, e.DateOfBirth });
}

When using the qualifiers, we recommend that you use the list of columns that is indexed from the target table to maximize the performance.

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 updatedRows = connection.BinaryBulkUpdate("[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.