Bulk operations and async — ADO.NET — Connected & Disconnected Data Access

8. Bulk operations and async

IntermediateMediumLesson 8 of 9

csharp

using var bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.TableLock, tx)
{
    DestinationTableName = "dbo.StagingSales",
    BatchSize = 10_000,
    BulkCopyTimeout = 600
};
bulk.ColumnMappings.Add("Date", "SaleDate");
bulk.ColumnMappings.Add("Amount", "Amount");
await bulk.WriteToServerAsync(dataTableOrDataReader);   // streams rows using the bulk insert protocol

SqlBulkCopy loads millions of rows in seconds vs minutes with row-by-row INSERTs.
Use async APIs (OpenAsync, ExecuteReaderAsync, ReadAsync) in web apps to free threads during I/O.
CommandTimeout default is 30 seconds; set it per command for long reports.
Batch many statements with SqlBatch/DbBatch (.NET 6+) or TVPs instead of N round trips.