问题描述

我通过 SqlBulkCopy 插入数据，如下所示:

I am inserting data via SqlBulkCopy like so:

public void testBulkInsert(string connection, string table, DataTable dt)
{
    using (SqlConnection con = new SqlConnection(connection))
    {
        con.Open();

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
        {
            bulkCopy.DestinationTableName = table;
            bulkCopy.WriteToServer(dt);
        }
    }
}

这是否会自动包装在 SQL 事务中，以便在数据库中途出现问题时，将保持与批量插入开始之前相同的状态?还是会插入一半的数据?

Will this automatically be wrapped in a SQL transaction so that if something goes wrong half way through the DB will be left in the same state as it was before the bulk insert began? Or will half the data be inserted?

即我是否有必要显式调用 con.BeginTransaction

i.e. is it necessary for me to explicitly call con.BeginTransaction

或者，如果我调用 SqlBulkCopy 的构造函数，它接受一个字符串，这是让它在事务中发生的更好方法吗?

Or if I call SqlBulkCopy's constructor that takes a string, is that a better way of getting it to occur in a transaction?

public void testBulkInsert(string connection, string table, DataTable dt)
{
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = table;
        bulkCopy.WriteToServer(dt);
    }
}

我找到了 docs在这件事上有点不清楚，因为他们最初声明

I find the docs a bit unclear on this matter as they initially state that

默认情况下，批量复制操作是作为独立的手术.批量复制操作以非事务性方式发生，没有机会回滚它

By default, a bulk copy operation is performed as an isolated operation. The bulk copy operation occurs in a non-transacted way, with no opportunity for rolling it back

但后来声明

默认情况下，批量复制操作是它自己的事务.当你想要执行专用的批量复制操作，创建一个新实例SqlBulkCopy 与连接字符串，或使用现有的没有活动事务的 SqlConnection 对象.在每个场景中，批量复制操作创建，然后提交或回滚交易.

By default, a bulk copy operation is its own transaction. When you want to perform a dedicated bulk copy operation, create a new instance of SqlBulkCopy with a connection string, or use an existing SqlConnection object without an active transaction. In each scenario, the bulk copy operation creates, and then commits or rolls back the transaction.

所以有必要这样做:

public void testBulkInsert(string connection, string table, DataTable dt)
{
    using (SqlConnection con = new SqlConnection(connection))
    {
        con.Open();
        using (SqlTransaction tr = con.BeginTransaction(IsolationLevel.Serializable))
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con, SqlBulkCopyOptions.Default, tr))
            {
                bulkCopy.DestinationTableName = table;
                bulkCopy.WriteToServer(dt);
            }
            tr.Commit();
        }
    }
}

推荐答案

这里没有来自 SqlBulkCopy 文档的文本/library/tchktcdk(v=vs.110).aspx" rel="noreferrer">msdn

No here is text from SqlBulkCopy documentation in msdn

默认情况下，批量复制操作是作为独立的手术.批量复制操作以非事务方式发生，没有机会回滚它.如果需要全部回滚或部分大容量复制发生错误时，可以使用SqlBulkCopy 管理的事务，执行批量复制操作在现有交易中，或加入System.Transactions 事务.

By default, a bulk copy operation is performed as an isolated operation. The bulk copy operation occurs in a non-transacted way, with no opportunity for rolling it back. If you need to roll back all or part of the bulk copy when an error occurs, you can use a SqlBulkCopy-managed transaction, perform the bulk copy operation within an existing transaction, or be enlisted in a System.Transactions Transaction.

从我给您的链接中正确阅读文档:

Read properly the documentation, from the link which I gave you:

默认情况下，批量复制操作是它自己的事务.当你要执行专用的批量复制操作，请创建一个新的带有连接字符串的 SqlBulkCopy 实例，或使用
没有活动事务的现有 SqlConnection 对象.每一个场景，大容量复制操作创建，然后提交或滚动支持交易.

By default, a bulk copy operation is its own transaction. When you want to perform a dedicated bulk copy operation, create a new instance of SqlBulkCopy with a connection string, or use an
existing SqlConnection object without an active transaction. In each scenario, the bulk copy operation creates, and then commits or rolls back the transaction.

这是为case内部批量复制事务编写的，不是默认的！

This is written for the case internal bulk copy transaction, which is not the default!

   using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
                       connectionString, SqlBulkCopyOptions.KeepIdentity |
                       SqlBulkCopyOptions.UseInternalTransaction))
   {
       ....
   }

仔细查看SqlBulkCopyOptions.UseInternalTransaction！您在 SqlBulkCopy 类构造函数中显式指定 UseInternalTransaction 选项，以显式导致大容量复制操作在其自己的事务中执行，从而导致每批大容量复制操作在单独的事务中执行.由于不同的批处理在不同的事务，如果在大容量复制操作过程中发生错误，将回滚当前批次中的所有行，但之前批次的行将保留在数据库中.

如果您因为发生错误而需要回滚整个大容量复制操作，或者如果大容量复制应该作为可以回滚的更大进程的一部分执行，您可以向 SqlBulkCopy 提供一个 SqlTransaction 对象构造函数.

外部交易案例.

            using (SqlTransaction transaction =
                       destinationConnection.BeginTransaction())
            {
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
                           destinationConnection, SqlBulkCopyOptions.KeepIdentity,
                           transaction))
                {
                     ....
                }
            }

<小时>

就像我在乞求中所说的那样，答案是否定的，您应该使用现有事务或内部批量复制事务.阅读链接中的文档文件，了解更多信息.

Like I said in the begging the answer is no, you should use existing transaction or internal bulk copy transaction. Read the documentation file which is in the link, for more information.

如果你想进行交易，你应该使用我写的两种情况之一.

If you want to have transaction you should use one of the two cases which I wrote.

这篇关于SqlBulkCopy 是否自动启动事务?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！