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

Question

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.

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 
}

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

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

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 :

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.

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?

Answer 1

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

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

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
}