Operations (MySQL)

For MySQL via the MySql.Data driver (RepoDb.MySql.BulkOperations), every row-load goes through this package’s own internal MySqlBulkCopy class — a LOAD DATA LOCAL INFILE-based stand-in built on top of MySql.Data’s MySqlBulkLoader, since MySql.Data ships no genuine streaming bulk-copy API of its own (unlike SqlBulkCopy, it has no reader-streaming WriteToServer(IDataReader) overload). This package’s MySqlBulkCopy first serializes whatever rows it’s given (entities, a DataTable, or a reader) to a temporary tab-delimited file, hands that file to MySqlBulkLoader, then deletes it once the load completes.

This is a separate implementation from Operations (MySqlConnector), which uses the third-party MySqlConnector package’s own MySqlBulkCopy type. The two packages are unrelated and cannot be mixed.

The connection string needs AllowLoadLocalInfile=True;AllowUserVariables=True; — the former lets the client send LOAD DATA LOCAL INFILE, which this package’s internal MySqlBulkCopy uses for every row-load, and the latter lets the staging-table SQL use session user variables and PREPARE/EXECUTE for its identity pre-assignment and nullability-toggling steps. The server also needs its local_infile global variable turned on (SET GLOBAL local_infile = 1;, requires SUPER/SYSTEM_VARIABLES_ADMIN) — it’s off by default. You must also call RepoDb.MySqlBootstrap.Initialize() once at application startup. See Installation for more options.

For BulkInsert, the entities/rows are written straight to the target table — unless identityBehavior: MySqlBulkImportIdentityBehavior.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 for each call. The library writes to it via its internal MySqlBulkCopy, 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 MySqlBulkImportPseudoTableType 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, including an explicit Memory and Auto’s row-count threshold. The TEMPORARY TABLE branch is fully implemented in the SQL builder, but the resolution step that picks between them (ResolvePseudoTableType) currently maps every input to Physical unconditionally, until that path is enabled and verified against a live server. Because a physical pseudo-table has no per-session isolation, 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>

The Staging Table Lifecycle

BulkMerge, BulkUpdate, BulkDelete, BulkDeleteByKey, and BulkInsert with ReturnIdentity stage rows into a per-call pseudo table before running a set-based statement against it. Every call — not just the first one for a given table — issues a fresh DROP TABLE IF EXISTS followed by CREATE TABLE ... AS SELECT ... WHERE (1 = 0) (or CREATE TEMPORARY TABLE ... for Memory) to (re)create the pseudo table, shaped after the real table’s columns, plus one extra surrogate column — __RepoDbBulkRowOrder__ BIGINT AUTO_INCREMENT PRIMARY KEY — that gives the staged rows a deterministic order to read back in. The pseudo table is dropped again once the operation finishes.

Because CREATE TABLE/DROP TABLE are DDL, and DDL causes an implicit COMMIT in MySQL, every BulkMerge/BulkUpdate/BulkDelete/BulkDeleteByKey/BulkInsert-with-ReturnIdentity call implicitly commits any other uncommitted work already pending on that connection — both when the pseudo table is (re)created at the start of the call and again when it’s dropped at the end. Keep this in mind if you’re bulk-writing inside a larger transaction alongside other statements.

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 native MERGE statement, so BulkMerge always performs the same two-statement translation: an UPDATE ... INNER JOIN against the rows that match on qualifiers, followed by an INSERT ... SELECT guarded by a LEFT JOIN ... WHERE ... IS NULL anti-join for the 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.

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.

Returning identity values relies on session user variables (and, for BulkInsert/BulkMerge’s nullable-column step, a dynamically prepared ALTER TABLE statement) — this is why AllowUserVariables=True is required on the connection string.

The Transaction Boundary

This package’s internal MySqlBulkCopy is constructed from a bare MySqlConnection and never receives a MySqlTransaction, and it issues LOAD DATA LOCAL INFILE directly against that connection rather than through a command enlisted in your transaction.

Whether a rolled-back transaction leaves already-loaded rows behind has not been verified against a live server — treat it as unconfirmed until you’ve checked the behavior for your MySQL version and storage engine. See the release notes for the current verification status of this package.

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: MySqlBulkImportIdentityBehavior.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