Operations (MySqlConnector)

For MySQL via the MySqlConnector driver, the underlying implementation is leveraging the MySqlBulkCopy class of the MySqlConnector namespace — the same kind of bulk-load primitive SqlBulkCopy is for SQL Server.

For BulkInsert, the entities/rows are written straight to the target table — unless MySqlConnectorBulkImportIdentityBehavior.ReturnIdentity is requested, in which case a staging (pseudo) table is used instead, so the newly generated AUTO_INCREMENT values can be pre-assigned and read back before the rows are copied into the target table.

For BulkDelete, BulkDeleteByKey, BulkMerge and BulkUpdate, a pseudo (staging) table is created under a transaction context. The library writes to it via BulkInsert internally, then cascades the changes to the original table using the correct SQL statement, then drops the pseudo table.

The data is brought together from the client application into the database server (at one-go). It then gets processed together at the same time.

The other bulk operations can be optimized further by targeting the underlying table indexes (via qualifiers). Pass a list of Field objects when calling the operations.

Pseudo Table Type

The MySqlConnectorBulkImportPseudoTableType enum lets you choose between a TEMPORARY table (session-isolated) and an ordinary heap table for the staging table.

Every value currently resolves to Physical at runtime, regardless of the value passed or the row count. Auto and Memory are kept in the enum so existing code doesn’t need to change once a session-isolated staging table is fully wired up. Avoid running concurrent bulk operations against the same target table until this is resolved.

Supported Objects

Below are the following objects supported by the bulk operations.

• System.DataTable
• System.Data.Common.DbDataReader
• IEnumerable<T>
• ExpandoObject
• IDictionary<string, object>

Operation SQL Statements

Once all the data is in the pseudo (staging) table, the correct SQL statement is used to cascade the changes towards the original table.

BulkInsert writes directly into the target table and skips the staging table entirely — unless identityBehavior is set to ReturnIdentity, in which case a staging table is used to pre-assign identity values first (see Identity Setting Alignment below).

For BulkDelete / BulkDeleteByKey

> DELETE T FROM `OriginalTable` T
> INNER JOIN `PseudoTempTable` S ON (T.QualifierField1 = S.QualifierField1 AND T.QualifierField2 = S.QualifierField2);

For BulkMerge

> UPDATE `OriginalTable` T INNER JOIN `PseudoTempTable` S ON (T.QualifierField1 = S.QualifierField1 AND T.QualifierField2 = S.QualifierField2)
> SET T.Field3 = S.Field3, T.Field4 = S.Field4;
>
> INSERT INTO `OriginalTable` (Field1, Field2, ...)
> SELECT S.Field1, S.Field2, ... FROM `PseudoTempTable` S
> LEFT JOIN `OriginalTable` T ON (T.QualifierField1 = S.QualifierField1 AND T.QualifierField2 = S.QualifierField2)
> WHERE T.QualifierField1 IS NULL;

MySQL has no MERGE statement. BulkMerge is instead split into an UPDATE ... INNER JOIN for rows that match on the qualifiers, followed by an INSERT ... SELECT guarded by a LEFT JOIN ... WHERE ... IS NULL anti-join for rows that don’t. The identity column, if any, is always left out of the INSERT column list — a brand-new row’s identity property is typically an unset default (e.g. 0), not a real value meant to be inserted as-is.

For BulkUpdate

> UPDATE `OriginalTable` T INNER JOIN `PseudoTempTable` S ON (T.QualifierField1 = S.QualifierField1 AND T.QualifierField2 = S.QualifierField2)
> SET T.Field3 = S.Field3, T.Field4 = S.Field4;

Unlike BulkMerge, there is no anti-join INSERT branch — staged rows with no matching target row are left as-is, not inserted. If every staged column is also a qualifier (i.e. there is nothing left to update), the operation short-circuits and returns 0 without touching the database.

Special Arguments

The arguments below are available on most operations.

Identity Setting Alignment

When identityBehavior is set to ReturnIdentity, the library adds a surrogate __RepoDbBulkRowOrder__ column to the pseudo table to track each entity’s/row’s position in the source IEnumerable — MySQL has no equivalent to Oracle’s implicit ROWID, so this stands in for it. Identity values are pre-assigned into the pseudo table via a session user variable seeded from the target table’s current MAX(identity) + 1, then copied over as literal values, since MySQL’s AUTO_INCREMENT has no per-row NEXTVAL-style mechanism to pull values from ahead of the actual INSERT. The resultset is then read back ordered by __RepoDbBulkRowOrder__ so each value is assigned to the correct entity, regardless of ordering during the underlying INSERT/MERGE.

Returning identity values relies on session user variables (and, for BulkInsert/BulkMerge’s nullable-column step, a dynamically prepared ALTER TABLE statement). Add AllowUserVariables=True to your MySqlConnector connection string when using identityBehavior: ReturnIdentity — it is false by default — otherwise the operation will fail.

BatchSize

All the provided operations have a batchSize argument that lets you override the number of rows wired-up to the server per batch. By default it is null, meaning all items are sent together in one-go.

Use this argument if you wish to optimize the operation based on certain situations.

• Network Latency
• Infrastructure
• No. of Columns
• Type of Data

Async Methods

All the provided synchronous operations have an equivalent asynchronous (Async) counterpart.

BulkDelete

using (var connection = new MySqlConnection(connectionString))
{
    var people = connection.Query<Person>(e => e.IsActive == false);
    var deletedRows = connection.BulkDelete<Person>(people);
}

Or with qualifiers.

using (var connection = new MySqlConnection(connectionString))
{
    var deletedRows = connection.BulkDelete<Person>(people,
        qualifiers: e => new { e.LastName, e.DateOfBirth });
}

BulkDeleteByKey

using (var connection = new MySqlConnection(connectionString))
{
    var primaryKeys = new object[] { 10045, 10046, 10047 };
    var deletedRows = connection.BulkDeleteByKey("Person", primaryKeys);
}

BulkInsert

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

To return the newly generated identity values:

using (var connection = new MySqlConnection(connectionString))
{
    var people = GetPeople(10000);
    var insertedRows = connection.BulkInsert(people,
        identityBehavior: MySqlConnectorBulkImportIdentityBehavior.ReturnIdentity);
}

BulkMerge

using (var connection = new MySqlConnection(connectionString))
{
    var people = GetPeople(10000);
    var mergedRows = connection.BulkMerge(people);
}

Or with qualifiers.

using (var connection = new MySqlConnection(connectionString))
{
    var mergedRows = connection.BulkMerge(people,
        qualifiers: e => new { e.LastName, e.DateOfBirth });
}

BulkUpdate

using (var connection = new MySqlConnection(connectionString))
{
    var people = GetPeople(10000);
    var updatedRows = connection.BulkUpdate(people);
}

Or with qualifiers.

using (var connection = new MySqlConnection(connectionString))
{
    var updatedRows = connection.BulkUpdate(people,
        qualifiers: e => new { e.LastName, e.DateOfBirth });
}

Table of contents

• BulkDelete
• BulkDeleteByKey
• BulkInsert
• BulkMerge
• BulkUpdate