Пример #1
0
        static void TestEmployee()
        {
            DataAccessLayer.Employee objE = new DataAccessLayer.Employee();
            int NewID, RC;
            RC = objE.InsEmployee("test", out NewID);
            Console.WriteLine("RC: {0} and NewID: {1}", RC, NewID);

            foreach (Employee empl in objE.SelEmployee())
            {
                Console.WriteLine("Employee ID: {0}, Employee Name: {1}", empl.EmployeeID, empl.EmployeeName);
            }

            RC = objE.UpdEmployee(NewID, "test2");

            Console.WriteLine("RC: {0} on update call", RC);

            foreach (Employee empl in objE.SelEmployee())
            {
                Console.WriteLine("Employee ID: {0}, Employee Name: {1}", empl.EmployeeID, empl.EmployeeName);
            }

            RC = objE.DelEmployee(NewID);

            Console.WriteLine("RC: {0} on delete call", RC);

            foreach (Employee empl in objE.SelEmployee())
            {
                Console.WriteLine("Employee ID: {0}, Employee Name: {1}", empl.EmployeeID, empl.EmployeeName);
            }

        }
Пример #2
0
        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 ActionResult formCollection(FormCollection formCollection)
        {
            Employee emp = new Employee();
            DataAccessLib db = new DataAccessLib();
            emp.Name = formCollection["Name"];
            emp.Designation = formCollection["Designation"];
            emp.Gender = formCollection["Gender"];
            emp.Salary = Convert.ToDouble(formCollection["Salary"]);
            emp.City = formCollection["City"];
            emp.State = formCollection["State"];
            emp.Zip = formCollection["Zip"];

            db.AddEmployees(emp);

            return RedirectToAction("Index");
        }
        public void AddEmployees(Employee emp)
        {
            string con = System.Configuration.ConfigurationManager.ConnectionStrings["connectionStr"].ToString();
            SqlConnection sql_conn = new SqlConnection(con);
            sql_conn.Open();
            using (SqlCommand sql_command = new SqlCommand("uspInsertEmployee", sql_conn))
            {
                sql_command.CommandType = CommandType.StoredProcedure;
                sql_command.Parameters.Clear();
                sql_command.Parameters.AddWithValue("@name", emp.Name);
                sql_command.Parameters.AddWithValue("@designation", emp.Designation);
                sql_command.Parameters.AddWithValue("@gender", emp.Gender);
                sql_command.Parameters.AddWithValue("@salary", emp.Salary);
                sql_command.Parameters.AddWithValue("@city", emp.City);
                sql_command.Parameters.AddWithValue("@state", emp.State);
                sql_command.Parameters.AddWithValue("@zip", emp.Zip);
                sql_command.ExecuteNonQuery();
            }

        }
Пример #5
0
        public IEnumerable<Employee> SelEmployee()
        {
            //Set up the variables
            int intRC = 0;
            SqlDataReader dr;
            List<Employee> retVal = new List<Employee>();
            
            //Set up the parameters using my custom parameter factory
            IParameterFactory objParams = new EmployeesParameterFactory();

            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 = "pSelEmployees";

                        dr = sqlCommand.ExecuteReader();

                        while (dr.Read())
                        {
                            Employee empl = new Employee();
                            empl.EmployeeID = (int) dr["EmployeeID"];
                            empl.EmployeeName = (string) dr["EmployeeName"];
                            retVal.Add(empl);
                        }
                        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;
        }
Пример #6
0
        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);

        }
