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

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();
    }
}

9 个解决方案

#1

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.
}

#2

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.

高度精细的“解决方案”往往会导致错误的代码。简单就是更好。

#3

MSDN Docs make this pretty clear...

MSDN Docs让这个很清楚......

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.
关闭和处置在功能上是等效的。

This is why a using block results in a closed connection. using calls Dispose for you.

这就是使用块导致闭合连接的原因。使用呼叫为您处理。

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.

重要的安全提示。谢谢，Egon。

#4

I'm guessing that by "_SqlConnection.State == ConnectionState.Closed" you 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模式。

#5

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，但它看起来更好。

#6

See this question for the answer:

看到这个问题的答案：

Close and Dispose - which to call?

关闭和处理 - 要打电话？

If your connection lifetime is a single method call, use the using feature of the language to ensure the proper clean-up of the connection. While a try/finally block 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 Dispose regardless 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 IDisposable and clean-up the connection in Dispose.

如果您的连接生命周期对应于包含类的生命周期，则实现IDisposable并清除Dispose中的连接。

#7

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 statements are old hat to many developers, but younger developers might not know that off hand.

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

#8

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

没有必要尝试。最终围绕“使用”，使用IS一试..最终

#9

-4

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 :)

希望有所帮助:)

#1