Wolfgang.Etl.SqlBulkCopy 0.9.0

Wolfgang.Etl.SqlBulkCopy

A loader that uses SqlBulkCopy for fast inserts into a Microsoft SQL database

📦 Installation

dotnet add package Wolfgang.Etl.SqlBulkCopy

NuGet Package: Wolfgang.Etl.SqlBulkCopy

📄 License

This project is licensed under the MIT License. See the LICENSE file for details.

📚 Documentation

• GitHub Repository: https://github.com/Chris-Wolfgang/ETL-SqlBulkCopy
• API Documentation: https://Chris-Wolfgang.github.io/ETL-SqlBulkCopy/
• Formatting Guide: README-FORMATTING.md
• Contributing Guide: CONTRIBUTING.md
• Architecture Decisions: docs/adr/index.md
• Migration Guides: docs/migrations/

🚀 Quick Start

using Microsoft.Data.SqlClient;
using Wolfgang.Etl.SqlBulkCopy;

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);

Configuring the loader

Everything about a load is configured through SqlBulkCopyLoaderOptions<T>, a record with init-only members, passed as the second constructor argument ((connection, options, transaction, logger), all three optional). It carries the loader's own settings — BulkCopyOptions, BatchSize, BulkCopyTimeout, the destination overrides, validation, the pre/post actions and IsDryRun — and the ones every loader shares (ReportingInterval, MaximumItemCount, SkipItemCount, ErrorPolicy). A loader constructed without a record keeps every default. The settable properties on the loader are deprecated and will be removed in a later release.

✨ Features

Examples:

• Truncate before load: PreAction = PreAction.TruncateTable on the options record (shown above).
• Custom pre-action: PreAction = PreAction.CustomAction and PreLoadCustomAction = async p => { /* p.Connection, p.Transaction, p.Columns, p.CancellationToken */ } on the options record.
• Nested table: decorate a [NotMapped]-free IEnumerable<TChild> property; the child rows write to the child's [Table] in the same session.
• Transaction across multiple files: build each loader with new SqlBulkCopyLoader<T>(connection, options, transaction) and either Commit() once for all-or-nothing, or commit per file for restartability (worked examples on the constructor's XML docs).
• Dry run: set IsDryRun = true on the options record to run the full pipeline without writing — it still enumerates, maps, validates, counts, and logs, so mapping/validation errors surface without touching the destination.

See the API documentation for the full surface.

🎯 Supported Frameworks

This library targets:

• .NET Framework: 4.6.2, 4.8.1
• .NET Standard: 2.0
• .NET: 5.0, 6.0, 7.0, 8.0, 10.0

See the NuGet package page for the authoritative per-TFM compatibility matrix.

⚡ Native AOT & trimming

By default the per-row hot path compiles a property getter at runtime with System.Linq.Expressions — fast under the JIT, but it emits IL at run time, so under Native AOT it falls back to the slower expression interpreter.

Opt a record into compile-time source-generated accessors by marking it [BulkCopyable]:

using System.ComponentModel.DataAnnotations.Schema;
using Wolfgang.Etl.SqlBulkCopy;

[BulkCopyable]
[Table("People")]
public sealed class Person
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public Status Status { get; set; }   // enum columns are handled too
}

The bundled source generator emits strongly-typed getters (and enum→underlying converters) at compile time, and the loader uses them automatically — no runtime Expression.Compile, so the marked type's hot path is AOT-clean and keeps the compiled-getter throughput.

Note: a full Native-AOT publish of an app that opens a SQL connection also depends on Microsoft.Data.SqlClient's own AOT support, which is outside this library's control. [BulkCopyable] makes this library's mapping path AOT-clean. See ADR 0006 for the full rationale.

🔍 Code Quality & Static Analysis

This project enforces strict code quality standards through 8 specialized analyzers and custom async-first rules:

Analyzers in Use

