The number of rows for a batch sent to the server.
The following example shows how to bulk load data in batches of 50 rows.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString, transaction))
{
// SET BatchSize value.
bulkCopy.BatchSize = 50;
bulkCopy.DestinationTableName = "TheDestinationTable";
bulkCopy.WriteToServer(dt);
}
bulkCopy.BatchSize = 4000;
By default, SqlBulkCopy will process the operation in a single batch. If you have 100000 rows to copy, 100000 rows will be copied at once.
Not specifying a BatchSize can impact your application:
There is no value that fit all scenarios. Some people will recommend a BatchSize of 1000, 2000, or 5000 and they are all good values which fit in the rule "not to high, not to low". All these value will have slighty performance difference which is sometime better or worse.
var transaction = connection.BeginTransaction();
using (var sqlBulk = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
// SET BatchSize value.
sqlBulk.BatchSize = 4000;
sqlBulk.DestinationTableName = "Customers";
sqlBulk.WriteToServer(dt);
transaction.Commit();
}
By default, SqlBulkCopy do not use a transaction. So if a batch fail, there is no rollback of all rows already processed from previous batch.
If you set the UseInternalTransaction option to true, a transaction will be created for every batch. Again, if a batch fail, there is no rollback of all rows already processed from previous batch.
The best solution, is creating your own transaction.