SqlBulkCopy - Unexpected existing transaction

c# sqlbulkcopy sql-server sql-server-2008

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?

1
19
10/1/2013 1:17:18 PM

Accepted Answer

"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))
    {
38
10/1/2013 1:38:34 PM

Popular Answer

You need to use the constructor that takes in the transaction so SqlBulkCopy will be aware of the transaction

connection.Open();

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

       bulkCopy.ColumnMappings.Add("...", "...");                            

       using (var dataReader = new ObjectDataReader<MyObject>(data))
       {
          bulkCopy.WriteToServer(dataReader);
       }

       tran.Commit();
       return true;

   }
}


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow