public IEnumerable<ValidHourEntry> SelValidHourEntries()
{
//Set up the variables
int intRC = 0;
SqlDataReader dr;
List<ValidHourEntry> retVal = new List<ValidHourEntry>();
//Set up the parameters using my custom parameter factory
IParameterFactory objParams = new ValidHourEntryParameterFactory();
try
{
using (SqlConnection sqlConnection = new ADOConnectionFactory().Connection)
{
sqlConnection.Open();
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = sqlConnection;
sqlCommand.Parameters.Add((objParams.Parameters["RC"]));
//call stored procedure to insert record
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "pSelValidHours";
dr = sqlCommand.ExecuteReader();
while (dr.Read())
{
ValidHourEntry vhe = new ValidHourEntry();
vhe.TimePeriodID = (int)dr["TimePeriodID"];
vhe.TimePeriod = (string)dr["TimePeriod"];
retVal.Add(vhe);
}
dr.Close();
//check the return code for errors
intRC = (int)objParams.Parameters["RC"].Value;
if (intRC < 0)
{
throw new Exception("Error reported in Stored Procedure: " + objParams.Parameters["RC"].Value.ToString());
}
}
}
}
catch (Exception)
{
throw;
}
return retVal;
}
public IEnumerable<ThisYearsDate> SelThisYearsDates()
{
//Set up the variables
int intRC = 0;
SqlDataReader dr;
List<ThisYearsDate> retVal = new List<ThisYearsDate>();
//Set up the parameters using my custom parameter factory
IParameterFactory objParams = new ThisYearsDateParameterFactory();
try
{
using (SqlConnection sqlConnection = new ADOConnectionFactory().Connection)
{
sqlConnection.Open();
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = sqlConnection;
sqlCommand.Parameters.Add((objParams.Parameters["RC"]));
//call stored procedure to insert record
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "pSelThisYearsDates";
dr = sqlCommand.ExecuteReader();
while (dr.Read())
{
ThisYearsDate tyd = new ThisYearsDate();
tyd._dateID = (int)dr["dateID"];
tyd._dateName = (string)dr["dateName"];
retVal.Add(tyd);
}
dr.Close();
//check the return code for errors
intRC = (int)objParams.Parameters["RC"].Value;
if (intRC < 0)
{
throw new Exception("Error reported in Stored Procedure: " + objParams.Parameters["RC"].Value.ToString());
}
}
}
}
catch (Exception)
{
throw;
}
return retVal;
}
public IEnumerable<EmployeeProjectHour> SelEmployeeProjectHours()
{
//Set up the variables
int intRC = 0;
SqlDataReader dr;
List<EmployeeProjectHour> retVal = new List<EmployeeProjectHour>();
//Set up the parameters using my custom parameter factory
IParameterFactory objParams = new EmployeeProjectHourParameterFactory(0, String.Empty, 0, String.Empty, DateTime.Now, 0);
try
{
using (SqlConnection sqlConnection = new ADOConnectionFactory().Connection)
{
sqlConnection.Open();
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = sqlConnection;
sqlCommand.Parameters.Add((objParams.Parameters["RC"]));
//call stored procedure to insert record
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "pSelEmployeeProjectHours";
dr = sqlCommand.ExecuteReader();
while (dr.Read())
{
EmployeeProjectHour eph = new EmployeeProjectHour();
eph.EmployeeID = (int)dr["EmployeeID"];
eph.EmployeeName = (string)dr["EmployeeName"];
eph.ProjectID = (int)dr["ProjectID"];
eph.ProjectName = (string)dr["ProjectName"];
eph.Date = (DateTime)dr["Date"];
eph.Hours = (decimal)dr["Hours"];
retVal.Add(eph);
}
dr.Close();
//check the return code for errors
intRC = (int)objParams.Parameters["RC"].Value;
if (intRC < 0)
{
throw new Exception("Error reported in Stored Procedure: " + objParams.Parameters["RC"].Value.ToString());
}
}
}
}
catch (Exception)
{
throw;
}
return retVal;
}
public int DelEmployeeProjectHours(int EmployeeID, int ProjectID, DateTime Date)
{
//Set up the variables
int intRC = 0; //Used to trap the Stored Procedure's return code
//Set up the parameters using my custom parameter factory
IParameterFactory objParams = new EmployeeProjectHourParameterFactory(EmployeeID, String.Empty, ProjectID, String.Empty, Date, 0);
try
{
using (SqlConnection sqlConnection = new ADOConnectionFactory().Connection)
{
sqlConnection.Open();
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = sqlConnection;
//add parameters to procedure
sqlCommand.Parameters.Add(objParams.Parameters["RC"]);
sqlCommand.Parameters.Add(objParams.Parameters["EmployeeID"]);
sqlCommand.Parameters.Add(objParams.Parameters["ProjectID"]);
sqlCommand.Parameters.Add(objParams.Parameters["Date"]);
//call stored procedure to insert record
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "pDelEmployeeProjectHours";
sqlCommand.ExecuteNonQuery();
//Trap or return the Stored Procedure's return code
intRC = (int)objParams.Parameters["RC"].Value;
if (intRC < 0)
{
throw new Exception("Error reported in Stored Procedure: " + objParams.Parameters["RC"].Value.ToString());
}
}
}
}
catch (Exception)
{
throw;
}
return intRC;
}
public int UpdProject(int ProjectID, string ProjectName, string ProjectDescription)
{
//Set up the variables
int intRC = 0; //Used to trap the Stored Procedure's return code
//Set up the parameters using my custom parameter factory
IParameterFactory objParams = new ProjectsParameterFactory(ProjectID, ProjectName, ProjectDescription);
try
{
using (SqlConnection sqlConnection = new ADOConnectionFactory().Connection)
{
sqlConnection.Open();
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = sqlConnection;
//add parameters to procedure
sqlCommand.Parameters.Add(objParams.Parameters["RC"]);
sqlCommand.Parameters.Add(objParams.Parameters["ProjectID"]);
sqlCommand.Parameters.Add(objParams.Parameters["ProjectName"]);
sqlCommand.Parameters.Add(objParams.Parameters["ProjectDescription"]);
//call stored procedure to insert record
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "pUpdProjects";
sqlCommand.ExecuteNonQuery();
//Trap or return the Stored Procedure's return code
intRC = (int)objParams.Parameters["RC"].Value;
if (intRC < 0)
{
throw new Exception("Error reported in Stored Procedure: " + objParams.Parameters["RC"].Value.ToString());
}
}
}
}
catch (Exception)
{
throw;
}
return intRC;
}
public int InsProject(string ProjectName, string ProjectDescription, out int NewRowID)
{
//Set up the variables
int intRC = 0; //Used to trap the Stored Procedure's return code
//Set up the parameters using my custom parameter factory
IParameterFactory objParams = new ProjectsParameterFactory(ProjectName : ProjectName, ProjectDescription: ProjectDescription);
try
{
using (SqlConnection sqlConnection = new ADOConnectionFactory().Connection)
{
sqlConnection.Open();
using (SqlCommand sqlCommand = new SqlCommand())
{
sqlCommand.Connection = sqlConnection;
//add parameters to procedure
sqlCommand.Parameters.Add(objParams.Parameters["RC"]);
sqlCommand.Parameters.Add(objParams.Parameters["ProjectName"]);
sqlCommand.Parameters.Add(objParams.Parameters["ProjectDescription"]);
sqlCommand.Parameters.Add(objParams.Parameters["NewRowID"]);
//call stored procedure to insert record
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "pInsProjects";
sqlCommand.ExecuteNonQuery();
//Get the new row ID created by the table's Identity feature
if (objParams.Parameters["NewRowID"].Value is DBNull)
{ NewRowID = 0; } //if the insert has failed, then set this to an arbitrary number
else { NewRowID = (int)objParams.Parameters["NewRowID"].Value; } //else send it back as output
//Trap or return the Stored Procedure's return code
intRC = (int)objParams.Parameters["RC"].Value;
if (intRC < 0)
{
throw new Exception("Error reported in Stored Procedure: " + objParams.Parameters["RC"].Value.ToString());
}
}
}
}
catch (Exception)
{
throw;
}
return intRC;
}
