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.