/// <summary>
/// This method executes a query against the database and returns the results as a
/// <see cref="DataTransferObject"/>. The primary purpose of this method is to retrieve
/// a single entity (record) from the database (master) that may have associated child
/// records (details).
/// </summary>
/// <param name="helper">The <see cref="DALCHelper"/> class that contains the database
/// command information.</param>
/// <param name="criteria">The <see cref="DataTransferObject"/> containing the criteria
/// to be used to determine mathing records in the database.</param>
/// <returns>The <see cref="DataTransferObject"/> now populated with the record found
/// in the database matching the specified criteria.</returns>
protected DataTransferObject ExecuteQueryDto(DALCHelper helper, DataTransferObject criteria)
{
// Initialize objects
IDataReader reader = null;
Database db = null;
DbCommandWrapper cw = null;
// Initialize output parameters
DataTransferObject dto = null;
// If the passed in helper is null throw exception
if (null == helper)
{
throw new MNNonFatalException("CommonDALC.ExecuteQueryDto was passed a null DALCHelper.");
}
// Make sure any necessary criteria has been set
if (helper.CriteriaIsValid(criteria) == false)
{
throw new MNNonFatalException("CommonDALC.ExecuteQueryDto was passed invalid search criteria.");
}
try
{
// Initialize the database and command wrapper
if (helper.DbInstanceName() == null)
{
db = DatabaseFactory.CreateDatabase();
}
else
{
db = DatabaseFactory.CreateDatabase(helper.DbInstanceName());
}
cw = helper.InitializeCommand(db, criteria);
DbCommand command = cw.Command;
// Execute a data reader
if (_isInTransaction)
{
reader = db.ExecuteReader(command, _transaction.GetTransaction());
}
else
{
reader = db.ExecuteReader(command);
}
// set command for garbage collection
command = null;
// Convert the results into a data transfer object
dto = helper.ConvertResultsDto(cw, reader);
}
catch (MNException e)
{
e.AddMessageTraceData("MNException caught in CommonDALC.ExecuteQueryDto");
throw;
}
catch (Exception e)
{
MNException ex = new MNException(e.Message, e, criteria);
ex.AddMessageTraceData("Exception caught in CommonDALC.ExecuteQueryDto");
throw ex;
}
finally
{
// Close the data reader if it has not already been closed
if ((null != reader) && (reader.IsClosed == false))
{
reader.Close();
}
// Set the database object for garbage collection
if (db != null)
{
db = null;
}
// Set the command wrapper object for garbage collection
if (cw != null)
{
cw = null;
}
}
// Return the data transfer object
return(dto);
}
/// <summary>
/// This method executes a non-query against the database. The primary purpose of this method
/// is to insert, update or delete data in the database.
/// </summary>
/// <param name="helper">The <see cref="DALCHelper"/> class that contains the database
/// command information.</param>
/// <param name="criteria">The <see cref="DataTransferObject"/> containing the data to be
/// used for the insert or update and/or the criteria used to determine mathing records in
/// the database to be updated.</param>
/// <returns>A long integer representing the count of records affected</returns>
protected int ExecuteNonQuery(DALCHelper helper, DataTransferObject criteria)
{
// Initialize objects
Database db = null;
DbCommandWrapper cw = null;
// If the passed in helper is null throw exception
if (null == helper)
{
throw new MNNonFatalException("CommonDALC.ExecuteNonQuery was passed a null DALCHelper.");
}
// Make sure any necessary criteria has been set
if (helper.CriteriaIsValid(criteria) == false)
{
throw new MNNonFatalException("CommonDALC.ExecuteNonQuery was passed invalid data and/or criteria.");
}
try
{
// Initialize the database and command wrapper
if (helper.DbInstanceName() == null)
{
db = DatabaseFactory.CreateDatabase();
}
else
{
db = DatabaseFactory.CreateDatabase(helper.DbInstanceName());
}
cw = helper.InitializeCommand(db, criteria);
DbCommand command = cw.Command;
// Execute the command
int recordsAffected = 0;
if (_isInTransaction)
{
recordsAffected = db.ExecuteNonQuery(command, _transaction.GetTransaction());
}
else
{
recordsAffected = db.ExecuteNonQuery(command);
}
// set command for garbage collection
command = null;
// return the count
return(recordsAffected);
}
catch (MNException e)
{
e.AddMessageTraceData("MNException caught in CommonDALC.ExecuteNonQuery");
throw;
}
catch (Exception e)
{
MNException ex = new MNException(e.Message, e, criteria);
ex.AddMessageTraceData("Exception caught in CommonDALC.ExecuteNonQuery");
throw ex;
}
finally
{
// Set the database object for garbage collection
if (db != null)
{
db = null;
}
// Set the command wrapper object for garbage collection
if (cw != null)
{
cw = null;
}
}
}
/// <summary>
/// This method updates the database based on modified data within the DataSet object that has been
/// submitted back to the database through this method.
/// </summary>
/// <param name="ds">The DataSet containing the modified data.</param>
/// <param name="helper">The <see cref="DALCHelperDsUpdater"/> class that contains the database
/// command information.</param>
/// <returns>An integer value of the number of records affected by the update.</returns>
protected int UpdateDataSet(DataSet ds, DALCHelperDsUpdater helper)
{
// Initialize objects
Database db = null;
DbCommandWrapper commInsert = null;
DbCommandWrapper commUpdate = null;
DbCommandWrapper commDelete = null;
// Initialize output parameters
int recordsAffected;
// If the passed in helper is null throw exception
if (null == helper)
{
throw new MNNonFatalException("CommonDALC.UpdateDataSet was passed a null DALCHelperDsUpdater.");
}
// If the passed in dataset is null throw exception
if (null == ds)
{
throw new MNNonFatalException("CommonDALC.UpdateDataSet was passed a null DataSet.");
}
try
{
// Initialize the database and command wrapper
if (helper.DbInstanceName() == null)
{
db = DatabaseFactory.CreateDatabase();
}
else
{
db = DatabaseFactory.CreateDatabase(helper.DbInstanceName());
}
commInsert = helper.InitializeInsertCommand(db);
commUpdate = helper.InitializeUpdateCommand(db);
commDelete = helper.InitializeDeleteCommand(db);
// Update the database
if (_isInTransaction)
{
recordsAffected =
db.UpdateDataSet(ds, helper.GetDsTableName(), commInsert.Command, commUpdate.Command,
commDelete.Command, _transaction.GetTransaction());
}
else
{
recordsAffected =
db.UpdateDataSet(ds, helper.GetDsTableName(), commInsert.Command, commUpdate.Command,
commDelete.Command, GetUpdateBehavior(helper.GetDsUpdateBehavior()));
}
}
catch (MNException e)
{
e.AddMessageTraceData("MNException caught in CommonDALC.UpdateDataSet");
throw;
}
catch (Exception e)
{
MNException ex = new MNException(e.Message, e);
ex.AddMessageTraceData("Exception caught in CommonDALC.UpdateDataSet");
throw ex;
}
finally
{
// Set the database object for garbage collection
if (db != null)
{
db = null;
}
// Set the command wrappers object for garbage collection
if (commInsert != null)
{
commInsert = null;
}
if (commUpdate != null)
{
commUpdate = null;
}
if (commDelete != null)
{
commDelete = null;
}
}
// Return the number of records affected by the update
return(recordsAffected);
}
/// <summary>
/// This method executes a query against the database and returns the results
/// as a DataSet. The primary purpose of this method is to retrieve a set of matching
/// records from the database that require data processing from the calling procedure.
/// The difference between ExecuteQueryDs and LoadDataSet is that this method allows
/// an existing DataSet to be passed in as an input parameter such that additional
/// results may be added to it versus a new DataSet being created.
/// </summary>
/// <param name="helper">The <see cref="DALCHelper"/> class that contains the database
/// command information.</param>
/// <param name="ds">An existing DataSet to which more information shall be added.</param>
/// <param name="criteria">The <see cref="DataTransferObject"/> containing the criteria
/// to be used to determine mathing records in the database.</param>
/// <returns>The DataSet filled with the matching records found in the database matching
/// the specified criteria.</returns>
protected void LoadDataSet(DALCHelper helper, DataSet ds, DataTransferObject criteria)
{
// Initialize objects
Database db = null;
DbCommandWrapper cw = null;
string[] tableNames = null;
// If the passed in helper is null throw exception
if (null == helper)
{
throw new MNNonFatalException("CommonDALC.LoadDataSet was passed a null DALCHelper.");
}
// Make sure any necessary criteria has been set
if (helper.CriteriaIsValid(criteria) == false)
{
throw new MNNonFatalException("CommonDALC.LoadDataSet was passed invalid search criteria.");
}
// make sure the InitializeDSTableNames method is not returning
// null.
tableNames = helper.InitializeDsTableNames();
if (tableNames == null)
{
throw new MNNonFatalException("CommonDALC.LoadDataSet has no table names specified.");
}
try
{
// Initialize the database and command wrapper
if (helper.DbInstanceName() == null)
{
db = DatabaseFactory.CreateDatabase();
}
else
{
db = DatabaseFactory.CreateDatabase(helper.DbInstanceName());
}
cw = helper.InitializeCommand(db, criteria);
DbCommand command = cw.Command;
// Load the dataset
if (_isInTransaction)
{
db.LoadDataSet(command, ds, tableNames, _transaction.GetTransaction());
}
else
{
db.LoadDataSet(command, ds, tableNames);
}
// set command for garbage collection
command = null;
}
catch (MNException e)
{
e.AddMessageTraceData("MNException caught in CommonDALC.LoadDataSet");
throw;
}
catch (Exception e)
{
MNException ex = new MNException(e.Message, e, criteria);
ex.AddMessageTraceData("Exception caught in CommonDALC.LoadDataSet");
throw ex;
}
finally
{
// Set the database object for garbage collection
if (db != null)
{
db = null;
}
// Set the command wrapper object for garbage collection
if (cw != null)
{
cw = null;
}
}
}