Getting Started

This guide will help you quickly get up and running with Wolfgang.Etl.SqlBulkCopy.

Prerequisites

  • .NET — the package targets net462, net481, netstandard2.0, net5.0, net6.0, net7.0, net8.0 and net10.0. The netstandard2.0 build covers the runtimes without a direct target (.NET Core 2.0–3.1), so .NET Framework 4.6.2+ and any modern .NET can consume it.
  • A reachable Microsoft SQL Server and a destination table whose columns match the record type you intend to load.

Installation

Via NuGet Package Manager

dotnet add package Wolfgang.Etl.SqlBulkCopy

Via Package Manager Console

Install-Package Wolfgang.Etl.SqlBulkCopy

Quick Start

Describe the destination with attributes, then hand the loader an IAsyncEnumerable<TRecord>:

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Threading;
using Microsoft.Data.SqlClient;
using Wolfgang.Etl.SqlBulkCopy;

[Table("Customers", Schema = "dbo")]
public sealed record Customer
{
    public int Id { get; init; }

    public string Name { get; init; } = string.Empty;

    public decimal Balance { get; init; }
}

async IAsyncEnumerable<Customer> ReadSourceAsync()
{
    // your source: file, API, another database, etc.
    yield return new Customer { Id = 1, Name = "Acme", Balance = 100m };
    yield return new Customer { Id = 2, Name = "Contoso", Balance = 250m };
}

using var connection = new SqlConnection("Server=.;Database=Sandbox;Integrated Security=True;Encrypt=True;");
await connection.OpenAsync();

var loader = new SqlBulkCopyLoader<Customer>
(
    connection,
    new SqlBulkCopyLoaderOptions<Customer>
    {
        BatchSize = 10_000,
        PreAction = PreAction.TruncateTable
    }
);

await loader.LoadAsync(ReadSourceAsync(), CancellationToken.None);

Common Tasks

Report progress

var progress = new Progress<SqlBulkCopyReport>(r =>
    Console.WriteLine($"{r.CurrentItemCount} rows in {r.BatchCount} batch(es), {r.CurrentSkippedItemCount} skipped"));

await loader.LoadAsync(ReadSourceAsync(), progress, CancellationToken.None);

Validate rows before loading

var loader = new SqlBulkCopyLoader<Customer>
(
    connection,
    new SqlBulkCopyLoaderOptions<Customer>
    {
        EnableDataValidation = true,
        ValidationFailureBehavior = ValidationFailureBehavior.Skip,
        OnValidationFailed = (item, errors) => Console.WriteLine($"skipped: {errors.Count} error(s)")
    }
);

Rehearse without writing

var loader = new SqlBulkCopyLoader<Customer>
(
    connection,
    new SqlBulkCopyLoaderOptions<Customer>
    {
        IsDryRun = true
    }
);

// Enumerates, maps, validates, counts and logs — but issues no SQL.
await loader.LoadAsync(ReadSourceAsync(), CancellationToken.None);

Next Steps

Common Issues

  • InvalidOperationException about a missing connection — PreAction / PostAction values that issue SQL need a loader constructed with a SqlConnection.
  • Column not loaded — check the property has a getter and is not marked [NotMapped]; the column name comes from [Column] when present, otherwise the property name.
  • Native AOT falls back to reflection — mark the record [BulkCopyable] so the bundled source generator emits accessors at compile time.

Additional Resources

Edit this page