Bulk Copy Operations

Overview

Jaunty's bulk copy functionality provides high-performance data loading for large datasets by automatically leveraging database-specific native bulk copy APIs (SqlBulkCopy, NpgsqlBinaryImporter, etc.).

Key Benefits:

• 10-100x faster than standard INSERT for large datasets (10K+ rows)
• Automatic activation when beneficial (configurable threshold: 100 rows default)
• One package to install, then no code changes: the same BulkInsert call routes itself
• Consistent API across all supported databases

The native path needs Jaunty.Extensions.Reflection. All four built-in dialects report SupportsNativeBulkCopy => false. That package supplies the dialect wrappers that report true and carry the providers, and SqlDialectFactory picks them up by probing for the assembly at resolution time. Without it every call below still works and still returns the right row count; it takes the multi-row or per-row route instead of the native one.

Which route a BulkInsert takes

Four independent conditions gate the native route, and failing any one of them is a silent downgrade to a slower route that produces the same rows. That is the intended behavior, and it is also why a bulk insert that seems slow is usually a missing package rather than a missing index.

SQLite is excluded from the multi-row route on purpose. It runs in-process, so there are no round-trips to save, and the parameter objects a multi-row statement needs cost more than the savings.

What each engine does natively

MySQL's native route is chunked INSERT rather than LOAD DATA LOCAL INFILE. LOAD DATA fails on any MySQL 8+ or MariaDB server with the default local_infile=0 and additionally needs AllowLoadLocalInfile=true in the connection string, while chunked INSERT needs no server or connection-string configuration, works with both MySqlConnector and MySql.Data, and performs in the same class over the 100 to 10,000 row range this path targets.

Identity columns never reach any provider. EntityDataReader streams EntityMetadata.InsertColumns, which excludes them, so the exclusion is one upstream decision rather than four per-provider ones. BulkCopyOptions.IdentityMode is inert on every provider for this reason.

Quick Start

using Jaunty;
using Jaunty.Configuration;

// Optional: Configure global settings at application startup
BulkCopyConfiguration.DefaultBatchSize = 10000;
BulkCopyConfiguration.MinimumRowsForNativeBulkCopy = 100;

// Bulk insert - native bulk copy used automatically for 100+ rows
var products = GetProducts(); // List<Product>
int inserted = connection.BulkInsert(products);

Console.WriteLine($"Inserted {inserted} products");

Configuration

Global Configuration

Configure at application startup:

// Set default batch size
BulkCopyConfiguration.DefaultBatchSize = 10000;

// Set timeout (seconds)
BulkCopyConfiguration.DefaultTimeout = 30;

// Set minimum rows for native bulk copy (default: 100)
BulkCopyConfiguration.MinimumRowsForNativeBulkCopy = 50;

// Disable native bulk copy entirely
BulkCopyConfiguration.EnableNativeBulkCopy = false;

// Configure identity handling
BulkCopyConfiguration.DefaultIdentityMode = BulkCopyIdentityMode.KeepIdentity;

// Reset to defaults
BulkCopyConfiguration.Reset();

Configuration Options

Identity Mode Options

Table Lock Options

API Reference

BulkInsert()

Inserts multiple entities using native bulk copy when beneficial.

// Basic usage
int BulkInsert<T>(this IDbConnection connection, IEnumerable<T> entities) where T : new();

// With options
int BulkInsert<T>(this IDbConnection connection, IEnumerable<T> entities, CommandOptions options) where T : new();

// Ignore constraints (if supported)
int BulkInsertIgnoreConstraints<T>(this IDbConnection connection, IEnumerable<T> entities) where T : new();

int BulkInsertIgnoreConstraints<T>(this IDbConnection connection, IEnumerable<T> entities, CommandOptions options) where T : new();

Returns: Number of rows inserted

Example:

