C# 在抛出异常时确保 SQL 连接关闭的正确方法是什么？

What is the proper way to ensure a SQL connection is closed when an exception is thrown?

提问by Eric Schoonover

I use a pattern that looks something like this often. I'm wondering if this is alright or if there is a best practice that I am not applying here.

我经常使用看起来像这样的模式。我想知道这是否可以，或者是否有我没有在这里应用的最佳实践。

Specifically I'm wondering; in the case that an exception is thrown is the code that I have in the finally block enough to ensure that the connection is closed appropriately?

具体来说，我想知道；在抛出异常的情况下，我在 finally 块中的代码是否足以确保正确关闭连接？

public class SomeDataClass : IDisposable
{
    private SqlConnection _conn;

    //constructors and methods

    private DoSomethingWithTheSqlConnection()
    {
        //some code excluded for brevity

        try
        {
            using (SqlCommand cmd = new SqlCommand(SqlQuery.CountSomething, _SqlConnection))
            {
                _SqlConnection.Open();
                countOfSomething = Convert.ToInt32(cmd.ExecuteScalar());
            }
        }
        finally
        {
            //is this the best way?
            if (_SqlConnection.State == ConnectionState.Closed)
                _SqlConnection.Close();
        }

        //some code excluded for brevity
    }

    public Dispose()
    {
        _conn.Dispose();
    }
}

采纳答案by Sklivvz

Wrap your database handling code inside a "using"

将您的数据库处理代码包裹在“使用”中

using (SqlConnection conn = new SqlConnection (...))
{
    // Whatever happens in here, the connection is 
    // disposed of (closed) at the end.
}

回答by Aaron Maenpaa

Since you're using IDisposables anyway. You can use the 'using' keyword, which is basically equivalent to calling dispose in a finally block, but it looks better.

因为您无论如何都在使用 IDisposables。您可以使用 'using' 关键字，它基本上等同于在 finally 块中调用 dispose ，但它看起来更好。

回答by Jeffrey L Whitledge

I'm guessing that by _SqlConnection.State == ConnectionState.Closedyou meant !=

我猜_SqlConnection.State == ConnectionState.Closed你的意思是!=

This will certainly work. I think it is more customary to contain the connection object itself inside a using statement, but what you have is good if you want to reuse the same connection object for some reason.

这肯定会奏效。我认为在 using 语句中包含连接对象本身更为习惯，但是如果您出于某种原因想重用相同的连接对象，那么您所拥有的就很好。

One thing that you should definitely change, though, is the Dispose()method. You should not reference the connection object in dispose, because it may have already been finalized at that point. You should follow the recommended Dispose pattern instead.

但是，您绝对应该改变的一件事是Dispose()方法。您不应在 dispose 中引用连接对象，因为它可能在那时已经完成。您应该遵循推荐的 Dispose 模式。

回答by Torbj?rn Gyllebring

Might I suggest this:

我可以建议这个：

    class SqlOpener : IDisposable
    {
        SqlConnection _connection;

        public SqlOpener(SqlConnection connection)
        {
            _connection = connection;
            _connection.Open();

        }

        void IDisposable.Dispose()
        {
            _connection.Close();
        }
    }

    public class SomeDataClass : IDisposable
    {
        private SqlConnection _conn;

        //constructors and methods

        private void DoSomethingWithTheSqlConnection()
        {
            //some code excluded for brevity
            using (SqlCommand cmd = new SqlCommand("some sql query", _conn))
            using(new SqlOpener(_conn))
            {
                int countOfSomething = Convert.ToInt32(cmd.ExecuteScalar());
            }
            //some code excluded for brevity
        }

        public void Dispose()
        {
            _conn.Dispose();
        }
    }

Hope that helps :)

希望有帮助:)

回答by Ed Schwehm

Put the connection close code inside a "Finally" block like you show. Finally blocks are executed before the exception is thrown. Using a "using" block works just as well, but I find the explicit "Finally" method more clear.

将连接关闭代码放在“最终”块中，如您所示。在抛出异常之前执行 finally 块。使用“using”块也同样有效，但我发现明确的“Finally”方法更清晰。

Using statements are old hat to many developers, but younger developers might not know that off hand.

对于许多开发人员来说，使用语句是老生常谈，但年轻的开发人员可能不知道这一点。

回答by Cyber Oliveira

The .Net Framework mantains a connection pool for a reason. Trust it! :) You don't have to write so much code just to connect to the database and release the connection.

.Net Framework 维护连接池是有原因的。相信它！:) 你不必为了连接到数据库并释放连接而编写这么多代码。

You can just use the 'using' statement and rest assured that 'IDBConnection.Release()' will close the connection for you.

您可以只使用 'using' 语句并放心，'IDBConnection.Release()' 将为您关闭连接。

Highly elaborate 'solutions' tend to result in buggy code. Simple is better.

高度复杂的“解决方案”往往会导致代码错误。简单的更好。

回答by Amy B

MSDN Docsmake this pretty clear...

MSDN 文档使这一点非常清楚......

• The Close method rolls back any pending transactions. It then releases the connection to the connection pool, or closes the connection if connection pooling is disabled.

• Close 方法回滚任何挂起的事务。然后它释放与连接池的连接，或者如果连接池被禁用则关闭连接。

You probably haven't (and don't want to) disable connection pooling, so the pool ultimately manages the state of the connection after you call "Close". This could be important as you may be confused looking from the database server side at all the open connections.

您可能没有（也不想）禁用连接池，因此在您调用“关闭”之后，池最终会管理连接的状态。这可能很重要，因为从数据库服务器端查看所有打开的连接可能会感到困惑。

---

---

• An application can call Close more than one time. No exception is generated.

• 一个应用程序可以多次调用 Close。不会产生异常。

So why bother testing for Closed? Just call Close().

那么为什么要为 Closed 测试呢？只需调用 Close()。

---

---

• Close and Dispose are functionally equivalent.

• Close 和 Dispose 在功能上是等效的。

This is why a usingblock results in a closed connection. usingcalls Dispose for you.

这就是using块会导致连接关闭的原因。 使用调用为您处理。

---

---

• Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class.

• 不要在类的 Finalize 方法中对 Connection、DataReader 或任何其他托管对象调用 Close 或 Dispose。

Important safety tip. Thanks, Egon.

重要的安全提示。谢谢，埃贡。

回答by Brannon

See this question for the answer:

看到这个问题的答案：

Close and Dispose - which to call?

关闭并处理 - 调用哪个？

If your connection lifetime is a single method call, use the usingfeature of the language to ensure the proper clean-up of the connection. While a try/finallyblock is functionally the same, it requires more code and IMO is less readable. There is no need to check the state of the connection, you can call Disposeregardless and it will handle cleaning-up the connection.

如果您的连接生命周期是单个方法调用，请使用using该语言的功能来确保正确清理连接。虽然try/finally块在功能上是相同的，但它需要更多的代码并且 IMO 的可读性较差。不需要检查连接的状态，你可以Dispose不管它，它会处理清理连接。

If your connection lifetime corresponds to the lifetime of a containing class, then implement IDisposableand clean-up the connection in Dispose.

如果您的连接生命周期对应于包含类的生命周期，则IDisposable在Dispose.

回答by BlackTigerX

no need for a try..finally around a "using", the using ISa try..finally

不需要尝试..终于围绕“使用”，使用是尝试..最后