1. Microsoft.CodeAnalysis.NetAnalyzers - Built-in .NET analyzers for correctness and performance
2. Roslynator.Analyzers - Advanced refactoring and code quality rules
3. AsyncFixer - Async/await best practices and anti-pattern detection
4. Microsoft.VisualStudio.Threading.Analyzers - Thread safety and async patterns
5. Microsoft.CodeAnalysis.BannedApiAnalyzers - Prevents usage of banned synchronous APIs
6. Meziantou.Analyzer - Comprehensive code quality rules
7. SonarAnalyzer.CSharp - Industry-standard code analysis
8. Microsoft.CodeAnalysis.PublicApiAnalyzers - Tracks the shipped public surface (RS0016/RS0017)

Async-First Enforcement

This library uses BannedSymbols.txt to prohibit synchronous APIs and enforce async-first patterns:

Blocked APIs Include:

• ❌ Task.Wait(), Task.Result - Use await instead
• ❌ Thread.Sleep() - Use await Task.Delay() instead
• ❌ Synchronous file I/O (File.ReadAllText) - Use async versions
• ❌ Synchronous stream operations - Use ReadAsync(), WriteAsync()
• ❌ Parallel.For/ForEach - Use Task.WhenAll() or Parallel.ForEachAsync()
• ❌ Obsolete APIs (WebClient, BinaryFormatter)

Why? To ensure all code is truly async and non-blocking for optimal performance in async contexts.

🛠️ Building from Source

Prerequisites

• .NET 10.0 SDK or later

Why the 10.0 SDK when the library targets frameworks as old as net462? The SDK is the build toolchain, not a runtime requirement. It must be at least as new as the highest framework in <TargetFrameworks> — net10.0 here — because an older SDK has no targeting pack for a newer framework and fails restore with NETSDK1045: The current .NET SDK does not support targeting .NET 10.0. The SDK is backward-compatible, so the single 10.0 SDK builds every target in the list, from net462 up.

This applies only to building this repository. Consuming the package requires nothing of the sort — any runtime matching one of the shipped targets works (.NET Framework 4.6.2+, or anything netstandard2.0-compatible, or .NET 8/10).

• Optional: PowerShell Core for formatting scripts

Build Steps

# Clone the repository
git clone https://github.com/Chris-Wolfgang/ETL-SqlBulkCopy.git
cd ETL-SqlBulkCopy

# Restore dependencies
dotnet restore

# Build the solution
dotnet build --configuration Release

# Run tests
dotnet test --configuration Release

# Run code formatting (PowerShell Core)
pwsh ./scripts/format.ps1

Code Formatting

This project uses .editorconfig and dotnet format:

# Format code
dotnet format

# Verify formatting (as CI does)
dotnet format --verify-no-changes

See README-FORMATTING.md for detailed formatting guidelines.

Building Documentation

This project uses DocFX to generate API documentation:

# Install DocFX (one-time setup)
dotnet tool install -g docfx

# Generate API metadata and build documentation
cd docfx_project
docfx metadata  # Extract API metadata from source code
docfx build     # Build HTML documentation

# Documentation is generated in the docs/ folder at the repository root

The documentation is automatically built and deployed to GitHub Pages when changes are pushed to the main branch.

Local Preview:

# Serve documentation locally (with live reload)
cd docfx_project
docfx build --serve

# Open http://localhost:8080 in your browser

Documentation Structure:

• docfx_project/ - DocFX configuration and source files
• docs/ - Generated HTML documentation (published to GitHub Pages)
• docfx_project/index.md - Main landing page content
• docfx_project/docs/ - Additional documentation articles
• docfx_project/api/ - Auto-generated API reference YAML files

🤝 Contributing

Contributions are welcome! Please see CONTRIBUTING.md for:

• Code quality standards
• Build and test instructions
• Pull request guidelines
• Analyzer configuration details

🙏 Acknowledgments

Built on top of Wolfgang.Etl.Abstractions and Microsoft.Data.SqlClient.