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

Answer 2

The Dispose should close the connections, as MSDN points out:

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.

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

Answer 3

The SqlProvider used by the LINQ DataContext only closes the SQL connection (through SqlConnectionManager.DisposeConnection) if it was the one to open it. If you give an already-open SqlConnection object to the DataContext constructor, it will not close it for you. Thus, you should write:

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

Answer 4

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

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

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

Answer 5

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

// 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.Connection in the dispose method throws a ObjectDisposedException.

And the above works as well as I was hoping!

Answer 6

I experienced the same issue using the Entity Framework. My ObjectContext was wrapped around a using block. A connection was established when I called SaveChanges() ,but after the using statement 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.

From MSDN 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 Open on 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 Close on 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 Open call.

Also ClearAllPools and ClearPool seems useful to explicitly close all pooled connections if needed.