Batch Operations

A batch operation executes multiple single-operations (or SQL statements) against the database in one call. The execution is ACID; an implicit transaction is provided if none exists.

This operation is flexible, allowing you to control the number of rows batched per execution. This lets you tune performance based on your environment (e.g., network latency, infrastructure, number of columns, type of data).

In RepoDB, the InsertAll, UpdateAll, and MergeAll operations are the batch operations. They are all ACID in nature.

Identity Setting Alignment

The library enforces additional logic to ensure correct identity setting alignment when using InsertAll and MergeAll.

During the batch operation, a dedicated DbParameter object is added with a column named __RepoDb_OrderColumn in the sub-table. Its value is the entity model index, ensuring that the index correctly maps to the corresponding entity in the IEnumerable object. The sub-table result set is ordered by this column in ascending order prior to the data cascade.

When the newly generated identity value is set back to the data model, the __RepoDb_OrderColumn value is used to look up the correct index in the IEnumerable object, and the compiled identity-setter function assigns the identity value back to the identity property.

Normal executions

Given a Customer model mapped to the [dbo].[Customer] table, calling the Insert operation:

var customer = new Customer
{
    Name = "John Doe",
    Address = "New York"
};
using (var connection = new SqlConnection(connectionString))
{
    connection.Insert<Customer>(customer);
}

…executes the following SQL statement:

> INSERT INTO [Customer] (Name, Address) VALUES (@Name, @Address);

Inserting multiple rows via iteration:

var customers = GetCustomers(1000);
using (var connection = new SqlConnection(connectionString))
{
    customers.ForEach(
        c => connection.Insert<Customer>(c));
}

The same SQL statement is executed once per row — even when wrapped in a transaction.

Batch executions

With batch execution, the SQL statements are packed and executed in a single call.

var customers = GetCustomers(1000);
using (var connection = new SqlConnection(connectionString))
{
    connection.InsertAll<Customer>(customers,
        batchSize: 100);
}

The library generates packed statements batched by the specified size. For a batch size of 100:

> INSERT INTO [Customer] (Name, Address) VALUES (@Name, @Address);
> INSERT INTO [Customer] (Name, Address) VALUES (@Name1, @Address1);
> ...
> INSERT INTO [Customer] (Name, Address) VALUES (@Name99, @Address98);
> INSERT INTO [Customer] (Name, Address) VALUES (@Name99, @Address99);

The default value of the batchSize is 10. The value can be seen at Constant.DefaultBatchOperationSize.

The packed statements are cached and executed in batches — 10 times with 100 rows each in the example above. All parameters are mapped to their correct indexes based on the batch number. This approach is significantly more efficient than individual inserts, reducing 1000 separate executions to just 10.

In SQL Server, ADO.NET supports a maximum of 2100 parameters. The batch operation will fail if this limit is exceeded. Adjust the batch size using the batchSize argument accordingly.

Behind the scene of the Batch Operations

When calling any batch push operation (InsertAll, UpdateAll, or MergeAll), the following steps occur internally: