static void TestProject()
{
DataAccessLayer.Project objP = new DataAccessLayer.Project();
int NewID, RC;
RC = objP.InsProject("testName", "testDescription", out NewID);
Console.WriteLine("RC: {0} and NewID: {1}", RC, NewID);
foreach (Project prjt in objP.SelProjects())
{
Console.WriteLine("Project ID: {0}, Project Name: {1}, Project Description: {2}", prjt.ProjectID, prjt.ProjectName, prjt.ProjectDescription);
}
RC = objP.UpdProject(NewID, "testname2", "testDescription2");
Console.WriteLine("RC: {0} on update call", RC);
foreach (Project prjt in objP.SelProjects())
{
Console.WriteLine("Project ID: {0}, Project Name: {1}, Project Description: {2}", prjt.ProjectID, prjt.ProjectName, prjt.ProjectDescription);
}
RC = objP.DelProject(NewID);
Console.WriteLine("RC: {0} on delete call", RC);
foreach (Project prjt in objP.SelProjects())
{
Console.WriteLine("Project ID: {0}, Project Name: {1}, Project Description: {2}", prjt.ProjectID, prjt.ProjectName, prjt.ProjectDescription);
}
}
public FormManager()
{
InitializeComponent();
eph = new List<EmployeeProjectHour>();
objEPH = new EmployeeProjectHour();
listEmployee = new List<Employee>();
empl = new Employee();
listProject = new List<Project>();
prjt = new Project();
}
public IEnumerable<Project> SelProjects()
{
//Set up the variables
int intRC = 0;
SqlDataReader dr;
List<Project> retVal = new List<Project>();
//Set up the parameters using my custom parameter factory
IParameterFactory objParams = new ProjectsParameterFactory();
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 = "pSelProjects";
dr = sqlCommand.ExecuteReader();
while (dr.Read())
{
Project prjt = new Project();
prjt.ProjectID = (int)dr["ProjectID"];
prjt.ProjectName = (string)dr["ProjectName"];
prjt.ProjectDescription = (string)dr["ProjectDescription"];
retVal.Add(prjt);
}
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;
}
static void TestEmployeeProjectHour()
{
DataAccessLayer.EmployeeProjectHour objEph = new DataAccessLayer.EmployeeProjectHour();
int RC;
DateTime dt = DateTime.Now;
int newEmployeeID;
int newProjectID;
foreach (EmployeeProjectHour prjt in objEph.SelEmployeeProjectHours())
{
Console.WriteLine("Employee ID: {0}, Employee Name {1}, Project ID: {2}, Project Name: {3}, Date: {4}, Hours: {5}", prjt.EmployeeID, prjt.EmployeeName, prjt.ProjectID, prjt.ProjectName, prjt.Date, prjt.Hours);
}
//create an employee and a project to insert
Employee objEmpl = new Employee();
objEmpl.InsEmployee("bob dobalina",out newEmployeeID);
Project objProj = new Project();
objProj.InsProject("recording","", out newProjectID);
RC = objEph.InsEmployeeProjectHours(newEmployeeID, newProjectID, dt, 42);
Console.WriteLine();
Console.WriteLine("RC: {0}", RC);
foreach (EmployeeProjectHour prjt in objEph.SelEmployeeProjectHours())
{
Console.WriteLine("Employee ID: {0}, Employee Name {1}, Project ID: {2}, Project Name: {3}, Date: {4}, Hours: {5}", prjt.EmployeeID, prjt.EmployeeName, prjt.ProjectID, prjt.ProjectName, prjt.Date, prjt.Hours);
}
RC = objEph.UpdEmployeeProjectHours(newEmployeeID, newProjectID, dt, 104);
Console.WriteLine();
Console.WriteLine("RC: {0}", RC);
foreach (EmployeeProjectHour prjt in objEph.SelEmployeeProjectHours())
{
Console.WriteLine("Employee ID: {0}, Employee Name {1}, Project ID: {2}, Project Name: {3}, Date: {4}, Hours: {5}", prjt.EmployeeID, prjt.EmployeeName, prjt.ProjectID, prjt.ProjectName, prjt.Date, prjt.Hours);
}
RC = objEph.DelEmployeeProjectHours(newEmployeeID, newProjectID, dt);
Console.WriteLine();
Console.WriteLine("RC: {0}", RC);
foreach (EmployeeProjectHour prjt in objEph.SelEmployeeProjectHours())
{
Console.WriteLine("Employee ID: {0}, Employee Name {1}, Project ID: {2}, Project Name: {3}, Date: {4}, Hours: {5}", prjt.EmployeeID, prjt.EmployeeName, prjt.ProjectID, prjt.ProjectName, prjt.Date, prjt.Hours);
}
objEmpl.DelEmployee(newEmployeeID);
objProj.DelProject(newProjectID);
}
public IEnumerable<Project> SelProject(int ProjectID = -1)
{
int intRC = 0;
objProjects = new List<Project>();
IParameterFactory objParams = new ProjectsParameterFactory(ProjectID: ProjectID, ProjectName: ProjectName);
System.Data.SqlClient.SqlCommand objCmd = new SqlCommand("pSelProjects", new ADOConnectionFactory().Connection);
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Parameters.Add(objParams.Parmeters["RC"]);
objCmd.Parameters.Add(objParams.Parmeters["ProjectID"]);
try
{
objCmd.Connection.Open();
System.Data.SqlClient.SqlDataReader objDR = objCmd.ExecuteReader();
while (objDR.Read())
{
Project objP = new Project();
objP.intProjectID = (int)objDR["ProjectID"];
objP.strProjectName = (string)objDR["ProjectName"];
objP.strProjectDescription = (string)objDR["ProjectDescription"];
((List<Project>)objProjects).Add(objP);
}
objDR.Close(); //You cannot get the return value if the reader is not closed!
intRC = (int)objParams.Parmeters["RC"].Value;
if (intRC < 0)
{ throw new Exception("Error reported in Stored Procedure: " + objParams.Parmeters["RC"].Value.ToString()); }
objCmd.Connection.Close();
}
catch (Exception) { throw; }
return objProjects;
}