Bulk Copy Architecture

Overview

Jaunty's bulk copy architecture provides high-performance data loading for large datasets by leveraging database-specific native bulk copy APIs. The architecture is designed to:

1. Automatically use native bulk copy when beneficial (100+ rows by default)
2. Maintain zero external dependencies through reflection-based provider access
3. Provide consistent API across all supported database dialects
4. Fall back gracefully to standard INSERT operations when native bulk copy is unavailable

Architecture Components

Core Interfaces

IBulkCopyProvider

internal interface IBulkCopyProvider
{
    bool IsSupported { get; }
    int CopyToServer(IDbConnection connection, string tableName, IDataReader data, BulkCopyOptions options);
    ValueTask<int> CopyToServerAsync(DbConnection connection, string tableName, IDataReader data, BulkCopyOptions options, CancellationToken cancellationToken);
}

Purpose: Abstracts database-specific bulk copy implementations.

Implementations:

• SqlServerBulkCopyProvider - Uses SqlBulkCopy
• PostgreSqlBulkCopyProvider - Uses NpgsqlBinaryImporter (COPY BINARY)
• MySqlBulkCopyProvider - Chunked multi-row parameterized INSERT
• SQLite - no provider: BulkInsert routes to the core prepared-loop path

Entity Adapter

EntityDataReader<T>

internal sealed class EntityDataReader<T> : IDataReader

Purpose: Adapts IEnumerable<T> to IDataReader for bulk copy APIs.

Key Features:

• Uses compiled expression trees for property access (no reflection during iteration)
• Getters cached per type in EntityDataReaderCache<T> (thread-safe lazy initialization)
• Streams entities without buffering entire dataset
• Handles null values correctly (converts to DBNull.Value)
• Implements full IDataReader interface for compatibility

Thread Safety: Getters are initialized once per type using double-check locking pattern:

private static class EntityDataReaderCache<TEntity> where TEntity : new()
{
    public static void Initialize(ColumnMetadata[] columns)
    {
        if (_getters is null)
        {
            lock (columns)
            {
                if (_getters is null)
                    _getters = BuildGetters(columns);
            }
        }
    }
}

Usage:

var entities = GetEntities();
var metadata = MetadataCache<T>.Metadata;
var reader = new EntityDataReader<T>(entities, metadata);
// Pass reader to bulk copy provider

Configuration

BulkCopyConfiguration (Global)

public static class BulkCopyConfiguration
{
    public static int DefaultBatchSize { get; set; } = 10000;
    public static int DefaultTimeout { get; set; } = 30;
    public static BulkCopyIdentityMode DefaultIdentityMode { get; set; }
    public static bool DefaultCheckConstraints { get; set; } = false;
    public static int MinimumRowsForNativeBulkCopy { get; set; } = 100;
    public static bool EnableNativeBulkCopy { get; set; } = true;
}

Purpose: Global configuration for bulk copy behavior.

Important: Configure at application startup before executing bulk operations.

BulkCopyOptions (Per-Operation)

internal sealed class BulkCopyOptions
{
    public int BatchSize { get; set; } = 10000;
    public int Timeout { get; set; } = 30;
    public BulkCopyIdentityMode IdentityMode { get; set; }
    public bool CheckConstraints { get; set; }
    public TableLockOption TableLock { get; set; }
    public bool EnableStreaming { get; set; } = true;
    public IDbTransaction? Transaction { get; set; }
}

Purpose: Per-operation configuration overrides.

Enums

BulkCopyIdentityMode

TableLockOption

Database-Specific Implementations

SQL Server

Provider: SqlServerBulkCopyProvider

Technology: SqlBulkCopy (via reflection)

Features:

• Native bulk copy API
• Supports transactions
• Configurable batch size
• Identity column handling
• Table locking options

Reflection Strategy:

// Dynamically load from Microsoft.Data.SqlClient or System.Data.SqlClient
private static readonly Type? SqlBulkCopyType = Type.GetType("Microsoft.Data.SqlClient.SqlBulkCopy, Microsoft.Data.SqlClient")
    ?? Type.GetType("System.Data.SqlClient.SqlBulkCopy, System.Data");

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

PostgreSQL

Provider: PostgreSqlBulkCopyProvider

Technology: NpgsqlBinaryImporter (COPY BINARY format)

Features:

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

COPY Command:

COPY table_name (col1, col2, ...) FROM STDIN BINARY

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

MySQL / MariaDB

Provider: MySqlBulkCopyProvider

Technology: Chunked multi-row parameterized INSERT (2,000-parameter budget per statement, chunk size adapts to column count)

Features:

• No LOAD DATA / local_infile server or connection-string requirement
• One reused command for full chunks, separate tail command
• Sync and async paths; own or caller-supplied transaction