var products = new List<Product>
{
    new Product { Name = "Product 1", Price = 10.00m },
    new Product { Name = "Product 2", Price = 20.00m },
    // ... 100+ more items for native bulk copy
};

int inserted = connection.BulkInsert(products);

BulkInsertAsync()

Async version of BulkInsert.

ValueTask<int> BulkInsertAsync<T>(this IDbConnection connection, IEnumerable<T> entities, CancellationToken cancellationToken = default);

ValueTask<int> BulkInsertAsync<T>(this IDbConnection connection, IEnumerable<T> entities, CommandOptions options, CancellationToken cancellationToken = default);

Example:

int inserted = await connection.BulkInsertAsync(products, cancellationToken);

BulkUpdate()

Updates multiple entities in a single transaction.

int BulkUpdate<T>(this IDbConnection connection, IEnumerable<T> entities) where T : new();

int BulkUpdate<T>(this IDbConnection connection, IEnumerable<T> entities, CommandOptions options) where T : new();

Note: Bulk update uses standard UPDATE statements. Native bulk update is not generally supported across databases.

Example:

var products = GetUpdatedProducts();
int updated = connection.BulkUpdate(products);

BulkUpdateIgnoreConstraints()

Updates multiple entities, bypassing foreign key constraints (if supported).

int BulkUpdateIgnoreConstraints<T>(this IDbConnection connection, IEnumerable<T> entities) where T : new();

int BulkUpdateIgnoreConstraints<T>(this IDbConnection connection, IEnumerable<T> entities, CommandOptions options) where T : new();

Supported Databases:

• PostgreSQL (SET session_replication_role = 'replica')
• MySQL (SET FOREIGN_KEY_CHECKS = 0)
• SQLite (PRAGMA foreign_keys = OFF)
• SQL Server (not supported)

BulkDelete()

Deletes multiple entities by primary key.

int BulkDelete<T>(this IDbConnection connection, IEnumerable<T> entities) where T : new();

int BulkDelete<T>(this IDbConnection connection, IEnumerable<T> entities, CommandOptions options) where T : new();

Example:

var productsToDelete = GetProductsToDelete();
int deleted = connection.BulkDelete(productsToDelete);

Transaction Support

All bulk operations support transactions:

using var transaction = connection.BeginTransaction();

try
{
    connection.BulkInsert(products, CommandOptions.WithTransaction(transaction));
    connection.BulkInsert(orders, CommandOptions.WithTransaction(transaction));
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

Database-Specific Behavior

SQL Server

Technology: SqlBulkCopy

Performance: 10-100x faster than INSERT for 10K+ rows

Features:

• Configurable batch size
• Identity column handling
• Table locking (TABLOCK)
• Transaction support

Requirements: Microsoft.Data.SqlClient or System.Data.SqlClient

PostgreSQL

Technology: NpgsqlBinaryImporter (COPY BINARY)

Performance: 15-25x faster than INSERT for 10K+ rows

Features:

• Binary COPY protocol
• Very high throughput
• Automatic type conversion

Requirements: Npgsql

MySQL / MariaDB

Technology: Chunked multi-row parameterized INSERT (2,000-parameter budget per statement; no server-side local_infile requirement)

Performance: 12.9-16.1x faster than a transactional loop (measured 2026-07-04)

Features:

• No LOAD DATA / local_infile server configuration needed
• Reused prepared command for full chunks
• Own or caller-supplied transaction

Requirements: MySql.Data or MySqlConnector

SQLite

Technology: Prepared-loop INSERT in a single transaction (BulkInsert routes SQLite here automatically; multi-row VALUES is quadratic in Microsoft.Data.Sqlite)

Performance: parity with hand-coded ADO.NET (measured 2026-07-04)

Features:

• Single transaction for all rows
• Prepared statement reuse

Note: SQLite has no native bulk copy API; there is no separate provider.

Performance Guidelines

When to Use Bulk Copy

Optimization Tips

1. Batch large datasets: For 1M+ rows, batch into chunks of 10K-50K
2. Use transactions: Always wrap bulk operations in transactions
3. Disable indexes: For very large loads, consider dropping/recreating indexes
4. Set appropriate batch size: Match to your database's optimal batch size
5. Consider identity mode: Use AutoGenerate unless you need to preserve IDs

Performance Comparison

SQL Server example times

Error Handling

Common Errors

Parameter count mismatch:

InvalidOperationException: No parameter binder found for type 'Product'.

Solution: Ensure source generation or reflection extension is used.

Provider not available:

InvalidOperationException: SqlBulkCopy is not available.

Solution: Ensure database provider package is installed.

Constraint violation:

DbException: FOREIGN KEY constraint failed.

Solution: Use BulkInsertIgnoreConstraints<T>() if appropriate.

Transaction Rollback

Bulk operations automatically rollback on error when using transactions:

using var transaction = connection.BeginTransaction();
try
{
    connection.BulkInsert(products, CommandOptions.WithTransaction(transaction));
    transaction.Commit();
}
catch (Exception ex)
{
    transaction.Rollback();
    // ex contains details of what failed
    throw;
}

Entity Requirements

Required Attributes

For bulk operations, entities should have:

[Table("products")]
public class Product
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    
    [Column("name")]
    public string Name { get; set; }
    
    [Column("price")]
    public decimal Price { get; set; }
}

