C# + Disposing DbConnection and DbCommand and catching error

Question

Hi,

I am trying to understand DbConnection and DbCommand, and the proper way to dispose those objects after use.

Following is the code snippet I have. By using "using statement" on DbConnection and DbCommand, would it be sufficient? I am trying to prevent possible memory leak.

2nd question,

Do I have to Dispose DbCommand object?

thanks a lot

DbProviderFactory fac = DbProviderFactories.GetFactory(this.DatabaseProviderName);

using (DbConnection dbConn = fac.CreateConnection())
{
     dbConn.ConnectionString = this.ConnectionString;

     using (DbCommand comm = fac.CreateCommand())
     {
          comm.CommandText = "select * from aTable";
          comm.Connection = dbConn;
          DataTable targetTable = new DataTable();

          DbDataAdapter facDA = fac.CreateDataAdapter();
          facDA.SelectCommand = comm;
          facDA.Fill(targetTable);

          //assuming Adapter would open / close connection (right assumption?)

          //do something with the datatable
     }
}

Answer 1

using using is the same as a try/finally block with dispose() called in finally.

DbCommand should be wrapped in a using statement as it implements IDisposable.

Note that DbCommand is actually an abstract class so you will need to either

• derive from it
• code to an interface (IDbCommand)
• use one of the predefined derived classes such as SqlCommand.

DbConnection is also an abstract class so you will need to do something similar as I have suggested above for DbCommand for this too.

The general recommendation is that if an object implements IDisposable, it should be wrapped in a using statement such that Dispose() is called to free resources, even if an Exception is thrown within in the statement block. In your example then, a good practice would be to wrap each of the connection, command, DataTable and DbDataAdapter objects in a using statement.

Answer 2

Yes I would call Dispose on the DbCommand object. In general, the rule should be that if it implements IDisposable, you should call its Dispose method when appropriate. Your code looks well formed to me. I think you are on the right track.

EDIT Actually, you might want to also wrap your DbDataAdapter in a using statement since it too implements IDisposable.

Answer 3

Wrapping the objects in using blocks as you do should be sufficient; this will make the code call Dispose even if there is an exception thrown.

And yes, you should do that for the DbCommand object, as well as the DbDataAdapter and the DataTable. They all (directly or indirectly) implement IDisposable.