How can I actually close a sql connection

Question

It might be my misunderstanding but I do have big problem. Please consider the following code:

static void Main(string[] args)
{
    using (SqlConnection con = new SqlConnection(connectionString))
    {
        con.Open();
        int i = 10;
        con.Close();
        con.Dispose();
    }
    int y = 10;
}

At line int y = 10, place a break point.

Now, go to your SQL Server and right click on SQl Connection which is the same connection as connectionString in above code, and choose "Activity Monitor". Run the above code and check the SQL Server Activity monitor. When con.Open() is executed, the activity monitor shows that the connection has been made. So far, so good! But when the cursor hits the line which indicates int y = 10;, the Activity Monitor still shows you the connection which should not be there! Because I closed it, I disposed it and even I passed the using statement as well.

Does anyone know how I can close the connection?

Answer 1

This is the connection pool, correctly doing its job. This is a good thing, in virtually all circumstances. If you don't want this: disable the connection pool via the connection string.

Basically, you need to keep separate 2 concepts:

• the SqlConnection managed connection instance
• the underlying unmanaged connection object

In normal usage, closing or disposing the managed connection just puts the unmanaged connection back in the pool, ready for quick use. If you want the managed and unmanaged connection to die together, you must disable connection pooling.

Answer 2

Database connections in .NET are pooled by default. That means that although a single instance of a connection (SqlConnection) is closed, the framework will actually keep the underlying real connection to allow new connections to be created faster. Unless you have good reasons, you probably don’t want to mess around with this, as it does give you a good performance boost.

That being said, you actually can force the pool to close all its connection it still has. You can do that with SqlConnection.ClearPool, or—by using a even bigger hammer—with SqlConnection.ClearAllPools. With that, the real connections are closed as well, and your activity monitor won’t show the connections any more.