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 <EmployeeProjectHour> SelEmployeeProjectHour(int EmployeeID = -1, int ProjectID = -1) { int intRC = 0; objEmployeeProjectHours = new List <EmployeeProjectHour>(); IParameterFactory objParams = new EmployeeProjectHourParameterFactory(EmployeeID: EmployeeID, ProjectID: ProjectID); System.Data.SqlClient.SqlCommand objCmd = new SqlCommand("pSelEmployeeProjectHours", new ADOConnectionFactory().Connection); objCmd.CommandType = CommandType.StoredProcedure; objCmd.Parameters.Add(objParams.Parmeters["RC"]); objCmd.Parameters.Add(objParams.Parmeters["EmployeeID"]); //objCmd.Parameters.Add(objParams.Parmeters["EmployeeName"]); objCmd.Parameters.Add(objParams.Parmeters["ProjectID"]); //objCmd.Parameters.Add(objParams.Parmeters["ProjectName"]); try { objCmd.Connection.Open(); System.Data.SqlClient.SqlDataReader objDR = objCmd.ExecuteReader(); while (objDR.Read()) { EmployeeProjectHour objEPH = new EmployeeProjectHour(); objEPH.intEmployeeID = (int)objDR["ProjectID"]; objEPH.strEmployeeName = (string)objDR["EmployeeName"]; objEPH.strProjectName = (string)objDR["ProjectName"]; objEPH.intProjectID = (int)objDR["ProjectID"]; objEPH.Date = (System.DateTime)objDR["Date"]; objEPH.Hours = (decimal)objDR["Hours"]; ((List <EmployeeProjectHour>)objEmployeeProjectHours).Add(objEPH); } 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(objEmployeeProjectHours); }
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; }
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<EmployeeProjectHour> SelEmployeeProjectHour(int EmployeeID = -1, int ProjectID = -1) { int intRC = 0; objEmployeeProjectHours = new List<EmployeeProjectHour>(); IParameterFactory objParams = new EmployeeProjectHourParameterFactory(EmployeeID: EmployeeID, ProjectID: ProjectID); System.Data.SqlClient.SqlCommand objCmd = new SqlCommand("pSelEmployeeProjectHours", new ADOConnectionFactory().Connection); objCmd.CommandType = CommandType.StoredProcedure; objCmd.Parameters.Add(objParams.Parmeters["RC"]); objCmd.Parameters.Add(objParams.Parmeters["EmployeeID"]); //objCmd.Parameters.Add(objParams.Parmeters["EmployeeName"]); objCmd.Parameters.Add(objParams.Parmeters["ProjectID"]); //objCmd.Parameters.Add(objParams.Parmeters["ProjectName"]); try { objCmd.Connection.Open(); System.Data.SqlClient.SqlDataReader objDR = objCmd.ExecuteReader(); while (objDR.Read()) { EmployeeProjectHour objEPH = new EmployeeProjectHour(); objEPH.intEmployeeID = (int)objDR["EmployeeID"]; objEPH.strEmployeeName = (string)objDR["EmployeeName"]; objEPH.strProjectName = (string)objDR["ProjectName"]; objEPH.intProjectID = (int)objDR["ProjectID"]; objEPH.Date = (System.DateTime)objDR["Date"]; objEPH.Hours = (decimal)objDR["Hours"]; ((List<EmployeeProjectHour>)objEmployeeProjectHours).Add(objEPH); } 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 objEmployeeProjectHours; }