"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();
                        }
                    }
                }
            }
        }
    }
}

Recommendation

when are you expecting this to have the connection closed? – Notum

It's the connection pooling keeping it in that state. Look into that. You can actually disable it in the conn string. – Guffey

You can coalesce using-blocks by removing the { } around another using-block, might save some indentation... – Knowhow

Answer 1

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).

Sideband

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. – Knowhow

@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. – Recommendation