CODEWITHSUNDEEP
c#sql-serversql-server-2008sqlbulkcopy
1110
1110

Reputation: 6829

SqlBulkCopy - Unexpected existing transaction

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?

Upvotes: 26

Views: 15375

Answers (2)

jltrem
jltrem

Reputation: 12524

"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))
    {

Upvotes: 55

Scott Chamberlain
Scott Chamberlain

Reputation: 127563

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;

   }
}

Upvotes: 8

Related Questions