/// <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); }