ADO - ADO.NET Bulk Operations

Bulk operations are used to insert or update very large volumes of data efficiently with minimal database round trips.

In ADO.NET, this is mainly done using SqlBulkCopy.

Why bulk operations exist

Row-by-row inserts are slow because:

• Each insert = network round trip

• Each insert = logging + locking overhead

Bulk operations send data in batches, drastically improving performance.

Core class: SqlBulkCopy

Used to copy data from:

• DataTable

• DataReader

• CSV (via reader)

• In-memory collections (after conversion)

Directly into a SQL Server table

Basic example

using (SqlConnection conn = new SqlConnection(cs))
{
    conn.Open();

    using (SqlBulkCopy bulk = new SqlBulkCopy(conn))
    {
        bulk.DestinationTableName = "dbo.Users";
        bulk.WriteToServer(dataTable);
    }
}

✔ Inserts thousands/millions of rows
✔ One database call
✔ Very fast

Column mapping (mandatory in real projects)

bulk.ColumnMappings.Add("UserId", "Id");
bulk.ColumnMappings.Add("UserName", "Name");
bulk.ColumnMappings.Add("Email", "Email");

Rule: source column name ≠ destination column name → mapping required

Batch size & timeout (performance tuning)

bulk.BatchSize = 5000;
bulk.BulkCopyTimeout = 60;

Why this matters

• BatchSize limits rows per transaction

• Prevents long locks

• Reduces rollback size on failure

Using DataReader (best for large files)

using (SqlCommand cmd = new SqlCommand("SELECT * FROM TempUsers", conn))
using (SqlDataReader reader = cmd.ExecuteReader())
{
    bulk.WriteToServer(reader);
}

✔ Streams data
✔ Minimal memory usage
✔ Best for very large datasets

Bulk operations + Transactions

SqlTransaction tx = conn.BeginTransaction();

using (SqlBulkCopy bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tx))
{
    bulk.DestinationTableName = "Orders";
    bulk.WriteToServer(table);
}

tx.Commit();

✔ All-or-nothing bulk insert
✔ Rollback supported

Important SqlBulkCopyOptions

Example:

new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock, tx)

Common failures & causes

When NOT to use bulk operations

• Small data (<100 rows)

• Business logic per row is required

• Triggers must run for every insert (unless FireTriggers)

Real performance numbers (approx)

ADO.NET bulk operations (SqlBulkCopy) allow extremely fast, transactional, large-scale data insertion by minimizing network and logging overhead.