Docs / Bulk Loading

Bulk Loading

What this page covers#

This page explains what bulk loading is, when to use it, and how MooDb helps you send large batches of rows into SQL Server efficiently.

What is bulk loading#

Bulk loading is a fast way of sending many rows into a SQL Server table in one operation.

Instead of inserting rows one at a time, bulk loading packages them together and sends them much more efficiently.

Where bulk usually fits#

Bulk loading is usually more of a back-office or data-movement feature than a normal day-to-day application operation.

It is commonly used for things like:

  • importing CSV data
  • seeding databases
  • syncing batches from another system
  • loading staging tables
  • moving a large prepared set of rows quickly

The simple path: write rows from IEnumerable<T>#

For most application code, the simplest path is to pass a collection of normal .NET objects.

</> C# 
var rows = new[]
{
    new UserImportRow
    {
        Email = "ada@example.com",
        DisplayName = "Ada Lovelace",
        Age = 36,
        IsActive = true,
        CreatedUtc = new DateTime(2024, 01, 02, 03, 04, 05),
        UpdatedUtc = null
    }
};

await db.Bulk.WriteToTableAsync("dbo.tbl_User", rows);

Typed bulk loading uses the row type's public readable instance properties and materialises them into a DataTable internally before calling SQL Server bulk copy.

The table path: write from a DataTable#

If you already have a DataTable, you can pass it directly.

</> C# 
using System.Data;

var table = new DataTable();
table.Columns.Add("Email", typeof(string));
table.Columns.Add("DisplayName", typeof(string));
table.Columns.Add("Age", typeof(int));
table.Columns.Add("IsActive", typeof(bool));
table.Columns.Add("CreatedUtc", typeof(DateTime));
table.Columns.Add("UpdatedUtc", typeof(DateTime));

table.Rows.Add("ada@example.com", "Ada Lovelace", 36, true, new DateTime(2024, 01, 02, 03, 04, 05), DBNull.Value);

await db.Bulk.WriteToTableAsync("dbo.tbl_User", table);

MooBulkOptions gives you more control#

MooBulkOptions lets you control both the bulk copy itself and the SQL that runs around it.

That includes:

  • BatchSize
  • BulkCopyTimeoutSeconds
  • PreparationSql
  • CleanupSql

Preparation SQL#

PreparationSql runs before the bulk copy starts.

Common examples include:

  • deleting existing rows from a staging table
  • truncating a table before loading fresh data
  • reseeding an identity
  • disabling constraints for a controlled load
</> C# 
var options = new MooBulkOptions
{
    PreparationSql = "DELETE FROM [dbo].[tbl_User];"
};

Cleanup SQL#

CleanupSql runs after the bulk copy succeeds.

Cleanup SQL is also run on the failure path when preparation SQL ran, swallowing any cleanup exception in that failure cleanup path.

That means cleanup logic should be written as operational housekeeping SQL that is safe for your scenario.

Common examples include:

  • re-enabling constraints
  • clearing helper rows
  • post-load housekeeping statements
</> C# 
var options = new MooBulkOptions
{
    CleanupSql = "UPDATE [dbo].[tbl_User] SET [DisplayName] = [DisplayName] + N' Imported';"
};

Example with both#

</> C# 
var options = new MooBulkOptions
{
    PreparationSql = "DELETE FROM [dbo].[tbl_User];",
    CleanupSql = "UPDATE [dbo].[tbl_User] SET [DisplayName] = [DisplayName] + N' Imported';"
};

await db.Bulk.WriteToTableAsync("dbo.tbl_User", rows, options);

Memory trade-off#

Typed bulk loading is convenient, but it materialises the input rows into a DataTable before sending them to SQL Server.

That is often a good trade for convenience, but it is worth remembering for larger loads because it is not a streaming API.