Why is SqlConnection not used / closed?

Question

Given the method:

internal static DataSet SelectDataSet(String commandText, DataBaseEnum dataBase) { var dataset = new DataSet(); SqlConnection sqlc = dataBase == DataBaseEnum.ZipCodeDb ? new SqlConnection(ConfigurationManager.AppSettings["ZipcodeDB"]) : new SqlConnection(ConfigurationManager.AppSettings["WeatherDB"]); SqlCommand sqlcmd = sqlc.CreateCommand(); sqlcmd.CommandText = commandText; var adapter = new SqlDataAdapter(sqlcmd.CommandText, sqlc); adapter.Fill(dataset); return dataset; } 

Why is sqlc (SqlConnection) not being placed / closed after the calling method goes out of scope, or sqlc has no more references?

EDIT 1: Even wrapping it in use, I still see the connection using (my connection pool is disabled):

 SELECT DB_NAME(dbid) as 'Database Name', COUNT(dbid) as 'Total Connections' FROM sys.sysprocesses WITH (nolock) WHERE dbid > 0 GROUP BY dbid 

EDIT 2: Have some more debugging with the help I got from here - the answer was the one who hard-coded the connection string to the pool. Thanks for all the help - if I could, I would mark all the answers as answers.

Answer 1

C # garbage collection is not deterministic, but the language does provide a deterministic structure for disposing of resources as follows:

 using (SqlConnection connection = new SqlConnection(...)) { // ... } 

This will create a try/finally block, which ensures that the connection object is deleted no matter what happens in the method. You really need to wrap any type instances that implement IDisposable in a usage block like this, as this will provide responsible management of resources (unmanaged resources such as database connections) and also give you the deterministic control that you are looking for.

Answer 2

Because C # is a garbage collector, and garbage collection is not deterministic. The fact is that your sqlconnection is located. You simply cannot choose when.

Sql links are a limited resource, and you can easily create enough of them to exhaust. Instead, write:

 internal static DataSet SelectDataSet(String commandText, DataBaseEnum dataBase) { var dataset = new DataSet(); using (SqlConnection sqlc = dataBase == DataBaseEnum.ZipCodeDb ? new SqlConnection(ConfigurationManager.AppSettings["ZipcodeDB"]) : new SqlConnection(ConfigurationManager.AppSettings["WeatherDB"])) using (SqlCommand sqlcmd = sqlc.CreateCommand()) { sqlcmd.CommandText = commandText; var adapter = new SqlDataAdapter(sqlcmd.CommandText, sqlc); adapter.Fill(dataset); } return dataset; } 

Although in this case, it can do for you, because the .Fill() method is a strange beast:

If IDbConnection is closed before calling Fill, it opens to retrieve data and then closes.

Thus, this means that the data adapter must take care of it for you if you start with a closed connection. It bothers me more that you pass the sql command as a simple string. From time to time, your queries should have user parameters, and this means that you combine this data directly into the command line. Do not do this!! Use SqlCommand Parameters instead.

Answer 3

This will be after garbage collection works. The same goes for opening a file stream for recording without closing it. It can be "blocked" even if the codes are out of scope.

Answer 4

I believe this is due to the SqlConnection pool. What you can do, and we often at work, wrap the entire call in a using statement, which calls its call with the dispose () method, which closes the connection and removes the object

Then you could do something like this:

 internal static DataSet SelectDataSet(String commandText, DataBaseEnum dataBase) { var dataset = new DataSet(); using(SqlConnection sqlc = dataBase == DataBaseEnum.ZipCodeDb ? new SqlConnection(ConfigurationManager.AppSettings["ZipcodeDB"]) : new SqlConnection(ConfigurationManager.AppSettings["WeatherDB"])) { SqlCommand sqlcmd = sqlc.CreateCommand(); sqlcmd.CommandText = commandText; var adapter = new SqlDataAdapter(sqlcmd.CommandText, sqlc); adapter.Fill(dataset); return dataset; } } 

Answer 5

I agree with all the answers here, plus the connection can be wider than just a method. When you need to use an existing connection in different places, the script will change a little. Be sure to call Dispose on all objects that implement IDisposable after you finish with them. This is good practice, so you are not getting unused objects that the garbage collector cannot decide what to do with them.