Process:

1. Buffer rows up to the per-statement parameter budget
2. Bind and execute the reused full-chunk INSERT
3. Execute a tail INSERT for the remainder

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

SQLite

Provider: none (PROD-120: the former SQLiteBulkCopyProvider was removed)

Technology: BulkInsert detects SQLite dialects (including the Extensions.Reflection wrapper) and routes to the core prepared-loop path: one prepared command, one transaction. Multi-row VALUES is avoided because parameter binding is quadratic in Microsoft.Data.Sqlite.

Note: SQLite has no native bulk copy API.

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

Integration with BulkInsert

The bulk copy providers are automatically integrated with BulkInsert<T>():

private static int BulkInsertCore<T>(...)
{
    // ... validation ...
    
    ISqlDialect dialect = SqlDialectFactory.GetDialect(connection);
    
    // Use native bulk copy if:
    // 1. Provider supports it
    // 2. Enabled in configuration
    // 3. Row count >= threshold
    if (dialect.SupportsNativeBulkCopy && 
        BulkCopyConfiguration.EnableNativeBulkCopy &&
        entityList.Count >= BulkCopyConfiguration.MinimumRowsForNativeBulkCopy)
    {
        var bulkProvider = dialect.CreateBulkCopyProvider();
        var dataReader = new EntityDataReader<T>(entityList, cached.Metadata);
        return bulkProvider.CopyToServer(connection, tableName, dataReader, options);
    }
    
    // Fallback to existing multi-row/loop logic
    // ...
}

Performance Characteristics

When Native Bulk Copy Is Used

Expected Performance Gains

Compared to standard multi-row INSERT

Error Handling

Transaction Rollback

All bulk copy providers support transaction rollback:

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

Provider-Specific Errors

NativeAOT Compatibility

Current Status

Bulk copy providers use reflection to access database-specific APIs without hard dependencies. This approach:

Maintains zero external dependencies Requires reflection permissions (not fully NativeAOT-safe)

Future Enhancement

For full NativeAOT compatibility, consider:

1. Source-generated providers - Generate provider code at compile time
2. Extension packages - Move bulk copy to Jaunty.Providers.* packages
3. Direct references - Add optional direct package references with trimming support

Testing Strategy

Unit Tests

• BulkCopyConfigurationTests - Configuration options
• EntityDataReaderTests - Entity-to-data reader adaptation

Integration Tests

• BulkCopyProviderTests - Provider-specific tests per dialect
• Tests verify data integrity, null handling, transaction support

Performance Tests

See benchmarks/Jaunty.Benchmarks/Benchmarks/BulkCopyBenchmarks.cs

Extensibility

Adding New Providers

1. Implement IBulkCopyProvider
2. Add SupportsNativeBulkCopy property to dialect
3. Add CreateBulkCopyProvider() method to dialect
4. Add integration tests

Example: Oracle Provider

internal sealed class OracleBulkCopyProvider : IBulkCopyProvider
{
    public bool IsSupported => OracleBulkCopyType != null;
    
    public int CopyToServer(...)
    {
        // Use OracleBulkCopy via reflection
    }
}

Configuration Examples

Application Startup

// In Program.cs or Startup.cs
BulkCopyConfiguration.DefaultBatchSize = 5000;
BulkCopyConfiguration.DefaultTimeout = 60;
BulkCopyConfiguration.MinimumRowsForNativeBulkCopy = 50; // Use native for 50+ rows
BulkCopyConfiguration.EnableNativeBulkCopy = true;

Per-Operation Override

var options = new BulkCopyOptions
{
    BatchSize = 1000,
    Timeout = 120,
    IdentityMode = BulkCopyIdentityMode.KeepIdentity,
    TableLock = TableLockOption.BulkLock,
    Transaction = transaction
};

// Use options with bulk copy provider directly
var provider = dialect.CreateBulkCopyProvider();
provider.CopyToServer(connection, "MyTable", reader, options);

Limitations

1. SQL Server: Requires Microsoft.Data.SqlClient or System.Data.SqlClient
2. PostgreSQL: Requires Npgsql
3. MySQL: Requires MySql.Data or MySqlConnector
4. SQLite: No true bulk copy, only optimized INSERT
5. Reflection overhead: Small performance cost for provider access
6. NativeAOT: Not fully compatible due to reflection usage

Future Enhancements

1. Source-generated providers for NativeAOT
2. Streaming support for very large datasets
3. Progress reporting callbacks
4. Column mapping configuration
5. Error row handling (skip bad rows vs fail fast)
6. Parallel bulk copy for multi-file loads

References

• SQL Server SqlBulkCopy
• Npgsql Binary Import
• MySQL LOAD DATA
• SQLite Performance