BulkUpdate

This method is used to update all the rows from the client application into the database by bulk. It is only supporting the SQL Server RDBMS.

Call Flow Diagram

The diagram below shows the flow when calling this operation.

Use Case

This method is very useful if you are updating 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 ADO.NET (via SqlBulkCopy class).

If you are working to update range of rows from 1000 or more, then use this method over the UpdateAll.

Special Arguments

The arguments qualifiers and usePhysicalPseudoTempTable is 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 usePhysicalPseudoTempTable is used to define whether a physical pseudo-table will be created during the operation. By default, a temporary table (i.e.: #TableName) is used.

It is not recommended to enable the usePhysicalPseudoTempTable argument if you are to work with parallelism. Ensure to always utilize the session-based non-physical pseudo-temporary table when working with parallelism.

Caveats

RepoDB is automatically setting the value of the options argument to SqlBulkCopyOptions.KeepIdentity when calling this method and if you have not passed any qualifiers and if your table has an IDENTITY primary key column. The same logic will apply if there is no primary key but has an IDENTITY column defined in the table.

In addition, when calling this method, the library is creating a pseudo temporary table behind the scene. It requires your user to have the correct privilege to create a table in the database, otherwise a SqlException will be thrown.

Usability

Let us say you have a list of Person model at variable people that contains both existing and non-existing rows from the database.

Then, below is the code to bulk-update those rows [dbo].[Person] table.

using (var connection = new SqlConnection(connectionString))
{
    var updatedRows = connection.BulkUpdate(people);
}

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

using (var connection = new SqlConnection(connectionString))
{
    var updatedRows = connection.BulkUpdate(people, batchSize: 100);
}

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

DataTable

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

using (var connection = new SqlConnection(connectionString))
{
    var table = ConvertToDataTable(people);
    var updatedRows = connection.BulkUpdate<Person>(table);
}

Dictionary/ExpandoObject

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

using (var sourceConnection = new SqlConnection(sourceConnectionString))
{
    var result = sourceConnection.QueryAll("Person");
    using (var destinationConnection = new SqlConnection(destinationConnectionString))
    {
        var updatedRows = destinationConnection.BulkUpdate("Person", result,
            qualifiers: Field.From("LastName", "DateOfBirth"));
    }
}

DataReader

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

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

Or you can also convert the list of Person into a DataEntityDataReader object and bulk-update it.

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

Field Qualifiers

By default, this method is using the primary column as the qualifier. You can override it by passing the list of Field objects in the qualifiers argument.

using (var connection = new SqlConnection(connectionString))
{
    var people = GetPeople(10000);
    var updatedRows = connection.BulkUpdate<Person>(people,
        qualifiers: new { e.LastName, e.DateOfBirth });
}

When using the qualifiers, we recommend that you use the list of columns that has the correct index from the original table.

Column Mappings

You can add a mapping via 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 updatedRows = connection.BulkUpdate(people,
        mappings: mappings);
}

Bulk Copy Options

You can define your bulk-copy options by passing a value of SqlBulkCopyOptions in the options argument.

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

Targeting a Table

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

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

Table Hints

To pass a hint, simply write the table-hints and pass it in the hints argument.

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

Or, you can use the SqlServerTableHints class.

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