Is it possible to use System.Transactions.TransactionScope with SqlBulkCopy?

Question

Very simple question: is it possible to use System.Transactions.TransactionScope together with SqlBulkCopy? The documentation Transaction and Bulk Copy Operations doesn't mention anything (at least as of .NET 4.0) and my testing indicates it does not automatically enlist with TransactionScope.

Answer 1

SqlBulkCopy never enlists into a transaction. SqlCommand also does not do that. Common misconception. The enlistment is performed at the time SqlConnection.Open is called. After that, anything that runs on that connection is part of the transaction implicitly. In fact it is no longer allowed to pass an explicit transaction.

If you want SqlBulkCopy to take part in a System.Transactions.Transaction using TransactionScope the transaction must be set at the time you open the connection.

It is very easy to do:

using (var tran = new TransactionScope(...))
using (var conn = new SqlConnection(connStr))
{
  conn.Open(); //This enlists.

  using (var sqlBulkCopy = new SqlBulkCopy(conn)) {
    sqlBulkCopy.WriteToServer(...);
  }

  tran.Complete(); //Commit.
}

This code is all you need. Possible mistakes:

1. The transaction must be opened early enough.
2. Do not use the SqlTransaction parameter of SqlBulkCopy. Pass null.
3. Do not use SqlBulkCopyOptions.UseInternalTransaction.
4. Do not add exception handling unless you want to actually do something. Rollback is automatic if there is no commit.
5. Use the using statement for clean code and deterministic cleanup. Do not manually close or dispose any of these objects unless you have to. This would be redundant.

You can use any batch size you like and all batches will be part of the transaction. Therefore, batching has limited value (in particular the transaction log cannot be truncated early). Try no batching at all first.