无法访问 SqlTransaction 对象以在 catch 块中回滚

问题描述

我遇到了一个问题，我发现的所有文章或示例似乎都不关心它.

I've got a problem, and all articles or examples I found seem to not care about it.

我想在一个事务中做一些数据库操作.我想要做的与大多??数示例非常相似:

I want to do some database actions in a transaction. What I want to do is very similar to most examples:

using (SqlConnection Conn = new SqlConnection(_ConnectionString))
{
    try
    {
        Conn.Open();
        SqlTransaction Trans = Conn.BeginTransaction();

        using (SqlCommand Com = new SqlCommand(ComText, Conn))
        {
            /* DB work */
        }
    }
    catch (Exception Ex)
    {
        Trans.Rollback();
        return -1;
    }
}

但问题在于 SqlTransaction Trans 是在 try 块内声明的.所以它在 catch() 块中是不可访问的.大多数示例只是在 try 块之前执行 Conn.Open() 和 Conn.BeginTransaction() ，但我认为这有点冒险，因为两者都可以抛出多个异常.

But the problem is that the SqlTransaction Trans is declared inside the try block. So it is not accessable in the catch() block. Most examples just do Conn.Open() and Conn.BeginTransaction() before the try block, but I think that's a bit risky, since both can throw multiple exceptions.

我错了，还是大多数人只是忽略了这种风险?如果发生异常，能够回滚的最佳解决方案是什么?

Am I wrong, or do most people just ignore this risk? What's the best solution to be able to rollback, if an exception happens?

推荐答案

using (var Conn = new SqlConnection(_ConnectionString))
{
    SqlTransaction trans = null;
    try
    {
        Conn.Open();
        trans = Conn.BeginTransaction();

        using (SqlCommand Com = new SqlCommand(ComText, Conn, trans))
        {
            /* DB work */
        }
        trans.Commit();
    }
    catch (Exception Ex)
    {
        if (trans != null) trans.Rollback();
        return -1;
    }
}

或者你可以更简洁、更轻松地使用它:

or you could go even cleaner and easier and use this:

using (var Conn = new SqlConnection(_ConnectionString))
{
    try
    {
        Conn.Open();
        using (var ts = new System.Transactions.TransactionScope())
        {
            using (SqlCommand Com = new SqlCommand(ComText, Conn))
            {
                /* DB work */
            }
            ts.Complete();
        }
    }
    catch (Exception Ex)
    {     
        return -1;
    }
}