Disposing SqlCommand

Question

Because SqlCommand implements IDisposable, I would normally approach an ADO query as follows.

using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(query, connection))
{
    // Execute command, etc. here
}

However, what if I need to execute multiple commands during a single connection? Do I really need a new using block for each command?

The examples I found from Microsoft don't use a using block for SqlCommands (or even call Dispose()). What are best practices regarding disposing SqlCommand?

Answer 1

Sure, best practice is to dispose them.

using (SqlConnection connection = new SqlConnection(connectionString))
{
   connection.Open();

   using (SqlCommand command1 = new SqlCommand(query1, connection))
   {
      // Execute command, etc. here
   }

   using (SqlCommand command2 = new SqlCommand(query2, connection))
   {
      // Execute command, etc. here
   }

   using (SqlCommand command3 = new SqlCommand(query3, connection))
   {
      // Execute command, etc. here
   }
}

MSDN probably doesn't show it because it's not really needed in case of SqlCommand. But in my opinion it's bad from microsoft to not use this pattern on every object that implements IDdisosable because people aren't getting used to it.

Answer 2

The using statement ensures that Dispose is called even if an exception occurs while you are calling methods on the object. You can achieve the same result by putting the object inside a try block and then calling Dispose in a finally block.

In this case you use using for each command block:

using (SqlConnection connection = new SqlConnection(connectionString))
{
   using (SqlCommand cmd1= new SqlCommand(query1, connection))
   {
   }

   using (SqlCommand cmd2= new SqlCommand(query2, connection))
   {
   }  
}

Answer 3

No you do not. There are 2 ways that your multiple commands could be bundled within one connection and command.

The first would be to just reuse the existing CMD object, but changing the CommandText and other CMD.properties as needed.

using (SqlConnection con = new SqlConnection(connectionString)) {

    con.Open();

    using (SqlCommand cmd = new SqlCommand(query1, con)) {
        // cmd.CommandType = CommandType.xxxxx
        // add any parameters
        // Execute()

        cmd.CommandText = query2;
        // reset CommandType if needed
        // adjust parameters if needed
        // Execute()

        cmd.CommandText = query 3;
        // reset CommandType if needed
        // adjust parameters if needed
        // Execute()
    }
    con.Close();
}

The second method would be to create a Stored Procedure on the database server and call that in the one CMD object

-- Database
CREATE PROCEDURE schema.sproc_CommandBatch (
    -- any variables here
) AS
BEGIN
    -- query 1

    -- query 2

    -- query 3
END
GO


// C#
using (SqlConnection con = new SqlConnection(connectionString)) {

    con.Open();

    using (SqlCommand cmd = new SqlCommand("schema.sproc_CommandBatch", con)) {
        // cmd.CommandType = CommandType.StoredProcedure
        // add any parameters
        // Execute()
    }
    con.Close();
}

Answer 4

Best practice is that if it implements IDisposable then Dispose() it. In fact the examples here do all call Dispose. With a wrinkle:

The first example, where it opens a SqlDataReader, calls .Close() on the command in the finally clause. In fact, .Close is just a wrapper around .Dispose() (and no longer appears in the documentation). Jonathan Wood in his comment pointed out that the Close is on the reader. This is wrong.

For multiple calls on the same connection you can:

1. Re-use the one command (declared in one using). I don't like this myself, but that's just me: I find it...ugly.
2. Use multiple commands in multiple usings. That's what I'd do.

'Best Practice' is a bit amorphous. There's never unanimous agreement that what person A touts as Best Practice is better than what person B is recommending, let alone C, D or E. Use your own judgement.