"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.
– Marcel N., Commented Nov 25, 2013 at 20:57
You can coalesce using-blocks by removing the { } around another using-block, might save some indentation... — Mark Rotteveel
– Mark Rotteveel, Commented Nov 25, 2013 at 21:06

Brian · Accepted Answer · 2013-11-25 20:59:33Z

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, 2013 at 20:59

Brian

5,1377 gold badges41 silver badges52 bronze badges

answered Nov 25, 2013 at 20:57

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mark Rotteveel Over a year ago

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.

Jaques Over a year ago

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

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related