Class SqlBulkCopyLoader<TRecord>

Namespace: Wolfgang.Etl.SqlBulkCopy

Assembly: Wolfgang.Etl.SqlBulkCopy.dll

Loads items of type TRecord into a SQL Server table using SqlBulkCopy for high-throughput bulk insert operations.

public sealed class SqlBulkCopyLoader<TRecord> : LoaderBase<TRecord, SqlBulkCopyReport>, ILoadWithProgressAndCancellationAsync<TRecord, SqlBulkCopyReport>, ILoadWithProgressAsync<TRecord, SqlBulkCopyReport>, ILoadWithCancellationAsync<TRecord>, ILoadAsync<TRecord>, IReportsItemErrors, IAsyncDisposable, IDisposable where TRecord : notnull

Type Parameters

TRecord: The type of items to load. Must be notnull.

Inheritance: object

LoaderBase<TRecord, SqlBulkCopyReport>

SqlBulkCopyLoader<TRecord>

Implements: ILoadWithProgressAndCancellationAsync<TRecord, SqlBulkCopyReport>

ILoadWithProgressAsync<TRecord, SqlBulkCopyReport>

ILoadWithCancellationAsync<TRecord>

ILoadAsync<TRecord>

IReportsItemErrors

IAsyncDisposable

IDisposable

Inherited Members: LoaderBase<TRecord, SqlBulkCopyReport>.LoadAsync(IAsyncEnumerable<TRecord>)

LoaderBase<TRecord, SqlBulkCopyReport>.LoadAsync(IAsyncEnumerable<TRecord>, CancellationToken)

LoaderBase<TRecord, SqlBulkCopyReport>.LoadAsync(IAsyncEnumerable<TRecord>, IProgress<SqlBulkCopyReport>)

LoaderBase<TRecord, SqlBulkCopyReport>.LoadAsync(IAsyncEnumerable<TRecord>, IProgress<SqlBulkCopyReport>, CancellationToken)

LoaderBase<TRecord, SqlBulkCopyReport>.DisposeAsync()

LoaderBase<TRecord, SqlBulkCopyReport>.Dispose()

LoaderBase<TRecord, SqlBulkCopyReport>.ReportingInterval

LoaderBase<TRecord, SqlBulkCopyReport>.CurrentItemCount

LoaderBase<TRecord, SqlBulkCopyReport>.CurrentSkippedItemCount

LoaderBase<TRecord, SqlBulkCopyReport>.CurrentErrorItemCount

LoaderBase<TRecord, SqlBulkCopyReport>.MaximumItemCount

LoaderBase<TRecord, SqlBulkCopyReport>.SkipItemCount

LoaderBase<TRecord, SqlBulkCopyReport>.WorkerResilience

LoaderBase<TRecord, SqlBulkCopyReport>.ErrorPolicy

object.Equals(object)

object.Equals(object, object)

object.GetHashCode()

object.GetType()

object.ReferenceEquals(object, object)

object.ToString()

Examples

var loader = new SqlBulkCopyLoader<Person>
(
    connection,
    new SqlBulkCopyLoaderOptions<Person>
    {
        BatchSize = 5000,
        BulkCopyTimeout = 60
    }
);
await loader.LoadAsync(items, cancellationToken);

Remarks

Maps .NET types to SQL Server tables using System.ComponentModel.DataAnnotations.Schema attributes: [Table], [Column], and [NotMapped].

Supports nested collection properties that map to separate child tables, optional pre/post-load actions, and opt-in data validation.

Constructors

SqlBulkCopyLoader(SqlConnection)

Initializes a new instance of the SqlBulkCopyLoader<TRecord> class.

public SqlBulkCopyLoader(SqlConnection connection)

Parameters

connection SqlConnection: The SQL Server connection.

Exceptions

ArgumentNullException: Thrown when connection is null.

SqlBulkCopyLoader(SqlConnection, SqlBulkCopyOptions, SqlTransaction?, ILogger<SqlBulkCopyLoader<TRecord>>?)

Initializes a new instance of the SqlBulkCopyLoader<TRecord> class with full configuration.

public SqlBulkCopyLoader(SqlConnection connection, SqlBulkCopyOptions options, SqlTransaction? transaction, ILogger<SqlBulkCopyLoader<TRecord>>? logger = null)

Parameters

connection SqlConnection: The SQL Server connection.
options SqlBulkCopyOptions: The bulk copy options.
transaction SqlTransaction: An optional external transaction.
logger ILogger<SqlBulkCopyLoader<TRecord>>: An optional logger instance for diagnostic output.

