错误 - 与当前连接关联的事务已完成但尚未处置 [英] Error - The transaction associated with the current connection has completed but has not been disposed

问题描述

我一直有使用麻烦的TransactionScope 来包装多个数据库查询到一个事务中，我使用SqlBulkCopy的与BATCHSIZE 500当我增加了批量大小为1000我收到错误：

I've been having trouble using the TransactionScope to wrap multiple database queries into a transaction, I am using SqlBulkCopy with batchsize 500. When I increased the batch size to 1000 I am getting the error:

与当前连接相关联的事务已完成
  但一直没有被释放。本次交易前必须先进行处理
  连接可用于执行SQL语句。

The transaction associated with the current connection has completed but has not been disposed. The transaction must be disposed before the connection can be used to execute SQL statements.

这是code我使用的：

This is the code I am using:

using (var scope = new TransactionScope())
{
    using (var connection = (SqlConnection)customerTable.OpenConnection())
    {
        var table1BulkCopy = new SqlBulkCopy(connection)
        {
            BatchSize = BATCH_SIZE,
            DestinationTableName = TableName1
        };

        table1BulkCopy.WriteToServer(table1DataTable);

        var table2BulkCopy = new SqlBulkCopy(connection)
        {
            BatchSize = BATCH_SIZE,
            DestinationTableName = TableName2
        };

        table2BulkCopy.WriteToServer(table2DataTable);

        var table3BulkCopy = new SqlBulkCopy(connection)
        {
            BatchSize = BATCH_SIZE,
            DestinationTableName = TableName3
        };

        table1BulkCopy.WriteToServer(table3DataTable);

        var table4BulkCopy = new SqlBulkCopy(connection)
        {
            BatchSize = BATCH_SIZE,
            DestinationTableName = TableName4
        };

        table4BulkCopy.WriteToServer(table4DataTable);

        scope.Complete();
    }
}

谢谢，
普拉迪普

Thanks, Pradeep

推荐答案

我知道这是迟到了，但也许这将帮助别人。

I know this is late, but perhaps it will help someone else.

这可以在发生事务超时。您可以增加超时为您的交易是这样的（适合您的交易预期长度使用价值）。在code以下是15分钟：

This can happen when the transaction times out. You can increase the timeout for your transaction like this (use values appropriate for the expected length of your transaction). The code below is for 15 minutes:

using (TransactionScope scope = 
             new TransactionScope(TransactionScopeOption.Required, 
                                   new System.TimeSpan(0, 15, 0)))
  {
      // working code here
  }

这就是为什么它可以工作了BATCHSIZE 500而不是1000。

This is why it could have worked for batchsize 500 and not for 1000.

这篇关于错误 - 与当前连接关联的事务已完成但尚未处置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！