Do we need to close DbCommand in enterprise library 4.1?

Question

My function looks like the below. My question is: do we need to close cmd in this function. My understand is enterprise library automatically closes connection after using.

    const String STORED_PROCEDURE_NAME = "LTR_GetLicenseDetails";
    const string DB_CONNECTION_NAME = "LTRDB";
    MSPData.Database db;
    DbCommand cmd = null;

    db = CreateDatabase(DB_CONNECTION_NAME);
    DataSet ds;

    try
    {
        //Need to close cmd in enterprise libra
        cmd = db.GetStoredProcCommand(STORED_PROCEDURE_NAME);

        ds = ExecuteDataSet(db, cmd);
    }
    catch (Exception ex)
    {
        thrown ex;
    }

Answer 1

Database.GetStoredProcCommand returns an IDbCommand which implements IDisposable. In general, if an object implements IDisposable then it should be disposed when the object is no longer needed.

In fact if you call Enterprise Library methods that don't take a command they will construct a command and dispose it. e.g.

public virtual DataSet ExecuteDataSet(string storedProcedureName,
                                        params object[] parameterValues)
{
    using (DbCommand command = GetStoredProcCommand(storedProcedureName, parameterValues))
    {
        return ExecuteDataSet(command);
    }
}

Your understanding of Enterprise Library is correct -- the connection will be closed when calling one of the Database.Execute* methods (e.g. ExecuteDataSet). So not disposing the command object will not result in orphaned database connections. However, it may result in some other type of leak depending on the database provider.

In terms of SQL Server, the last time I looked at SqlCommand there was no obvious harm done if the object is not disposed and the only issue I recall seeing was the parameter cache would have to wait until garbage collection to be released.