Examples

// One transaction spanning every file: either every file lands or none
// do (roll back on any failure).
using var transaction = connection.BeginTransaction();
try
{
    foreach (var file in files)
    {
        var loader = new SqlBulkCopyLoader<Person>(connection, SqlBulkCopyOptions.Default, transaction)
        {
            DestinationTableName = "People"
        };

        await loader.LoadAsync(file, cancellationToken);
    }

    transaction.Commit();
}
catch
{
    // Roll back, but don't let a rollback failure mask the original error.
    try
    {
        transaction.Rollback();
    }
    catch
    {
        // Ignore: rethrow the original load exception below.
    }

    throw;
}

// Commit after each file: a mid-run failure keeps the files already
// committed (good for large, independent files and restartability).
foreach (var file in files)
{
    using var transaction = connection.BeginTransaction();
    var loader = new SqlBulkCopyLoader<Person>(connection, SqlBulkCopyOptions.Default, transaction)
    {
        DestinationTableName = "People"
    };

    await loader.LoadAsync(file, cancellationToken);
    transaction.Commit();
}

Exceptions

ArgumentNullException: Thrown when connection is null.

SqlBulkCopyLoader(SqlConnection, ILogger<SqlBulkCopyLoader<TRecord>>?)

Initializes a new instance of the SqlBulkCopyLoader<TRecord> class with diagnostic logging.

public SqlBulkCopyLoader(SqlConnection connection, ILogger<SqlBulkCopyLoader<TRecord>>? logger = null)

Parameters

connection SqlConnection: The SQL Server connection.
logger ILogger<SqlBulkCopyLoader<TRecord>>: An optional logger instance for diagnostic output. When null — or omitted — Instance is used and logging is disabled.

Exceptions

ArgumentNullException: Thrown when connection is null.

SqlBulkCopyLoader(SqlConnection, SqlBulkCopyLoaderOptions<TRecord>?, SqlTransaction?, ILogger<SqlBulkCopyLoader<TRecord>>?)

Initializes a new instance of the SqlBulkCopyLoader<TRecord> class configured through an options record (ADR-0009). This is the initialization path every other constructor chains into.

public SqlBulkCopyLoader(SqlConnection connection, SqlBulkCopyLoaderOptions<TRecord>? options = null, SqlTransaction? transaction = null, ILogger<SqlBulkCopyLoader<TRecord>>? logger = null)

Parameters

connection SqlConnection: The open SqlConnection to bulk-copy into. The caller owns its lifetime.
options SqlBulkCopyLoaderOptions<TRecord>: The loader's configuration, including the settings inherited from Wolfgang.Etl.Abstractions.LoaderOptions. When null — or omitted — every documented default applies.
transaction SqlTransaction: An optional SqlTransaction the bulk copy and the pre/post actions run in.
logger ILogger<SqlBulkCopyLoader<TRecord>>: An optional logger. When null — or omitted — Instance is used.

Exceptions

ArgumentNullException: connection is null.
ArgumentOutOfRangeException: BatchSize is below 1 or BulkCopyTimeout is negative.

Properties

BatchSize

Gets or sets the number of rows in each batch sent to the server.

public int BatchSize { get; set; }

Property Value

int: The default is 10,000.

Exceptions

ArgumentOutOfRangeException: Thrown when the value is less than 1.

BulkCopyTimeout

Gets or sets the timeout in seconds for each bulk copy operation. A value of 0 means no timeout.

public int BulkCopyTimeout { get; set; }

Property Value

int: The default is 30 seconds.

Exceptions

ArgumentOutOfRangeException: Thrown when the value is negative.

DestinationSchemaName

Gets or sets an optional destination schema name override. When null, the schema is derived from the [Table] attribute.

public string? DestinationSchemaName { get; set; }

Property Value

string

DestinationTableName

Gets or sets an optional destination table name override. When null, the table name is derived from the [Table] attribute or the type name.

public string? DestinationTableName { get; set; }

Property Value

string

EnableDataValidation

Gets or sets a value indicating whether to validate each item using System.ComponentModel.DataAnnotations attributes before loading.

public bool EnableDataValidation { get; set; }

Property Value

bool: The default is false.

Remarks