Ignored Properties

Properties marked with [Ignore] are skipped:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    
    [Ignore]
    public string CalculatedValue { get; set; } // Not inserted
}

Examples

Basic Bulk Insert

var products = new List<Product>
{
    new Product { Name = "Widget", Price = 9.99m },
    new Product { Name = "Gadget", Price = 19.99m },
    // ... 100+ more
};

int inserted = connection.BulkInsert(products);
Console.WriteLine($"Inserted {inserted} products");

Bulk Insert with Transaction

using var transaction = connection.BeginTransaction();
try
{
    var products = GetProducts();
    var categories = GetCategories();
    
    connection.BulkInsert(categories, CommandOptions.WithTransaction(transaction));
    connection.BulkInsert(products, CommandOptions.WithTransaction(transaction));
    
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

Bulk Insert with Custom Options

var options = new CommandOptions<Product>(transaction: transaction, commandTimeout: 60);

int inserted = connection.BulkInsert(products, options);

Async Bulk Insert

var products = await GetProductsAsync();
int inserted = await connection.BulkInsertAsync(products, cancellationToken);

Bulk Update

var products = GetUpdatedProducts();
int updated = connection.BulkUpdate(products);
Console.WriteLine($"Updated {updated} products");

Bulk Delete

var productsToDelete = GetProductsToDelete();
int deleted = connection.BulkDelete(productsToDelete);
Console.WriteLine($"Deleted {deleted} products");

Troubleshooting

Native Bulk Copy Not Being Used

Symptom: Performance is same as standard INSERT

Check:

1. Verify row count >= MinimumRowsForNativeBulkCopy
2. Verify EnableNativeBulkCopy = true
3. Check database provider is installed
4. Verify dialect supports native bulk copy

Solution:

// Lower threshold
BulkCopyConfiguration.MinimumRowsForNativeBulkCopy = 50;

// Verify enabled
Console.WriteLine(BulkCopyConfiguration.EnableNativeBulkCopy); // Should be true

Identity Values Not Preserved

Symptom: Identity columns get new values instead of entity values

Solution:

BulkCopyConfiguration.DefaultIdentityMode = BulkCopyIdentityMode.KeepIdentity;

Timeout Errors

Symptom: Bulk operation times out

Solution:

BulkCopyConfiguration.DefaultTimeout = 120; // 2 minutes

// Or per-operation
var options = CommandOptions<T>.WithTimeout(120);

See Also

• Bulk Copy Architecture
• Configuration
• Write Methods