02 June 2004

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.

<?xml:namespace prefix = o ns = “urn:schemas-microsoft-com:office:office” /> 

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