SqlBulkCopy - Unexpected existing transaction

Question

I am using SqlBulkCopy to insert large amount of data:

try {    using (var bulkCopy = new SqlBulkCopy(connection))    {       connection.Open();        using (var tran = connection.BeginTransaction(IsolationLevel.ReadCommitted))       {           bulkCopy.DestinationTableName = "table";            bulkCopy.ColumnMappings.Add("...", "...");                                        using (var dataReader = new ObjectDataReader<MyObject>(data))           {               bulkCopy.WriteToServer(dataReader);           }            tran.Commit();           return true;       }    } } catch (Exception ex) {     return false; } 

But I always get exception:

Unexpected existing transaction. 

Why this exception happens?

Answer 1

"Unexpected existing transaction" ... Why this exception happens?

This happens because using the SqlBulkCopy constructor without specifying a transaction will create its own transaction internally.

Avoid this by creating your transaction and then use it to create the SqlBulkCopy. SqlBulkCopy can be created with the transaction that you want to use, like this:

connection.Open(); using (var tran = connection.BeginTransaction(IsolationLevel.ReadCommitted)) {     using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, tran))     {