/// <summary>
/// this is the basic method that will be used by the underlying instance TestObjects.
/// </summary>
/// <param name="sqlCmd">sql command</param>
/// <param name="generateCollectionDelegateObject">The collection generator method delegate.
/// The passed delegate object will be used to call the appropriate collection generator method from the caller class.</param>
/// <returns>The object collection.</returns>
public static CollectionBase ExecuteReaderCmd(SqlCommand sqlCmd, GenerateCollectionFromReader generateCollectionDelegateObject, string connectionString)
{
if (connectionString == null)
{
throw (new ArgumentNullException("connectionString"));
}
if (connectionString.Length == 0)
{
throw (new ArgumentOutOfRangeException("connectionString"));
}
if (generateCollectionDelegateObject == null)
{
throw (new ArgumentNullException("generateCollectionDelegateObject"));
}
if (sqlCmd == null)
{
throw (new ArgumentNullException("sqlCmd"));
}
using (SqlConnection cn = new SqlConnection(connectionString))
{
sqlCmd.Connection = cn;
cn.Open();
CollectionBase temp = generateCollectionDelegateObject(sqlCmd.ExecuteReader());
return(temp);
}
}
public static CustomCollection <Boss> SelectAllEmployeeBosses()
{
// Execute SQL Command
SqlCommand sqlCmd = new SqlCommand();
DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CUSTOM_EMPLOYEES_GETALLEMPLOYEEBOSSES);
GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateBossCollectionFromReader);
CustomCollection <Boss> objCollection = ((CustomCollection <Boss>)DatabaseUtility.ExecuteReaderCmd(sqlCmd, test));
return(objCollection);
}
/// <summary>
/// Executes a reader command, and returns the result as a collection of object.
/// If no connection string is provided, Default database is assumed.
/// </summary>
/// <param name="sqlCmd">sql command</param>
/// <param name="generateCollectionDelegateObject">The collection generator method delegate.
/// The passed delegate object will be used to call the appropriate collection generator method from the caller class.</param>
/// <returns>The object collection.</returns>
public static CollectionBase ExecuteReaderCmd(SqlCommand sqlCmd, GenerateCollectionFromReader generateCollectionDelegateObject)
{
if (generateCollectionDelegateObject == null)
{
throw (new ArgumentNullException("generateCollectionDelegateObject"));
}
if (sqlCmd == null)
{
throw (new ArgumentNullException("sqlCmd"));
}
return(ExecuteReaderCmd(sqlCmd, generateCollectionDelegateObject, ConnectionStringManager.DefaultDBConnectionString));
}
protected object ExecuteReader(string storedProcedure, object[] @params, GenerateCollectionFromReader fofr)
{
Database db = default(Database);
try
{
db = DatabaseFactory.CreateDatabase(_ProvideName);
IDataReader Idata = (IDataReader)(db.ExecuteReader(storedProcedure, @params));
return(fofr(ref Idata));
}
catch (Exception ex)
{
}
finally
{
//log to db
db = null;
}
return(null);
}
public static IList ExecuteReaderCmd(SqlCommand sqlCmd, GenerateCollectionFromReader generateCollectionDelegateObject, string connectionString)
{
if (connectionString == null)
throw (new ArgumentNullException("connectionString"));
if (connectionString.Length == 0)
throw (new ArgumentOutOfRangeException("connectionString"));
if ( generateCollectionDelegateObject == null)
throw (new ArgumentNullException("generateCollectionDelegateObject"));
if (sqlCmd == null)
throw (new ArgumentNullException("sqlCmd"));
using (SqlConnection cn = new SqlConnection(connectionString))
{
sqlCmd.Connection = cn;
cn.Open();
IList temp = generateCollectionDelegateObject(sqlCmd.ExecuteReader());
return (temp);
}
}
public static Boss GetEmployeeBossByEmployeeId(int employeeId)
{
if (employeeId < GetIdMinValue)
{
throw (new ArgumentOutOfRangeException("employeeId"));
}
// Execute SQL Command
SqlCommand sqlCmd = new SqlCommand();
DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@employeeId", SqlDbType.Int, 0, ParameterDirection.Input, employeeId);
DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CUSTOM_EMPLOYEES_GETEMPLOYEEBOSSBYEMPLOYEEID);
GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateBossCollectionFromReader);
CustomCollection <Boss> objCollection = ((CustomCollection <Boss>)DatabaseUtility.ExecuteReaderCmd(sqlCmd, test));
if (objCollection.Count > 0)
{
return(objCollection[0]);
}
else
{
return(null);
}
}
protected object ExecuteReader(string storedProcedure, object[] @params, GenerateCollectionFromReader fofr)
{
Database db = default(Database);
try
{
db = DatabaseFactory.CreateDatabase(_ProvideName);
IDataReader Idata = (IDataReader)(db.ExecuteReader(storedProcedure, @params));
return fofr(ref Idata);
}
catch (Exception ex)
{
}
finally
{
//log to db
db = null;
}
return null;
}
public static CustomCollection<Employee> SelectAllEmployeesPaged(string orderBy, int startRowIndex, int maximumRows)
{
//Validate Input
if (string.IsNullOrEmpty(orderBy))
orderBy = "EmployeeId";
// Execute SQL Command
SqlCommand sqlCmd = new SqlCommand();
DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);
DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_EMPLOYEES_GETALLEMPLOYEES_PAGED);
GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateEmployeeCollectionFromReader);
CustomCollection<Employee> objCollection = ((CustomCollection<Employee>)DatabaseUtility.ExecuteReaderCmd(sqlCmd, test));
return objCollection;
}
public static CustomCollection<Employee> SelectAllEmployees()
{
// Execute SQL Command
SqlCommand sqlCmd = new SqlCommand();
DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_EMPLOYEES_GETALLEMPLOYEES);
GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateEmployeeCollectionFromReader);
CustomCollection<Employee> objCollection = ((CustomCollection<Employee>)DatabaseUtility.ExecuteReaderCmd(sqlCmd, test));
return objCollection;
}
public static CustomCollection<Employee> GetEmployeesByReportsToPaged(int? reportsTo, string orderBy, int startRowIndex, int maximumRows)
{
//Validate Input
//If reportsTo is less than the MinValue then return all records of the corresponding table, regarding the page size
if (reportsTo == null)
return SelectAllEmployeesPaged(orderBy, startRowIndex, maximumRows);
else if (reportsTo <= Employee.SelectEmployeeIdMinValue)
throw (new ArgumentOutOfRangeException("reportsTo"));
//If there is no sorting parameter, then by default consider the primary key as the sorting field
if (string.IsNullOrEmpty(orderBy))
orderBy = "EmployeeId";
// Execute SQL Command
SqlCommand sqlCmd = new SqlCommand();
DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@reportsTo", SqlDbType.Int, 0, ParameterDirection.Input, reportsTo);
DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);
DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_EMPLOYEES_GETEMPLOYEES_BY_REPORTSTO_PAGED);
GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateEmployeeCollectionFromReader);
CustomCollection<Employee> objCollection = ((CustomCollection<Employee>)DatabaseUtility.ExecuteReaderCmd(sqlCmd, test));
return objCollection;
}
public static CustomCollection<Employee> GetEmployeesByReportsTo(int? reportsTo)
{
//Validate Input
if (reportsTo == null)
return SelectAllEmployees();
else if (reportsTo <= Employee.SelectEmployeeIdMinValue)
throw (new ArgumentOutOfRangeException("reportsTo"));
// Execute SQL Command
SqlCommand sqlCmd = new SqlCommand();
DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@reportsTo", SqlDbType.Int, 0, ParameterDirection.Input, reportsTo);
DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_EMPLOYEES_GETEMPLOYEES_BY_REPORTSTO);
GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateEmployeeCollectionFromReader);
CustomCollection<Employee> objCollection = ((CustomCollection<Employee>)DatabaseUtility.ExecuteReaderCmd(sqlCmd, test));
return objCollection;
}
public static Employee GetEmployeeByEmployeeId(int employeeId)
{
//Validate Input
if (employeeId <= SelectEmployeeIdMinValue)
throw (new ArgumentOutOfRangeException("employeeId"));
// Execute SQL Command
SqlCommand sqlCmd = new SqlCommand();
DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@EmployeeId", SqlDbType.Int, 0, ParameterDirection.Input, employeeId);
DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_EMPLOYEES_GETEMPLOYEE_BY_EMPLOYEEID);
GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateEmployeeCollectionFromReader);
CustomCollection<Employee> objCollection = ((CustomCollection<Employee>)DatabaseUtility.ExecuteReaderCmd(sqlCmd, test));
if (objCollection.Count > 0)
return objCollection[0];
else
return null;
}
public static IList ExecuteReaderCmd(SqlCommand sqlCmd, GenerateCollectionFromReader generateCollectionDelegateObject)
{
return ExecuteReaderCmd(sqlCmd, generateCollectionDelegateObject, ConnectionStringManager.DefaultDBConnectionString);
}
/// <summary>
/// Executes a reader command, and returns the result as a collection of object.
/// If no connection string is provided, Default database is assumed.
/// </summary>
/// <param name="sqlCmd">sql command</param>
/// <param name="generateCollectionDelegateObject">The collection generator method delegate.
/// The passed delegate object will be used to call the appropriate collection generator method from the caller class.</param>
/// <returns>The object collection.</returns>
public static CollectionBase ExecuteReaderCmd(SqlCommand sqlCmd, GenerateCollectionFromReader generateCollectionDelegateObject)
{
if (generateCollectionDelegateObject == null)
throw (new ArgumentNullException("generateCollectionDelegateObject"));
if (sqlCmd == null)
throw (new ArgumentNullException("sqlCmd"));
return ExecuteReaderCmd(sqlCmd, generateCollectionDelegateObject, ConnectionStringManager.DefaultDBConnectionString);
}
