triaxis.Data.SqlImportExport 0.3.0

triaxis.Data.SqlImportExport

Bulk import and export helpers for Microsoft SQL Server. Stream entire databases to and from CSV files, or plug in your own data source.

Installation

dotnet add package triaxis.Data.SqlImportExport

Setup

Register the services with the DI container:

services.AddSqlImportExport();

Then inject IBulkImportService and/or IBulkExportService where needed.

Exporting

Export every table in the database to a directory of CSV files (one file per table, named <TableName>.csv):

await using var con = new SqlConnection(connectionString);
await _export.BulkExportAsync(con).ToCsvDirectoryAsync("output/");

Export options

await _export.BulkExportAsync(con, new BulkExportOptions
{
    TableFilter = name => name.StartsWith("Foo"),                  // include only matching tables
    ColumnFilter = (table, column) => column != "PasswordHash",    // exclude specific columns
}).ToCsvDirectoryAsync("output/");

Importing

Import a directory of CSV files into the database. Each file is matched to a table by name:

await using var con = new SqlConnection(connectionString);
await _import.BulkImportAsync(con, CsvSource.FromDirectory("output/"));

Import strategies

The default strategy is Insert, which uses bulk copy for maximum throughput and fails on duplicate keys. Other strategies use a staging table and a MERGE statement:

Set a strategy globally or per source:

// globally
await _import.BulkImportAsync(con, CsvSource.FromDirectory("output/"), new BulkImportOptions
{
    Strategy = BulkImportStrategy.Upsert,
});

// per source (IBulkImportSource.Strategy overrides the global one)

Import options

await _import.BulkImportAsync(con, source, new BulkImportOptions
{
    Strategy = BulkImportStrategy.InsertIgnore,
    SkipIdentity = true,       // let SQL Server generate identity values instead of using source values
    KeepNulls = true,          // don't substitute column defaults for null values
    SkipConstraints = false,   // verify foreign key constraints after import (default: true)
    DryRun = true,             // roll back the transaction at the end; useful for validation
    BatchSize = 5000,          // rows per batch (default: 1000)
    Timeout = TimeSpan.FromMinutes(10),
});

Inserted ID range

When SkipIdentity = true and the strategy is Insert or Truncate, the return value contains the range of identity values that SQL Server assigned to each table:

var ranges = await _import.BulkImportAsync(con, CsvSource.FromDirectory("output/"), new BulkImportOptions
{
    SkipIdentity = true,
});

foreach (var range in ranges)
{
    Console.WriteLine($"{range.SourceName}: IDs {range.First}–{range.Last}");
}

// build a lookup if needed
var byTable = ranges.ToDictionary(r => r.SourceName);

Custom import sources

Implement IBulkImportSource to supply data from any source:

public class MySource : IBulkImportSource
{
    public string Name => "MyTable";
    public BulkImportStrategy? Strategy => null; // use global strategy

    public Task<IEnumerable<string>> GetColumnNamesAsync() =>
        Task.FromResult<IEnumerable<string>>(["Id", "Name", "Value"]);

    public async IAsyncEnumerable<object[]> EnumerateDataAsync()
    {
        await foreach (var item in GetItemsAsync())
            yield return [item.Id, item.Name, item.Value];
    }
}

Then pass it to BulkImportAsync as an IAsyncEnumerable<IBulkImportSource>.

License

This package is licensed under the MIT License

Copyright © 2025 triaxis s.r.o.