Пример #7
0
        public List <Employee> ReadEmployeeFromDepartmentFromDB(EmployeeDepartment department)
        {
            List <Employee>         employees = new List <Employee>();
            List <EmployeeContract> contracts = new List <EmployeeContract>();

            try
            {
                string       sql = $"SELECT FirstName, LastName, employee.EmployeeNumber, Department,  DateOfBirth, City, Street, HouseNumber, HouseNumberAddition, PostCode, Email, Phone, BSN, ICEName, ICEPhone, Password FROM employee INNER JOIN employeeemploymentinfo ON employee.EmployeeNumber = employeeemploymentinfo.EmployeeNumber INNER JOIN empoyeepersonalinfo ON employee.EmployeeNumber = empoyeepersonalinfo.EmployeeNumber WHERE Department='{department}';";
                MySqlCommand cmd = new MySqlCommand(sql, conn);

                string       contract    = "SELECT EmployeeNumber, FTE, Salary, StartDate, EndDate, EndReason, IsActive, ContractNumber FROM contract;";
                MySqlCommand cmdcontract = new MySqlCommand(contract, conn);

                conn.Open();

                MySqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    Address adress = new Address(dr[6].ToString(), Convert.ToInt32(dr[7]), dr[8].ToString(), dr[9].ToString(), dr[5].ToString());
                    EmployeePersonalInfo          persInfo = new EmployeePersonalInfo(Convert.ToDateTime(dr[4]), dr[11].ToString(), adress, dr[10].ToString(), dr[12].ToString(), dr[13].ToString(), dr[14].ToString(), dr[15].ToString());
                    EmployeeEmploymentInformation emplInfo = new EmployeeEmploymentInformation((EmployeeDepartment)Enum.Parse(typeof(EmployeeDepartment), dr[3].ToString()));
                    Employee e = new Employee(dr[0].ToString(), dr[1].ToString(), emplInfo, persInfo, Convert.ToInt32(dr[2]));
                    employees.Add(e);
                }
                dr.Close();

                MySqlDataReader drcontr = cmdcontract.ExecuteReader();
                while (drcontr.Read())
                {
                    if (Convert.ToBoolean(drcontr[6]))
                    {
                        EmployeeContract eca = new EmployeeContract(Convert.ToDouble(drcontr[1]), Convert.ToDouble(drcontr[2]), Convert.ToDateTime(drcontr[3]), Convert.ToInt32(drcontr[0]), drcontr[7].ToString());
                        contracts.Add(eca);
                    }
                    else
                    {
                        EmployeeContract ec = new EmployeeContract(Convert.ToDouble(drcontr[1]), Convert.ToDouble(drcontr[2]), Convert.ToDateTime(drcontr[3]), Convert.ToDateTime(drcontr[4]), drcontr[5].ToString(), Convert.ToBoolean(drcontr[6]), Convert.ToInt32(drcontr[0]), drcontr[7].ToString());
                        contracts.Add(ec);
                    }
                }
                dr.Close();


                foreach (EmployeeContract c in contracts)
                {
                    foreach (Employee e in employees)
                    {
                        if (e.EmployeeNumber == c.EmplNum)
                        {
                            e.employmentInfo.InitializeContractHistory(c);
                        }
                    }
                }
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
            return(employees);
        }
Пример #8
0
        public IEnumerable<Employee> SelEmployee(int EmployeeID = -1)
        {
            int intRC = 0;
            objEmployees = new List<Employee>();

            IParameterFactory objParams = new EmployeesParameterFactory(EmployeeID: EmployeeID, EmployeeName: EmployeeName);
            System.Data.SqlClient.SqlCommand objCmd = new SqlCommand("pSelEmployees", new ADOConnectionFactory().Connection);
            objCmd.CommandType = CommandType.StoredProcedure;

            objCmd.Parameters.Add(objParams.Parmeters["RC"]);
            objCmd.Parameters.Add(objParams.Parmeters["EmployeeID"]);

            try
            {
                objCmd.Connection.Open();
                System.Data.SqlClient.SqlDataReader objDR = objCmd.ExecuteReader();
                while (objDR.Read())
                {   
                    Employee objE = new Employee();
                    objE.intEmployeeID = (int)objDR["EmployeeID"];
                    objE.strEmployeeName = (string)objDR["EmployeeName"];
                    ((List<Employee>)objEmployees).Add(objE);                  
                }
                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 objEmployees;
        }