When does SqlCommand close when used in another function?

Question

I have a program in C# where I created various class, and all of them need to make calls to the database. So I decided to create a static class that handles all the calls to allow me to apply far-reaching changes very easily. Within that class I have functions that call the various parts of SqlCommand (ExecuteReader(), etc) I finally got tired of rewriting all the connection code, and abstracted it out to another function called getSqlCommand() which just returns an initialized SqlCommand which I use to perform the various commands. I've chosen to not pass the SqlCommand back to the other programs (though it is a public method in case I need it) because there is some pre-processing if you will that I may do before I had the results back to the program.

My question is, if/when the SqlConnection closes? Since it is being passed back as a return value in a SqlCommand, will it be left open? and if so, can I close it? Here is some code (slightly stripped down) so you can see what I am doing, thanks in advance!

The function to create the SqlCommand:

public static SqlCommand GetSqlCommand(string query)
{
    using (SqlConnection dbConnection = new SqlConnection( SQLConn )) {
        dbConnection.Open();
        SqlCommand cmd = new SqlCommand( query, dbConnection );
        cmd.CommandTimeout = 0;
        return cmd;
    }
}

An example of the usage of the command in the static class:

public static SqlDataReader executeReader( string query )
{
    try {
       return GetSqlCommand(query).ExecuteReader();
    } catch (SqlException) {
       //Notify User and Resolve Error
    }

    return null;
}

Does this not error? I would have expected it error since `SqlConnection.Dispose` would normally do the closing. Perhaps .NETs caching of the connection is saving you (.NET does cache connections). Command should also be disposed of when you are done with it. — TyCobb, Dec 03 '14 at 17:36
Your connetion is automatically closed when it exits the `using` block in your code snippet, rendering the `SqlConnection` that's returned in a semi-useless state. — Michael, Dec 03 '14 at 17:36

score 5 · Accepted Answer · answered Dec 03 '14 at 17:37

A SqlConnection is closed when you call Dispose. Exiting the using block does that. The connection will be closed.

That's all there is to it. It will not magically stay open because ADO.NET does not know and cannot find out that you returned the connection object.

Move the connection out of the GetSqlCommand method. Pass the connection object into that method. GetSqlCommand has no business in creating a connection. It should take a connection, use it, but not close it.

score 1 · Answer 2 · answered Dec 03 '14 at 17:37

The connection closes before the code exits the GetSqlCommand method. That means that the command object that it returns is useless.

The using construct creates a try ... finally block, where it disposes the connection at the end of the block. As disposing the connection also closes it, the connection is already closed before the command object is returned from the method to the caller.

score 1 · Answer 3 · answered Dec 03 '14 at 17:57

The using keyword is equivalent to use try finally, and within the finally block your connexion will be disposed

SqlConnection dbConnection = new SqlConnection( SQLConn );
    try
    { 
        dbConnection.Open();
        SqlCommand cmd = new SqlCommand( query, dbConnection );
        cmd.CommandTimeout = 0;
        return cmd;
    }
    catch(Exception ex)
    {

    }
    finally
    {
        dbConnection.Dispose();
    }

And even if there is a return statement inside a try block , the block finally will always be executed

When does SqlCommand close when used in another function?

3 Answers3