/// <summary>
/// Loads a data reader
/// </summary>
/// <param name="selectSql">The sql statement object</param>
/// <returns>Returns an IDataReader object</returns>
/// <exception cref="DatabaseReadException">Thrown when an error
/// occurred while setting up the data reader. Also sends error
/// output to the log.</exception>
public virtual IDataReader LoadDataReader(ISqlStatement selectSql)
{
if (selectSql == null)
{
throw new DatabaseConnectionException
("The sql statement object " + "that has been passed to LoadDataReader() is null.");
}
IDbConnection con = null;
try
{
con = GetOpenConnectionForReading();
var cmd = CreateCommand(con);
selectSql.SetupCommand(cmd);
SetupReadTransaction(cmd);
return(cmd.ExecuteReader(CommandBehavior.CloseConnection));
}
catch (Exception ex)
{
Log.Log
("Error reading from database : " + Environment.NewLine
+ ExceptionUtilities.GetExceptionString(ex, 10, true), LogCategory.Exception);
Log.Log("Sql: " + selectSql, LogCategory.Exception);
Console.Out.WriteLine
("Error reading from database : " + Environment.NewLine
+ ExceptionUtilities.GetExceptionString(ex, 10, true));
Console.Out.WriteLine("Sql: " + selectSql);
throw new DatabaseReadException
("There was an error reading the database. Please contact your system administrator." + Environment.NewLine +
selectSql.ToString() + Environment.NewLine,
"The DataReader could not be filled with", ex, selectSql.ToString(), ErrorSafeConnectString());
}
}
/// <summary>
/// Loads data from the database into a DataTable object, using the
/// sql statement object provided
/// </summary>
/// <param name="selectSql">The sql statement object</param>
/// <param name="strSearchCriteria">The search criteria as a string
/// to append</param>
/// <param name="strOrderByCriteria">The order by criteria as a string
/// to append</param>
/// <returns>Returns a DataTable object</returns>
/// <exception cref="DatabaseReadException">Thrown if there is an
/// error reading the database. Also outputs error messages to the log.
/// </exception>
public DataTable LoadDataTable(ISqlStatement selectSql, string strSearchCriteria, string strOrderByCriteria)
{
//It was chosen to use a datareader to fill the DataTable instead of using an
// adapter because the data adapter approach requires creating a different adapter depending on the
// database type. This is understandable but for simple loading of a datatable without all the additional
// schema data it is unneccessary.
// It could however be easily achieved since there is a physical instance of a database connection object
// per database type that inherit from this class e.g. DatabaseConnectionMySQL.
if (selectSql == null)
{
throw new ArgumentNullException("selectSql");
}
IDbConnection con = null;
try
{
con = GetOpenConnectionForReading();
IDbCommand cmd = CreateCommand(con);
selectSql.SetupCommand(cmd);
DataTable dt;
using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt = GetDataTable(reader);
reader.Close();
}
return(dt);
}
catch (Exception ex)
{
Log.Log
("Error in LoadDataTable:" + Environment.NewLine
+ ExceptionUtilities.GetExceptionString(ex, 8, true), LogCategory.Exception);
Log.Log("Sql string: " + selectSql, LogCategory.Exception);
throw new DatabaseReadException
("There was an error reading the database. Please contact your system administrator.",
"The DataReader could not be filled with", ex, selectSql.ToString(), ErrorSafeConnectString());
}
}
/// <summary>
/// Loads data from the database into a DataTable object, using the
/// sql statement object provided
/// </summary>
/// <param name="selectSql">The sql statement object</param>
/// <param name="strSearchCriteria">The search criteria as a string
/// to append</param>
/// <param name="strOrderByCriteria">The order by criteria as a string
/// to append</param>
/// <returns>Returns a DataTable object</returns>
/// <exception cref="DatabaseReadException">Thrown if there is an
/// error reading the database. Also outputs error messages to the log.
/// </exception>
public DataTable LoadDataTable(ISqlStatement selectSql, string strSearchCriteria, string strOrderByCriteria)
{
//It was chosen to use a datareader to fill the DataTable instead of using an
// adapter because the data adapter approach requires creating a different adapter depending on the
// database type. This is understandable but for simple loading of a datatable without all the additional
// schema data it is unneccessary.
// It could however be easily achieved since there is a physical instance of a database connection object
// per database type that inherit from this class e.g. DatabaseConnectionMySQL.
if (selectSql == null) throw new ArgumentNullException("selectSql");
IDbConnection con = null;
try
{
con = GetOpenConnectionForReading();
IDbCommand cmd = CreateCommand(con);
selectSql.SetupCommand(cmd);
DataTable dt;
using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt = GetDataTable(reader);
reader.Close();
}
return dt;
}
catch (Exception ex)
{
Log.Log
("Error in LoadDataTable:" + Environment.NewLine
+ ExceptionUtilities.GetExceptionString(ex, 8, true), LogCategory.Exception);
Log.Log("Sql string: " + selectSql, LogCategory.Exception);
throw new DatabaseReadException
("There was an error reading the database. Please contact your system administrator.",
"The DataReader could not be filled with", ex, selectSql.ToString(), ErrorSafeConnectString());
}
}
/// <summary>
/// Loads a data reader
/// </summary>
/// <param name="selectSql">The sql statement object</param>
/// <returns>Returns an IDataReader object</returns>
/// <exception cref="DatabaseReadException">Thrown when an error
/// occurred while setting up the data reader. Also sends error
/// output to the log.</exception>
public virtual IDataReader LoadDataReader(ISqlStatement selectSql)
{
if (selectSql == null)
{
throw new DatabaseConnectionException
("The sql statement object " + "that has been passed to LoadDataReader() is null.");
}
IDbConnection con = null;
try
{
con = GetOpenConnectionForReading();
var cmd = CreateCommand(con);
selectSql.SetupCommand(cmd);
SetupReadTransaction(cmd);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
Log.Log
("Error reading from database : " + Environment.NewLine
+ ExceptionUtilities.GetExceptionString(ex, 10, true), LogCategory.Exception);
Log.Log("Sql: " + selectSql, LogCategory.Exception);
Console.Out.WriteLine
("Error reading from database : " + Environment.NewLine
+ ExceptionUtilities.GetExceptionString(ex, 10, true));
Console.Out.WriteLine("Sql: " + selectSql);
throw new DatabaseReadException
("There was an error reading the database. Please contact your system administrator." + Environment.NewLine +
selectSql.ToString() + Environment.NewLine,
"The DataReader could not be filled with", ex, selectSql.ToString(), ErrorSafeConnectString());
}
}
/// <summary>
/// Sets up an <see cref="IDbCommand"/> object with the provided statement.
/// </summary>
/// <param name="statement">The <see cref="ISqlStatement"/> to setup the command with.</param>
/// <param name="command">The <see cref="IDbCommand"/> to set up.</param>
/// <param name="transaction">The <see cref="IDbTransaction"/> that the command runs under.</param>
protected virtual void SetupCommand(ISqlStatement statement, IDbCommand command, IDbTransaction transaction)
{
statement.SetupCommand(command);
}
/// <summary>
/// Sets up an <see cref="IDbCommand"/> object with the provided statement.
/// </summary>
/// <param name="statement">The <see cref="ISqlStatement"/> to setup the command with.</param>
/// <param name="command">The <see cref="IDbCommand"/> to set up.</param>
/// <param name="transaction">The <see cref="IDbTransaction"/> that the command runs under.</param>
protected virtual void SetupCommand(ISqlStatement statement, IDbCommand command, IDbTransaction transaction)
{
statement.SetupCommand(command);
}
