SQL错误没有回滚整个SqlCommand

I have been placing set xact_abort on into SQL command statements and noticed that it is not rolling back updates, inserts, etc in my C# SqlCommand on error. Taken from this post. The MSDN states that:

我一直将set xact_abort放在SQL命令语句中，并注意到它并没有回滚c# SqlCommand中的更新、插入等等。从这篇文章。MSDN州:

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

当设置XACT_ABORT为ON时，如果Transact-SQL语句引发运行时错误，则终止并回滚整个事务。

The general format of my SQl query is:

我的SQl查询的一般格式是:

set xact_abort on
INSERT INTO Table1
UPDATE Table2
UPDATE Table3
UPDATE Table4
--Finally
SELECT someValue

I noticed that on error my SQL command was not being rolled back. The particular error is this case was that the data length of one parameter exceeded the columns specified length. I am using SqlCommand and SqlParameter to create SQL queries.

我注意到错误时，SQL命令没有回滚。具体的错误是，一个参数的数据长度超过了列指定的长度。我使用SqlCommand和SqlParameter创建SQL查询。

I am not looking to handle the exceptions in SQL, but it is very important that any errors do not commit any changes to the database.

我不打算处理SQL中的异常，但是非常重要的是，任何错误都不能向数据库提交任何更改。

Typically errors include: column does not exist, wrong data type, data would be truncated due to length, etc.

通常的错误包括:列不存在，错误的数据类型，由于长度等原因数据将被截断。

Should I be using something other than set xact_abort on? Thanks in advance!

除了设置xact_abort之外，我应该使用其他方法吗?提前谢谢!

2 个解决方案

#1

The statement you quoted from MSDN is correct: with XACT_ABORT set to ON, any error will abort the batch and roll-back any active Transaction. The confusion here is that in SQL Server, each statement is a Transaction by itself by default. If you want multiple statements to be grouped together into an explicit Transaction, then you need to use BEGIN TRAN; along with COMMIT;. The following example illustrates this behavior:

您从MSDN中引用的语句是正确的:将XACT_ABORT设置为ON，任何错误都将中止批处理并回滚任何活动事务。这里的混淆在于，在SQL Server中，每个语句都是默认的事务。如果希望将多个语句组合到一个显式事务中，则需要使用BEGIN TRAN;一起提交。下面的例子说明了这种行为:

Run this:

运行这个:

SET XACT_ABORT ON;
CREATE TABLE #Bob (ID INT);

INSERT INTO #Bob (ID) VALUES (1);

BEGIN TRAN;

INSERT INTO #Bob (ID) VALUES (2);
INSERT INTO #Bob (ID) VALUES (3);
INSERT INTO #Bob (ID) VALUES (4 / 0);

COMMIT TRAN;

Then run this separately (since the error in the statements above will abort the entire batch -- due to using XACT_ABORT ON -- and so the SELECT will never execute if you try to run the SELECT at the same time):

然后分别运行这个(因为上面的语句中的错误将会中止整个批处理——因为使用XACT_ABORT)，所以如果您试图同时运行SELECT，那么SELECT将永远不会执行):

SELECT * FROM #Bob;

It will return a single row containing 1 since that statement executed by itself and not within the explicit Transaction. Once you add the BEGIN TRAN; and COMMIT TRAN; statements to your code, it will work as you are expecting it to.

它将返回包含1的一行，因为该语句是由它自己执行的，而不是在显式事务中执行的。一旦添加了BEGIN TRAN;和提交TRAN;对代码的语句，它将像您期望的那样工作。

#2

One pattern to deal with transactions from C# code is the following:

从c#代码处理事务的一种模式是:

try
{
    var connection = new SqlConnection(connectionString);
    connection.Open();

    var trans = connection.BeginTransaction(); 

    using (var command = connection .CreateCommand())
    {
        command.Transaction = trans;
        command.CommandText = "...";
        command.ExecuteNonQuery();
    }

    // other commands may be defined here
    // command can be included or excluded from transaction (do not set Transaction property)

    // commits the transaction
    trans.Commit(); 
}
// best practice is to catch specific exception types like `SqlException`
catch (Exception ex) //error occurred
{
    trans.Rollback();
    // log error somewhere
}
finally
{
    // execute no-matter what
}

This pattern has the following advantages:

这种模式有以下优点:

No need to worry about SET XACT_ABORT (OFF by default)
无需担心设置XACT_ABORT(默认为OFF)
Better exception handling
更好的异常处理

Note: you may be interested in Unit of Work pattern

注意:您可能对工作模式的单位感兴趣

#1