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<VehicleRecordSelectVehicleSoldByDealership(lo ng
passedIn_DS_NUMBER,
string passedIn_VEH_STATUS)
{
IDataReader iDataReader = null;
List<VehicleRecordvehicleRecordList = 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