Skip to content

A convention based wrapper for bulk loading data into SQL databases. Supports SQL Server SqlBulkCopy and PostgreSQL binary COPY.

License

Notifications You must be signed in to change notification settings

JasonDV/SQLBulkLoader

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

36 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

SQL Bulk Loader

A convention based wrapper to allow for bulk loading data into a SQL RDMS.

The purpose of this utility is to reduce the amount of code necessary to preform bulk inserts into database tables. This utility allows for bulk inserting with only a collection of DTOs and a call to the BulkLoader class.

The class is configurable and handles remapping fields between DTO and target table, ignoring DTO properties, and controlling batch size. As well, the bulk loader avoids using a DataTable to reduce the memory overhead of preforming the SQLBulkCopy (SQL Server). The PostgreSQL version uses the binary COPY feature built into Postgre.

Nuget:

Targets

  • .NETFramework 4.6.1
  • .NETStandard 2.0

Basic usage

With this sample table:

CREATE TABLE dbo.Sample(
    Pk INT IDENTITY(1,1) PRIMARY KEY,
    TextValue nvarchar(200) NULL,
    IntValue int NULL,
    DecimalValue decimal(18,8) NULL
)

Simple bulk load with insert of identity values.

var dtos = new[]
{
    new SampleSurrogateKey
    {
        Pk = 100,
        TextValue = "JJ",
        IntValue = 100,
        DecimalValue = 100.99m
    }
};

new BulkLoader()
    .InsertWithOptions("Sample", conn, true, dtos)
    .Execute();

Bulk load with DTO to target table remapping.

var dtos = new[]
{
    new SampleSurrogateKeyDifferentNamesDto
    {
        Pk = 100,
        TextValueExtra = "JJ",
        IntValueExtra = 100,
        DecimalValueExtra = 100.99m
    }
};

new BulkLoader()
    .InsertWithOptions("Sample", conn, true, dtos)
    .With(c => c.TextValueExtra, "TextValue")
    .With(c => c.IntValueExtra, "IntValue")
    .With(c => c.DecimalValueExtra, "DecimalValue")
    .Execute();

Bulk load with ignore of property values.

var dtos = new[]
{
    new SampleSurrogateKey
    {
        Pk = 100,
        TextValue = "JJ",
        IntValue = 100,
        DecimalValue = 100.99m
    }
};

 new BulkLoader()
    .InsertWithOptions("Sample", conn, true, dtos)
    .Without(c => c.Pk)
    .Execute();

Dependencies

The SQL Server (SqlBulkCopy) version of this utility uses fast-member to create an efficient IDataReader that can feed directly into the SqlBulkCopy WriteToServer method. Normally, a DataTable is created for each batched write to the server. fast-member: https://github.com/mgravell/fast-member

The PostgreSQL (COPY) verison of this utility uses the Npgsql data provider to interact with a PostgreSQL instance. Npgsql: https://github.com/npgsql/npgsql

About

A convention based wrapper for bulk loading data into SQL databases. Supports SQL Server SqlBulkCopy and PostgreSQL binary COPY.

Topics

Resources

License

Stars

Watchers

Forks

Packages

No packages published