ADO - ADO.NET Batch Updates & Command Batching

1. What is Command Batching?

Command batching means sending multiple SQL statements to the database in one round trip instead of executing them one by one.

Goal: reduce network latency + improve throughput.

2. Why batching matters (fact)

Each ExecuteNonQuery() call:

• Creates a network round trip

• Incurs parsing + execution overhead

1000 updates → 1000 round trips (slow)
1 batch → 1 round trip (fast)

3. Method 1: Multiple SQL statements in one command

Example

SqlCommand cmd = new SqlCommand(@"
    UPDATE Users SET Active = 0 WHERE LastLogin < '2022-01-01';
    UPDATE Orders SET Status = 'Expired' WHERE OrderDate < '2022-01-01';
", conn);

cmd.ExecuteNonQuery();

✔ One DB call
✔ Simple
❌ Harder error isolation

4. Method 2: DataAdapter Batch Updates (real ADO.NET batching)

Key property

adapter.UpdateBatchSize = 50;

Example

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Products", conn);

SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.UpdateBatchSize = 100;

adapter.Update(dataTable);

What happens internally

• ADO.NET groups 100 row updates

• Sends them as one batch

• Repeats until complete

✔ Cleaner
✔ Supports rollback
✔ Proper error reporting

5. Method 3: Parameter array batching

StringBuilder sb = new StringBuilder();

for (int i = 0; i < orders.Count; i++)
{
    sb.Append($"UPDATE Orders SET Status='Shipped' WHERE Id={orders[i]};");
}

SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
cmd.ExecuteNonQuery();

✔ Very fast
❌ Risky if not parameterized
❌ SQL injection if careless

6. Batching + Transactions (correct way)

SqlTransaction tx = conn.BeginTransaction();

SqlCommand cmd = new SqlCommand(batchSql, conn, tx);
cmd.ExecuteNonQuery();

tx.Commit();

✔ Atomic batch
✔ Faster than per-row commits

7. Batch size tuning (important)

Always test with real data.

8. Batch Updates vs Bulk Copy (don’t confuse)

9. Common mistakes

❌ Not using transactions
❌ Mixing different connection strings
❌ Huge batches causing deadlocks
❌ Assuming batching = bulk copy

10. When to use batching

• Large UPDATE / DELETE workloads

• Syncing modified rows

• DataAdapter-based applications

Avoid for:

• Single-row operations

• Simple CRUD APIs