C# 即使在 using { } 中，.net SqlConnection 也不会关闭

.net SqlConnection not being closed even when within a using { }

提问by Ash

Please help!

请帮忙！

Background info

背景资料

I have a WPF application which accesses a SQL Server 2005 database. The database is running locally on the machine the application is running on.

我有一个访问 SQL Server 2005 数据库的 WPF 应用程序。数据库在运行应用程序的机器上本地运行。

Everywhere I use the Linq DataContext I use a using { } statement, and pass in a result of a function which returns a SqlConnection object which has been opened and had an SqlCommand executed using it before returning to the DataContext constructor.. I.e.

在我使用 Linq DataContext 的任何地方，我都使用 using { } 语句，并传入一个函数的结果，该函数返回一个 SqlConnection 对象，该对象已打开并在返回到 DataContext 构造函数之前使用它执行了 SqlCommand .. 即

// In the application code
using (DataContext db = new DataContext(GetConnection()))
{
    ... Code 
}

where getConnection looks like this (I've stripped out the 'fluff' from the function to make it more readable, but there is no additional functionality that is missing).

getConnection 看起来像这样（我已经从函数中去除了“绒毛”以使其更具可读性，但没有缺少其他功能）。

// Function which gets an opened connection which is given back to the DataContext constructor
public static System.Data.SqlClient.SqlConnection GetConnection()
{
   System.Data.SqlClient.SqlConnection Conn = new System.Data.SqlClient.SqlConnection(/* The connection string */);

    if ( Conn != null )
    {
        try
        {
            Conn.Open();
        }
        catch (System.Data.SqlClient.SqlException SDSCSEx)
        {
             /* Error Handling */
        }

        using (System.Data.SqlClient.SqlCommand SetCmd = new System.Data.SqlClient.SqlCommand())
        {
            SetCmd.Connection = Conn;
            SetCmd.CommandType = System.Data.CommandType.Text;

            string CurrentUserID = System.String.Empty;
            SetCmd.CommandText = "DECLARE @B VARBINARY(36); SET @B = CAST('" + CurrentUserID + "' AS VARBINARY(36)); SET CONTEXT_INFO @B";

            try
            {
                SetCmd.ExecuteNonQuery();
            }
            catch (System.Exception)
            {
                /* Error Handling */
            }
        }

        return Conn;
    }

I do not think that the application being a WPF one has any bearing on the issue I am having.

我认为该应用程序是 WPF 应用程序与我遇到的问题没有任何关系。

The issue I am having

我遇到的问题

Despite the SqlConnection being disposed along with the DataContext in Sql Server Management studio I can still see loads of open connections with :

尽管 SqlConnection 与 Sql Server Management Studio 中的 DataContext 一起处理，但我仍然可以看到大量打开的连接：

status : 'Sleeping' 
command : 'AWAITING COMMAND' 
last SQL Transact Command Batch : DECLARE @B VARBINARY(36); SET @B = CAST('GUID' AS VARBINARY(36)); SET CONTEXT_INFO @B

Eventually the connection pool gets used up and the application can't continue.

最终连接池用完，应用程序无法继续。

So I can only conclude that somehow running the SQLCommand to set the Context_Info is meaning that the connection doesn't get disposed of when the DataContext gets disposed.

所以我只能得出结论，以某种方式运行 SQLCommand 来设置 Context_Info 意味着当 DataContext 被释放时连接不会被释放。

Can anyone spot anything obvious that would be stopping the connections from being closed and disposed of when the DataContext they are used by are disposed?

任何人都可以发现任何明显的东西，当它们被使用的 DataContext 被处理时，会阻止连接被关闭和处理吗？

采纳答案by Marc Gravell

From MSDN(DataContext Constructor (IDbConnection)):

从MSDN( DataContext Constructor (IDbConnection)):

If you provide an open connection, the DataContext will not close it. Therefore, do not instantiate a DataContext with an open connection unless you have a good reason to do this.

如果您提供一个打开的连接，DataContext 将不会关闭它。因此，除非有充分的理由，否则不要使用打开的连接实例化 DataContext。

So basically, it looks like your connections are waiting for GC to finalize them before they will be released. If you have lots of code that does this, one approach might be to overide Dispose()in the data-context's partial class, and close the connection - just be sure to document that the data-context assumes ownership of the connection!

所以基本上，看起来你的连接在它们被释放之前正在等待 GC 完成它们。如果您有大量代码执行此操作，一种方法可能是Dispose()在数据上下文的部分类中覆盖，并关闭连接 - 只需确保记录数据上下文承担连接的所有权！

    protected override void Dispose(bool disposing)
    {
        if(disposing && this.Connection != null && this.Connection.State == ConnectionState.Open)
        {
            this.Connection.Close();
            this.Connection.Dispose();
        }
        base.Dispose(disposing);
    }

Personally, I would happily give it (regular data-context, w/o the hack above) an open connection as long as I was "using" the connection (allowing me to perform multiple operations) - i.e.

就个人而言，只要我“使用”连接（允许我执行多项操作），我就很乐意为其（常规数据上下文，没有上面的 hack）提供一个开放的连接 - 即

using(var conn = GetConnection())
{
   // snip: some stuff involving conn

   using(var ctx = new FooContext(conn))
   {
       // snip: some stuff involving ctx
   }

   // snip: some more stuff involving conn
}

回答by Robert S.

The Disposeshould close the connections, as MSDNpoints out:

本Dispose应关闭的连接，如MSDN指出：

If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose. Close and Dispose are functionally equivalent. If the connection pooling value Pooling is set to true or yes, the underlying connection is returned back to the connection pool. On the other hand, if Pooling is set to false or no, the underlying connection to the server is closed.

如果 SqlConnection 超出范围，它不会被关闭。因此，您必须通过调用 Close 或 Dispose 显式关闭连接。Close 和 Dispose 在功能上是等效的。如果连接池值 Pooling 设置为 true 或 yes，则底层连接返回到连接池。另一方面，如果 Pooling 设置为 false 或 no，则与服务器的底层连接将关闭。

My guess would be that your problem has something to do with GetContext().

我的猜测是您的问题与GetContext().

回答by Bradley Grainger

The SqlProviderused by the LINQ DataContextonly closes the SQL connection (through SqlConnectionManager.DisposeConnection) if it was the one to open it. If you give an already-open SqlConnectionobject to the DataContextconstructor, it will not close it for you. Thus, you should write:

在SqlProvider由LINQ使用DataContext仅关闭SQL连接（通过SqlConnectionManager.DisposeConnection），如果它是一个将其打开。如果你给构造函数一个已经打开的SqlConnection对象DataContext，它不会为你关闭它。因此，你应该写：

using (SqlConnection conn = GetConnection())
using (DataContext db = new DataContext(conn))
{
    ... Code 
}

回答by GeekyMonkey

I think the connection, while no longer referenced, is waiting for the GC to dispose of it fully.

我认为连接虽然不再被引用，但正在等待 GC 完全处理它。

Solution:

解决方案：

Create your own DataContext class which derives from the auto-generated one. (rename the base one so you don't have to change any other code).

创建您自己的 DataContext 类，该类派生自自动生成的类。（重命名基础代码，这样您就不必更改任何其他代码）。

In your derived DataContext - add a Dispose() function. In that - dispose the inner connection.

在派生的 DataContext 中 - 添加 Dispose() 函数。在那 - 处理内部连接。

回答by Ash

Well thanks for the help chaps, it has been solved now..

好吧，谢谢你的帮助，现在已经解决了..

Essentially I took elements of most of the answers above and implemented the DataContext constructor as above (I already had overloaded the constructors so it wasn't a big change).

从本质上讲，我采用了上述大多数答案的元素，并如上所述实现了 DataContext 构造函数（我已经重载了构造函数，所以这不是一个大的变化）。

// Variable for storing the connection passed to the constructor
private System.Data.SqlClient.SqlConnection _Connection;

public DataContext(System.Data.SqlClient.SqlConnection Connection) : base(Connection)
{
    // Only set the reference if the connection is Valid and Open during construction
    if (Connection != null)
    {
        if (Connection.State == System.Data.ConnectionState.Open)
        {
            _Connection = Connection;                    
        }
    }           
}

protected override void Dispose(bool disposing)
{        
    // Only try closing the connection if it was opened during construction    
    if (_Connection!= null)
    {
        _Connection.Close();
        _Connection.Dispose();
    }

    base.Dispose(disposing);
}

The reason for doing this rather than some of the suggestions above is that accessing this.Connectionin the dispose method throws a ObjectDisposedException.

这样做而不是上面的一些建议的原因是this.Connection在 dispose 方法中访问会抛出ObjectDisposedException。

And the above works as well as I was hoping!

上面的方法和我希望的一样好！

回答by Abhijeet Patel

I experienced the same issue using the Entity Framework. My ObjectContextwas wrapped around a usingblock.

我在使用实体框架时遇到了同样的问题。我的ObjectContext被包裹在一个using街区上。

A connection was established when I called SaveChanges(), but after the usingstatement was out of scope, I noticed that SQL Management Studio still had a "AWAITING COMMAND"for the .NET SQL Client. It looks like this has to do with the behavior of the ADO.NET provider which has connection pooling turned on by default.

调用时建立了连接SaveChanges()，但是在using语句超出范围后，我注意到 SQL Management Studio 仍然有一个"AWAITING COMMAND"用于 .NET SQL 客户端。看起来这与默认情况下打开连接池的 ADO.NET 提供程序的行为有关。

From "Using Connection Pooling with SQL Server" on MSDN(emphasis mine):

来自MSDN上的“使用 SQL Server 连接池” （重点是我的）：

Connection pooling reduces the number of times that new connections need to be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Openon a connection, the pooler looks to see if there is an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Closeon the connection, the pooler returns it to the pooled set of active connections instead of actually closing it. Once the connection is returned to the pool, it is ready to be reused on the next Opencall.

连接池减少了需要打开新连接的次数。池化器维护物理连接的所有权。它通过为每个给定的连接配置保持一组活动连接来管理连接。每当用户调用Open连接时，池化器都会查看池中是否有可用连接。如果池连接可用，它会将其返回给调用者，而不是打开一个新连接。当应用程序调用Close连接时，池化器将它返回到活动连接的池中，而不是实际关闭它。一旦连接返回到池中，它就可以在下一次Open调用中重用。

Also ClearAllPoolsand ClearPoolseems useful to explicitly close all pooled connections if needed.

此外ClearAllPools，ClearPool如果需要，显式关闭所有池连接似乎很有用。