Does SqlCommand.Dispose close the connection?

Question

Can I use this approach efficiently?

using(SqlCommand cmd = new SqlCommand("GetSomething", new SqlConnection(Config.ConnectionString))
{
    cmd.Connection.Open();
    // set up parameters and CommandType to StoredProcedure etc. etc.
    cmd.ExecuteNonQuery();
}

My concern is : Will the Dispose method of the SqlCommand (which is called when exiting the using block) close the underlying SqlConnection object or not?

Statius

After disposing the SqlCommand com, its Connection instance will be unrooted (not used by anything). So Once the GarbageCollector finalizes the SqlConnection instance, Won't the connection be disposed? I think it will be, because connection is referenced by only cmd here. – Innoxious

Well in that case, @Mecek, the memory will be freed harder since it will go through finalization which will promote it from gen0 to gen1, I guess. – Cheltenham

Answer 1

No, Disposing of the SqlCommand will not effect the Connection. A better approach would be to also wrap the SqlConnection in a using block as well:

using (SqlConnection conn = new SqlConnection(connstring))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand(cmdstring, conn))
    {
        cmd.ExecuteNonQuery();
    }
}

Otherwise, the Connection is unchanged by the fact that a Command that was using it was disposed (maybe that is what you want?). But keep in mind, that a Connection should be disposed of as well, and likely more important to dispose of than a command.

EDIT:

I just tested this:

SqlConnection conn = new SqlConnection(connstring);
conn.Open();

using (SqlCommand cmd = new SqlCommand("select field from table where fieldid = 1", conn))
{
    Console.WriteLine(cmd.ExecuteScalar().ToString());
}

using (SqlCommand cmd = new SqlCommand("select field from table where fieldid = 2", conn))
{
    Console.WriteLine(cmd.ExecuteScalar().ToString());
}

conn.Dispose();  

The first command was disposed when the using block was exited. The connection was still open and good for the second command.

So, disposing of the command definitely does not dispose of the connection it was using.

Truffle

You don't need to open the connection before creating the command right? Make it a little cleaner like this: using (SqlConnection conn = new SqlConnection(connstring)) using (SqlCommand cmd = new SqlCommand(cmdstring, conn)) { conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); } – Ixion

I guess the formatting doesn't stick... but basically lump the to using statements together and get rid of a level of nesting. (If you want) – Ixion

The point of the example is not to show a simpler syntax, but to demonstrate that both SqlCommands can be used with the same connection and then disposed without disposing of the connection. The connection needs to be opened and then used twice in order to demonstrate this (see original question). – Truffle

what about the other way round? does closing of the connection also dispose commands within its using scope? my guess is not? – Markova

Answer 2

SqlCommand.Dispose will not be sufficient because many SqlCommand(s) can (re)use the same SqlConnection. Center your focus on the SqlConnection.

Doorn

Answer 3

Soooo many places get this wrong, even MS' own documentation. Just remember - in DB world, almost everything is backed by an unmanaged resource, so almost everything implements IDisposable. Assume a class does unless the compiler tells you otherwise. Wrap your command in a using. Wrap your connection in a using. Create your connection off a DbProvider (get that from DbProviderFactories.GetFactory), and your command off your connection so that if you change your underlying DB, you only need to change the call to DBPF.GetFactory. So your code should end up looking nice and symmetrical:

var provider = DbProviderFactories.GetFactory("System.Data.SqlClient");// Or MS.Data.SqlClient
using (var connection = provider.CreateConnection())
{
    connection.ConnectionString = "...";
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "...";
        connection.Open();

        using (var reader = command.ExecuteReader())
        {
...
        }
    }
}

Paleoclimatology

Answer 4

I use this pattern. I have this private method somewhere in my app:

private void DisposeCommand(SqlCommand cmd)
{
    try
    {
        if (cmd != null)
        {
            if (cmd.Connection != null)
            {
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }
            cmd.Dispose();
        }
    }
    catch { } //don't blow up
}

Then I always create SQL commands and connections in a try block (but without being wrapped in a using block) and always have a finally block as:

    finally
    {
        DisposeCommand(cmd);
    }

The connection object being a property of the command object makes a using block awkward in this situation - but this pattern gets the job done without cluttering up your code.

Bamberger

this assumes that you're only using your connection once for that single command, and therefore you will have to create a new connection and open it, everytime you need to execute a new command. There's a lot of overhead in creating and opening connections. – Attraction

In my eyes this is ugly code, and would be much cleaner to just use the using-statement that automatically takes care of disposing the command. – Barde

Downvoting - unnecessary and less effective reimplemenation of the 'using' method. – Aleen

It’s not only unnecessary code, but also error-prone. 'using' is a better way to bracket use of a resource with its eventual disposal. – Pagandom