Is SqlCommand.Dispose() required if associated SqlConnection will be disposed?

Question

I usually use code like this:

using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConn"].ConnectionString))
{
   var command = connection.CreateCommand();
   command.CommandText = "...";
   connection.Open();
   command.ExecuteNonQuery();
}

Will my command automatically disposed? Or not and I have to wrap it into using block? Is it required to dispose SqlCommand?

Answer 1

Just do this:

using(var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConn"].ConnectionString))
using(var command = connection.CreateCommand())
{
   command.CommandText = "...";
   connection.Open();
   command.ExecuteNonQuery();
}

Not calling dispose on the command won't do anything too bad. However, calling Dispose on it will suppress the call to the finalizer, making calling dispose a performance enhancement.

Answer 2

The safest policy is to always call Dispose() on an object if it implements IDisposable, either explicitly or via a using block. There may be cases where it is not required but calling it anyway should never cause problems (if the class is written correctly). Also, you never know when an implementation may change meaning that where the call was previously not required it is now definitely required.

In the example you've given, you can add an extra inner using block for the command, as well as maintaining the outer using block for the connection.

Answer 3

Yes, you should, even if it the implementation is currently not doing much, you don't know how it is going to be changed in the future (newer framework versions for instance). In general, you should dispose all objects which implement IDisposable to be on the safe side.

However, if the operation is deferred and you don't control the full scope (for instance when working asynchroneously, or when returning an SqlDataReader or so), you can set the CommandBehavior to CloseConnection so that as soon as the reader is done, the connection is properly closed/disposed for you.

Answer 4

In practice, you can skip Dispose. It doesn't free any resources. It doesn't even suppress finalization since the SQLCommand constructor does that.

In theory, Microsoft could change the implementation to hold an unmanaged resource, but I would hope they'd come out with an API that gets rid of the Component base class long before they'd do that.

Answer 5

You can find out this kind of stuff using Reflector or dotPeek or https://referencesource.microsoft.com/.

I had a small dig (I would suggest that you dig yourself though to be fully sure of the rest of this though as I didn't try that hard) and it looks like when you kill a connection there is no disposal of any children associated with that connection. Furthermore it doesn't actually look like the disposal of a command actually does that much. It will set a field to null, detach itself from a container (this may prevent a managed memory leak) and raise an event (this might be important but I can't see who is listening to this event).

Either way it's good practice to use this stuff in a using block or to ensure you dispose of it using a dispose pattern in the object that holds the connection (if you intend to hold onto the command for a while).

Answer 6

In my opinion, calling Dispose for both SqlConnection and SqlCommand is good practice, use below code

using(var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConn"].ConnectionString))
try{
    using(var command = connection.CreateCommand())
    {
       command.CommandText = "...";
       connection.Open();
       command.ExecuteNonQuery();
    }
}
catch(Exception ex){ //Log exception according to your own way
    throw;
}
finally{
    command.Dispose();
    connection.Close();
    connection.Dispose();
}