Bulk Writer 1.0.1 released

A few years back, I had a somewhat new problem on hand. I needed to,
on a daily basis, wipe and re-insert about 100 million rows of product data into a customer support database. The data was in the form of a single CSV file (zipped of course), and I needed to find a way to efficiently insert that data into SQL Server. Enter SQL Bulk Copy. It can, lightning fast, bulk insert all that data in seconds what might take hours one row at a time.

There's a problem, however, in that SQL Bulk Copy only supports a few sources of data:

• CSV file through the command line
• CSV file through T-SQL
• .NET through SqlBulkCopy class

The last one is what I'd like to use, since I can then do more complex transformations in C# code versus some big expensive GUI ETL tool. However, the SqlBulkCopy class only supports ADO.NET primitives (DataTable, DataRow, and DbDataReader/IDataReader). Not exactly friendly for us!

Enter Bulk Writer, which takes care of mapping any DTO to SqlBulkCopy.

First, create a DTO that represents the data to insert:

[Table("Products")]
public class Product {
    public string Sku { get; set; }
    
    [Column("SkuDesc")]
    public string Name { get; set; }
}

By default Bulk Writer matches table/column names based on name, but you can use Data Annotations to map as needed, including:

• TableAttribute
• ColumnAttribute (Name and Order)
• KeyAttribute
• NotMappedAttribute (to skip properties)

Bulk Writer takes any IEnumerable<T> and streams this through SqlBulkCopy:

IEnumerable<string> ReadFile(string path)
{
    using (var stream = File.OpenRead(path))
    using (var reader = new StreamReader(stream))
    {
        while (reader.Peek() >= 0)
        {
            yield return reader.ReadLine();
        }
    }
}

var items =
    from line in ReadFile(@"C:\products.csv")
    let values = line.Split(',')
    select new Product {Sku = values[0], Name = values[1]};

Then we can construct our BulkWriter and stream these values:

using (var bulkWriter = new BulkWriter<Product>(connectionString)) {
    bulkWriter.WriteToDatabase(items);
}

Internally, we create an DbDataReader implementation that wraps your IEnumerable, meaning we bulk insert your items streaming from an IEnumerable, keeping memory usage down and the speed wicked fast (millions of items in just seconds).

Bulk Writer supports .NET 4.5.2 and .NET Standard 1.3.

Check it out via NuGet.

Enjoy!