Enabling validation adds per-item overhead. Validation is applied recursively to root TRecord instances and to every level of nested-collection children. How a failure is handled is controlled by ValidationFailureBehavior — the default is to throw a SqlBulkCopyValidationException, which fails loudly and stops the load. Set ValidationFailureBehavior to Skip to tolerate dirty data and drop only the failing items.

IsDryRun

Gets or sets a value indicating whether the load runs as a dry run — validating the pipeline against real data without writing to SQL Server.

public bool IsDryRun { get; set; }

Property Value

bool: The default is false.

Remarks

When true, the loader still enumerates the source, applies SkipItemCount / MaximumItemCount, runs data validation, increments the progress counters, and logs as usual — but performs no SQL side effects: the PreAction / PostAction (e.g. truncate / delete) and the bulk insert are all skipped. This lets a caller confirm a pipeline runs end-to-end and surfaces mapping / validation errors without touching the destination.

OnNestedValidationFailed

Gets or sets an optional callback invoked when a nested-collection child instance fails validation.

public Action<object, ICollection<ValidationResult>>? OnNestedValidationFailed { get; set; }

Property Value

Action<object, ICollection<ValidationResult>>

Remarks

Only invoked when EnableDataValidation is true. The callback runs before the configured ValidationFailureBehavior takes effect. The child is passed as object because the child type is resolved at load time, not at TRecord definition. For root-item validation, see OnValidationFailed.

OnValidationFailed

Gets or sets an optional callback invoked when a root TRecord fails validation.

public Action<TRecord, ICollection<ValidationResult>>? OnValidationFailed { get; set; }

Property Value

Action<TRecord, ICollection<ValidationResult>>

Remarks

Only invoked when EnableDataValidation is true. The callback runs before the configured ValidationFailureBehavior takes effect, so consumers can log / inspect the failing item from a single hook regardless of whether the loader then throws or skips. The callback receives the failing root item and the collection of validation errors. For nested-collection children, see OnNestedValidationFailed.

PostAction

Gets or sets the action to execute after loading completes.

public PostAction PostAction { get; set; }

Property Value

PostAction: The default is None.

PostLoadCustomAction

Gets or sets the custom delegate to invoke when PostAction is CustomAction.

public Func<PostLoadActionParameters, Task>? PostLoadCustomAction { get; set; }

Property Value

Func<PostLoadActionParameters, Task>

PreAction

Gets or sets the action to execute before loading begins.

public PreAction PreAction { get; set; }

Property Value

PreAction: The default is None.

PreLoadCustomAction

Gets or sets the custom delegate to invoke when PreAction is CustomAction.

public Func<PreLoadActionParameters, Task>? PreLoadCustomAction { get; set; }

Property Value

Func<PreLoadActionParameters, Task>

ValidationFailureBehavior

Gets or sets how the loader reacts to a validation failure when EnableDataValidation is true.

public ValidationFailureBehavior ValidationFailureBehavior { get; set; }

Property Value

ValidationFailureBehavior: The default is Throw.

Remarks

See ValidationFailureBehavior for the semantics of each option. The same setting applies to both root TRecord instances and nested-collection children.

Methods

CreateProgressReport()

Creates a progress report of type TProgress. This gives the derived class the opportunity to implement a custom progress report that is specific to the loading process.

protected override SqlBulkCopyReport CreateProgressReport()

Returns

SqlBulkCopyReport: Progress of type TProgress

CreateProgressTimer(IProgress<SqlBulkCopyReport>)

Creates the Wolfgang.Etl.Abstractions.IProgressTimer used to drive progress callbacks. Override this method in a derived class to inject a custom timer (for example, a custom implementation that allows manual control in unit tests).

protected override IProgressTimer CreateProgressTimer(IProgress<SqlBulkCopyReport> progress)

Parameters

progress IProgress<SqlBulkCopyReport>: The progress sink that will receive callbacks.

Returns

IProgressTimer: A started Wolfgang.Etl.Abstractions.IProgressTimer instance.

LoadWorkerAsync(IAsyncEnumerable<TRecord>, CancellationToken)

This method is the core implementation of the loading logic and should be overridden by derived classes.

protected override Task LoadWorkerAsync(IAsyncEnumerable<TRecord> items, CancellationToken token)

Parameters

items IAsyncEnumerable<TRecord>: The items to be loaded to the destination.
token CancellationToken: A CancellationToken to observe while waiting for the task to complete.

Returns

Task: A task representing the asynchronous operation.

Remarks

Items may be an empty sequence if no data is available or if the loading fails.

Exceptions

ArgumentNullException: Argument items is null