"using" keyword doesn't close an open SQL connection

Question

I'm referring to a post that was put on Stack Overflow a long, long time ago. Does End Using close an open SQL Connection

I have a problem however. I found that using does not close the connection at all on SQL 2012 Express edition as well as SQL 2008 Developer Edition.

Here is the code that I've used. The code will go through each Database and look for a specific table specified, however, when it is done, and you run an sp_who on the server, all the connections are still there. The status is sleeping and the cmd is "AWAITING COMMAND" but when you try to create a database for instance, model cannot be locked, because you still have a connection open to it. Is this a bug in the class?

using (SqlConnection conn = new SqlConnection("Data Source=" + ServerNameCombo.Text + ";Initial Catalog=master;Persist Security Info=True;User ID=" + UserNameEdit.Text + ";Password=" + PasswordEdit.Text))
{
    using (SqlCommand dbs = new SqlCommand("Select name from sysdatabases", conn))
    {
        conn.Open();
        using (SqlDataReader reader = dbs.ExecuteReader())
        {
            while (reader.Read())
            {
                using (SqlConnection dbconn = new SqlConnection("Data Source=" + ServerNameCombo.Text + ";Initial Catalog=" + reader["name"].ToString() + ";Persist Security Info=True;User ID=" + UserNameEdit.Text + ";Password=" + PasswordEdit.Text))
                {
                    using (SqlCommand dbscmd = new SqlCommand("Select name from sysobjects where name = '" + TableName + "'", dbconn))
                    {
                        dbconn.Open();
                        if (dbscmd.ExecuteScalar() != null)
                        {
                            DBNames += (DBNames != "" ? "," : "") + reader["name"].ToString();
                        }
                    }
                }
            }
        }
    }
}

It's the connection pooling keeping it in that state. Look into that. You can actually disable it in the conn string. — Marcel N., Nov 25 '13 at 20:57
You can coalesce `using`-blocks by removing the `{ }` around another `using`-block, might save some indentation... — Mark Rotteveel, Nov 25 '13 at 21:06

score 14 · Accepted Answer · edited Nov 25 '13 at 20:59

14

This is expected behaviour; it closes the managed connection, which means it releases the underlying connection the connection pool. This keeps the connection artificially open so that the next managed connection for the same connection-string and identity can use the existing connection (setting the reset bit in the TDS pipeline) to avoid connection spin-up latency.

If you don't want this: disable the connection pool in the connection string (Pooling=false).

edited Nov 25 '13 at 20:59

Brian

5,069
7
37
47

answered Nov 25 '13 at 20:57

Marc Gravell

1,026,079
266
2,566
2,900

The funny thing is, the OP should be happy with connection pooling as he is opening and closing the same connection over and over again in a loop. – Mark Rotteveel Nov 25 '13 at 21:08
@MarkRotteveel That is true, but I don't really use that same connection because the DB that I connect to change. I found that the physical connection was not there, and that is why it was funny that the connection was still server-side. – Jaques Nov 25 '13 at 21:16

"using" keyword doesn't close an open SQL connection

1 Answers1

Linked