.net SqlConnection即使在使用{}时也不会关闭 [英] .net SqlConnection not being closed even when within a using { }

问题描述

请帮助！

背景信息

我有一个WPF应用程序，可以访问SQL Server 2005数据库.数据库在应用程序运行所在的计算机上本地运行.

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.

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

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.

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

getConnection看起来像这样(我从函数中删除了"fluff"以使其更具可读性，但是没有其他功能缺失).

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

// 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;
    }

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

我遇到的问题

尽管在SQL Server Management Studio中将SqlConnection与DataContext一起处理，但我仍然可以看到带有:: p的开放连接

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

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.

所以我只能得出这样的结论:以某种方式运行SQLCommand来设置Context_Info意味着，在处理DataContext时，也不会处理连接.

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.

有人处置使用它们的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?

推荐答案

来自 MSDN (DataContext Constructor (IDbConnection)):

如果您提供开放连接，则 DataContext不会关闭它. 因此，不要实例化一个 具有开放连接的DataContext 除非你有充分的理由去做 这个.

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.

因此，基本上来说，您的连接似乎正在等待GC最终确定它们，然后再将它们释放.如果您有很多执行此操作的代码，则一种方法可能是在数据上下文的部分类中覆盖Dispose()并关闭连接-只需确保记录数据上下文假定该连接拥有所有权即可！

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!

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

就我个人而言，只要我使用"连接(允许我执行多项操作)即可，我会很乐意为它提供一个开放的连接(即上面的hack)，即

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.

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
}

这篇关于.net SqlConnection即使在使用{}时也不会关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！