Enterprise library database question

[AutoTrackerPlus]

I'm using the EL 2.0 library. I have a datalayer where I am debating doing 2
things... I'm concerned that because I am using a IDataReader, my connection
to the database is never getting closed and I have to rely on garbage
collection to close the connections.

I came across an article that said if I do a .Dispose() on the DbCommand
object, the connection will be closed... but I don't think that is true,
because the IDataReader that I am passing back from my data layer to my
Business Rules layer would fail if the connection was closed.

In my first example (EXAMPLE 1), I return an IDataReader and do the .Dispose
on my DbCommand object
EXAMPLE 1 QUESTION - is that ok?

In my second example (EXAMPLE 2), I return an object that I created instead
of the IDataReader and I close the IDataReader right away

EXAMPLE 2 QUESTION - is that a better idea? I don't like having my Core
object layer involved in my data layer, but I think I might have to do it
this way.
EXAMPLE 2 QUESTION - other than closing the IDataReader, do I have to
close/dispose anything else? I believe I read somewhere that when the
IDataReader is closed, the connection is closed. Do I need to do anything
with the connection, or the DbCommand object?



EXAMPLE 1
#region SelectVehicleSoldByDealership
public IDataReader SelectVehicleSoldByDealership(long passedIn_DS_NUMBER,
string passedIn_VEH_STATUS)
{
IDataReader iDataReader = null;

try
{
DbCommand dbCommand = _commonDatabase.GetStoredProcCommand
("VEHICLE_SELECT_SOLD_BY_DEALER");

//Parameters
_commonDatabase.AddInParameter(dbCommand, "DS_NUMBER", DbType.Int32,
passedIn_DS_NUMBER);
_commonDatabase.AddInParameter(dbCommand, "VEH_STATUS", DbType.String,
passedIn_VEH_STATUS);
iDataReader = _commonDatabase.ExecuteReader(dbCommand);
}
catch (Exception ex)
{
errorHelper = new ErrorHelper();
string errorMessage = errorHelper.FormatError(ex);
ExceptionPolicy.HandleException(new Exception(string.Format("Error: {0}",
errorMessage)), "Log And Email");

iDataReader = null;
}
finally
{
if (dbCommand != null)
dbCommand .Dispose();
}

return iDataReader ;
}
#endregion SelectVehicleSoldByDealership


EXAMPLE 2
#region SelectVehicleSoldByDealership
public List<VehicleRecord> SelectVehicleSoldByDealership(long
passedIn_DS_NUMBER,
string passedIn_VEH_STATUS)
{
IDataReader iDataReader = null;
List<VehicleRecord> vehicleRecordList = new List<VehicleRecord>();
VehicleRecord vehicleRecord = null;

try
{
DbCommand dbCommand = _commonDatabase.GetStoredProcCommand
("VEHICLE_SELECT_SOLD_BY_DEALER");

//Parameters
_commonDatabase.AddInParameter(dbCommand, "DS_NUMBER", DbType.Int32,
passedIn_DS_NUMBER);
_commonDatabase.AddInParameter(dbCommand, "VEH_STATUS", DbType.String,
passedIn_VEH_STATUS);
iDataReader = _commonDatabase.ExecuteReader(dbCommand);

while (iDataReader.Read())
{
vehicleRecord = new VehicleRecord();

vehicleRecord.StockNumber = iDataReader["VEH_STOCK_NUMBER"].ToString();
vehicleRecord.Year = Convert.ToInt32(iDataReader["VEH_YEAR"]);
vehicleRecord.Make = iDataReader["VEH_MAKE"].ToString();
vehicleRecord.Model = iDataReader["VEH_MODEL"].ToString();
vehicleRecordList.Add(vehicleRecord);
}

}
catch (Exception ex)
{
errorHelper = new ErrorHelper();
string errorMessage = errorHelper.FormatError(ex);
ExceptionPolicy.HandleException(new Exception(string.Format("Error: {0}",
errorMessage)), "Log And Email");

vehicleRecordList = null;
}
finally
{
if (iDataReader != null)
iDataReader.Close();
}

return vehicleRecordList;
}
#endregion SelectVehicleSoldByDealership

 

[Marc Gravell]

There is an overload of ExecuteReader that accepts a CommandBehavior;
one of the options here is CloseConnection, which when specified means
that closing the reader (not the command) will also close the
connection, which makes sense if you only want the connection while
you are reading data. I imagine (I haven't checked) that the
enterprise library uses this option, since IIRC it masks the
connection from you.

Re intermingling the core objects with your data-layer... isn't that
kinda the job of the (project specific) data-layer? As for a non-
project specific layer... enterprise library of ADO.NET already fills
that hole. If you wanted to abstract this, you could do something
involving a delegate of some kind (Action<IDataReader>, perhaps) - but
I haven't thought about this enough to see if it is a good idea ;-p
However, this kind of "disconnected by the time you leave the method"
approach does have the advantage that you can Close()/Dispose()
everything at source.

Marc