Most people (including me) don’t regularly Dispose() their Command objects when doing data access with ADO.NET. The Connection and DataReader objects have Close() methods, and people are very much in the habit (or should be) of ensuring that either Close() or Dispose() or both are called on Connection and DataReader objects.

But Command objects do have a Dispose() method even though they don’t have a Close() method. Should they be disposed?

I posed this question to some of the guys on the ADO.NET team at Microsoft. After a few emails bounced around I got an answer: “Sometimes it is important”

It turns out that the reason Command objects have a Dispose method is because they inherit from Component, which implements IDisposable. The reason Command objects inherit from Component is so that they can be easily used in the IDE on designer surfaces.

However, it also turns out that some Command objects really do have non-managed resources that need to be disposed. Some don’t. How do you know which do and which don’t? You need to ask the dev that wrote the code.

It turns out that SqlCommand has no un-managed resources, which is why most of us have gotten away with this so far. However, OleDbCommand and OdbcCommand do have un-managed resources and must be disposed to be safe. I don’t know about OracleCommand – as that didn’t come up in the email discussions.

Of course that’s not a practical answer, so the short answer to this whole thing is that you should always Dispose() your Command objects just to be safe.

So, follow this basic pattern in VB.NET 2002/2003 (pseudo-code):

Dim cn As New Connection("…")

cn.Open()

Try

  Dim cm As Command = cn.CreateCommand()

  Try

    Dim dr As DataReader = cm.ExecuteReader()

    Try

      ' do data reading here

    Finally

      dr.Close() ' and/or Dispose() – though Close() and Dispose() both work

    End Try

  Finally

    cm.Dispose()

  End Try

Finally

  cn.Close() ' and/or Dispose() – though Close() and Dispose() both work

End Try

And in C# 1.0 and 2.0 (pseudo-code):

using(Connection cn = new Connection("…"))

{

  cn.Open()

  using(Command cm = cn.CreateCommand())

  {

    using(DataReader dr = cm.ExecuteReader())

    {

      // do data reading here

    }

  }

}

And in VB 8 (VB.NET 2005) (pseudo-code):

Using cn As New Connection("…")

  cn.Open()

  Using cm As Command = cn.CreateCommand()

    Using dr As DataReader = cm.ExecuteReader()

      ' do data reading here

    End Using

  End Using

End Using