"using" keyword does not close open SQL connection

Question

I mean a post that was added to Qaru a long time ago. Does closing an open SQL connection end

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

Here is the code I used. The code will go through each database and look for a specific table, however, when this is done, and you run sp_who on the server, all connections still exist. The status is dormant and cmd is "AWAITING COMMAND", but when you try to create a database, for example, the model cannot be locked because you still have access to it. Is this a class error?

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

Answer 1

This is the expected behavior; it closes the managed connection, which means that it releases the underlying connection of the connection pool . This allows you to artificially open the connection, so that the next managed connection for the same connection string and identifier can use the existing connection (setting the reset bit in the TDS pipeline) to avoid connection delay.

If you do not want this: disable the connection pool in the connection string ( Pooling=false ).