Why doesn't the number of connections decrease after calling dispose on a connection?

Question

When I run this code, the number of connections to my DB increases to 3 after connection.Open(). Why does it not go back down to 2 after connection.Close()?

private static void AdoNetStuff()
{
    var connection = new SqlConnection(@"Initial Catalog=abook;server=.\SqlExpress;Integrated Security=true");
    var sqlCommand = new SqlCommand("SELECT TOP 10 * FROM dbo.Entity", connection);

    connection.Open();

    sqlCommand.ExecuteReader();

    sqlCommand.Dispose();
    connection.Dispose();
    connection.Close();
}

The above code is in a console app, and the number of connections doesn't drop back down to 2 until my console app closes.

This is how I'm checking the number of connections.

SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections, loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0 AND DB_NAME(dbid) = 'abook'
GROUP BY dbid, loginame

I can wait a while before ending the console app, and I still have 3 connections. As soon as I end the app, the number of connections drop back down to 2.

Answer 1

SQL server uses connection pools for performance reasons. That means your connections will stay active even through you have called dispose.

Try calling: SQLConnection.ClearAllPools();

To clear just the current connection try calling:

SqlConnection.ClearPool(ObjSqlConnection);