/// <summary>
/// Scalar Value: illustrates how to retrieve scalar (single value) data from database.
/// </summary>
/// <param name="employeeId"></param>
/// <returns></returns>
///Utils.GenerationCommandType.ScalarValue
public static string GetFirstNameByEmployeeId(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.AddParameterToSqlCmd(sqlCmd, "@ReturnVal", SqlDbType.NVarChar, 10, ParameterDirection.Output, null);
DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CUSTOM_EMPLOYEES_GETEMPLOYEEFIRSTNAME_BY_EMPLOYEEID);
//executing the command
DatabaseUtility.ExecuteScalarCmd(sqlCmd);
string returnValue = (string)sqlCmd.Parameters["@ReturnVal"].Value;
return(returnValue);
}
/// <summary>
/// sample custom method for get
/// returns a collection for records of DataTable type
/// Generic Data Table: illustrates how to retrieve a collection of data from database and populate it to a .NET generic DataTable instance.
/// </summary>
/// <param name="reportsTo"></param>
/// <returns></returns>
///Utils.GenerationCommandType.GenericDataTable
public static DataTable GetEmployeesByReportsTo(int reportsTo)
{
DataSet ds = new DataSet();
ds.Locale = System.Globalization.CultureInfo.CurrentCulture;
using (SqlConnection cn = new SqlConnection(ConnectionStringManager.DefaultDBConnectionString))
{
//sql command
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = cn;
DatabaseUtility.AddParameterToSqlCmd(sqlCmd, "@ReportsTo", SqlDbType.Int, 0, ParameterDirection.Input, reportsTo);
DatabaseUtility.SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_EMPLOYEES_GETEMPLOYEES_BY_REPORTSTO);
//adapter
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = sqlCmd;
adapter.Fill(ds);
}
return(ds.Tables[0]);
}
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);
}
}