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?

This behavior is normal and is due to connection pooling. To close the connection immediately, you would have to disable connection pooling in your connection string, but this will hurt performance. — Michael Liu, Mar 03 '14 at 16:52
Adding to Michael's comment, see this [thread](http://stackoverflow.com/questions/20203117/using-keyword-doesnt-close-an-open-sql-connection) — Andrei, Mar 03 '14 at 16:53
Your `Dispose` call is unnecessary: that's what `using` is encapsulating for you. — noelicus, Mar 03 '14 at 16:56
Well sort of - technically that depends on the implementation of the class (destructor), right? Whereas `using` is part of the language. — noelicus, Mar 03 '14 at 17:00
@noelicus For `SqlConnection` objects, *“Close and Dispose are functionally equivalent.”* ([source](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.close.aspx)) — poke, Mar 03 '14 at 17:05

score 8 · Accepted Answer · answered Mar 03 '14 at 16:54

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.

score 4 · Answer 2 · answered Mar 03 '14 at 16:58

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.

How can I actually close a sql connection

2 Answers2