Disposing of SqlConnection before Disposing of SqlCommand

Question

I have a method like this:

void Execute(SqlCommand cmd)
{
    try
    {
        using(SqlConnection conn = new SqlConnection(...))
        {
            conn.Open();
            cmd.Connection = conn;
            // ...
        }
    }
    finally
    {
        cmd.Connection = null;
    }
}

It's used like this:

using(SqlCommand cmd = new SqlCommand(...))
{
    Execute(cmd);
}

Is there anything behaviorally wrong with this? I know it's more usual to create the SqlConnection first, then the SqlCommand, but since SqlCommand is in reality more-or-less a plain old object I think it should be fine in practice.

Answer 1

I highly recommend to rethink about refactoring to find better solution. But in your scenario

You can safety dispose connection before disposing command.

They are not related to each other and you can safety remove connection from a command object or even set it to another connection.

The only important thing is command must have an open connection before execute.

Removing connection before finally section make disposing of connection faster.

I recomend to rewrite your code to something like this:

void Execute(SqlCommand cmd)
{
    try
    {
        using (SqlConnection conn = new SqlConnection(""))
        {
            conn.Open();
            cmd.Connection = conn;

            // Execute command and get data

            // It is better to remove connection from command here
            cmd.Connection = null;
        }
    }
    catch
    {
       // Remove connection if there is error
        cmd.Connection = null;
    }
}