Skip to content

The Mechanic: SQL Bulk Copy

🏗️ The Mechanic: SQL Bulk Copy

When you need to insert 1,000,000 rows into a database, using a foreach loop with INSERT statements is a recipe for failure. SqlBulkCopy is the high-performance mechanic designed specifically for massive data ingestion.

1. Why `SqlBulkCopy`?

Unlike standard commands, SqlBulkCopy uses the TDS (Tabular Data Stream) protocol’s bulk-load operations. It bypasses much of the overhead of individual row processing.

Method	100k Rows	Why?
EF Core `Add()`	~30-60s	Change tracking + individual commands.
Dapper `Execute()`	~10-20s	Reduced overhead, but still row-by-row.
SqlBulkCopy	< 1s	Native bulk-stream protocol.

2. Basic Implementation

SqlBulkCopy works best with a DataTable or an IDataReader.

using Microsoft.Data.SqlClient;
using System.Data;

public async Task BulkInsertProducts(string connectionString, DataTable productTable)
{
    using var bulkCopy = new SqlBulkCopy(connectionString);
    bulkCopy.DestinationTableName = "Products";

    // Optional: Map C# columns to SQL columns if they don't match exactly
    bulkCopy.ColumnMappings.Add("Name", "ProductName");
    bulkCopy.ColumnMappings.Add("Price", "ProductPrice");

    await bulkCopy.WriteToServerAsync(productTable);
}

3. Advanced Tuning: Batching & Notifying

To avoid locking the entire table or running out of memory, you should use BatchSize.

bulkCopy.BatchSize = 5000; // Send 5k rows at a time
bulkCopy.SqlRowsCopied += (s, e) => Console.WriteLine($"Uploaded {e.RowsCopied} rows...");
bulkCopy.NotifyAfter = 5000;

🧪 Professor’s Challenge: The CSV-to-SQL Pipeline

Task: Build a CLI tool that streams a 1GB CSV file directly into SQL Server without loading the whole file into memory.

Use StreamReader to read the CSV line-by-line.
Implement a custom IDataReader (The Mechanic) that wraps the StreamReader.
Pass your custom reader to bulkCopy.WriteToServerAsync(reader).