Is SqlCommand.Dispose enough?

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?

Answer 1

@huseyint : are you sure of this? There is a connection pool to help exactly this issue but this does not address my problem..

Answer 2

@lassevk : This is not necessarily true. A closed connection can be reopened. A disposed connection cannot be reopened.

Answer 3

No, Disposing of the Command will not effect the Connection. A better approach would be to also wrap the SqlCommand 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.

Answer 4

Yes, I've often seen the pattern of nesting two using blocks (the outer for the connection and the inner for the command; or even 3 having the inner-most for the SqlDataReader) but I thought things can be compressed a bit.

Maybe they can't.

Answer 5

After some thought I have come to the conclusion that a compression of code can still be made like this :

using(SqlConnection con = new SqlConnection("connection string"), SqlCommand cmd = new SqlCommand ("GetSomething", con))
{
  con.Open();
  cmd.ExecuteNonQuery();
}

Answer 6

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