public void GetAllEmployee() { try { EmployeePayroll employeePayroll = new EmployeePayroll(); using (this.connection) { string query = @"SELECT * FROM employee_payroll;"; //define SqlCommand Object SqlCommand cmd = new SqlCommand(query, this.connection); this.connection.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { employeePayroll.employeeId = dr.GetInt32(0); employeePayroll.employeeName = dr.GetString(1); employeePayroll.basic_pay = dr.GetDecimal(2); employeePayroll.startDate = dr.GetDateTime(3); employeePayroll.Gender = dr.GetString(4); employeePayroll.phoneNumber = dr.GetString(5); employeePayroll.address = dr.GetString(6); employeePayroll.department = dr.GetString(7); employeePayroll.deductions = dr.GetDecimal(8); employeePayroll.taxablePay = dr.GetDecimal(9); employeePayroll.tax = dr.GetDecimal(10); employeePayroll.netPay = dr.GetDecimal(11); //Display retrieved record Console.WriteLine("{0},{1},{2},{3},{4},{5}", employeePayroll.employeeId, employeePayroll.employeeName, employeePayroll.phoneNumber, employeePayroll.address, employeePayroll.department, employeePayroll.Gender, employeePayroll.phoneNumber); Console.WriteLine("\n"); } } else { Console.WriteLine("No data found!"); } dr.Close(); this.connection.Close(); } } catch (Exception e) { throw new Exception(e.Message); } finally { this.connection.Close(); } }
public void UsingDatabaseFunction(EmployeePayroll payroll) { try { DatabaseFunction df = new DatabaseFunction(); connection = new SqlConnection(connectionString); string queryDb = @"SELECT gender,COUNT(basic_pay) AS TotalCount,SUM(basic_pay) AS TotalSum, AVG(basic_pay) AS AverageValue, MIN(basic_pay) AS MinValue, MAX(basic_pay) AS MaxValue FROM employee_payroll WHERE gender = 'F' GROUP BY gender;"; //define SqlCommand Object SqlCommand cmd = new SqlCommand(queryDb, this.connection); this.connection.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { df.gender = Convert.ToString(dr["gender"]); df.count = Convert.ToInt32(dr["TotalCount"]); df.totalSum = Convert.ToDecimal(dr["TotalSum"]); df.avg = Convert.ToDecimal(dr["AverageValue"]); df.min = Convert.ToDecimal(dr["MinValue"]); df.max = Convert.ToDecimal(dr["MaxValue"]); Console.WriteLine("Gender: {0}, TotalCount: {1}, TotalSalary: {2}, AvgSalary: {3}, MinSalary: {4}, MinSalary: {5}", df.gender, df.count, df.totalSum, df.avg, df.min, df.max); } } else { Console.WriteLine("Rows doesn't exist!"); } dr.Close(); } catch (Exception e) { Console.WriteLine(e.Message); connection.Close(); } finally { connection.Close(); } Console.WriteLine(); }
public int AddEmployeeToPayroll(PayrollModel payrollModel, EmployeePayroll employeePayroll) { int emp_ID = 0; try { using (this.connection) { SqlCommand command = new SqlCommand("spAddEmployeePayroll", this.connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@employee_name", employeePayroll.employeeName); command.Parameters.AddWithValue("@phoneno", employeePayroll.phoneNumber); command.Parameters.AddWithValue("@address", employeePayroll.address); command.Parameters.AddWithValue("@gender", employeePayroll.Gender); command.Parameters.AddWithValue("@basic_pay", payrollModel.BasicPay); command.Parameters.AddWithValue("@deduction", payrollModel.Deductions); command.Parameters.AddWithValue("@income_tax", payrollModel.IncomeTax); command.Parameters.AddWithValue("@start_date", DateTime.Now); this.connection.Open(); int result = command.ExecuteNonQuery(); if (result != 0) { string query = @"SELECT MAX(employee_id) FROM Employee;"; SqlCommand cmd = new SqlCommand(query, this.connection); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { employeePayroll.employeeId = dr.GetInt32(0); emp_ID = employeePayroll.employeeId; } } } return(emp_ID); } } catch (Exception e) { throw new Exception(e.Message); } finally { this.connection.Close(); } }
public void RetrieveEmployeeBasedOnStartDate(EmployeePayroll payroll) { try { connection = new SqlConnection(connectionString); SqlCommand command = new SqlCommand("spRetrieveEmployeeBasedOnStartDate", connection) { CommandType = CommandType.StoredProcedure }; command.Parameters.AddWithValue("@sdate", payroll.startDate); command.Parameters.AddWithValue("@edate", payroll.endDate); connection.Open(); SqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { payroll.employeeId = reader.GetInt32(0); payroll.employeeName = reader.GetString(1); payroll.address = reader.GetString(2); payroll.department = reader.GetString(3); payroll.basic_pay = reader.GetDecimal(4); payroll.startDate = reader.GetDateTime(5); payroll.endDate = reader.GetDateTime(6); Console.WriteLine("{0},{1},{2},{3},{4},{5},{6}", payroll.employeeId, payroll.employeeName, payroll.address, payroll.department, payroll.basic_pay, payroll.startDate, payroll.endDate); } } else { Console.WriteLine("Rows doesn't exist!"); } reader.Close(); } catch (Exception e) { Console.WriteLine(e.Message); connection.Close(); } finally { connection.Close(); } }
private static void Main(string[] args) { Console.WriteLine("Sql database connectivity!"); EmployeeRepo repo = new EmployeeRepo(); EmployeePayroll employeePayroll = new EmployeePayroll(); employeePayroll.employeeName = "John"; employeePayroll.basic_pay = 20000; employeePayroll.startDate = DateTime.Now; employeePayroll.Gender = "M"; employeePayroll.phoneNumber = "7677123446"; employeePayroll.address = "Bangur Rd"; employeePayroll.department = "HR"; employeePayroll.deductions = 1000; employeePayroll.taxablePay = 19000; employeePayroll.tax = 1900; employeePayroll.netPay = 18100; Console.WriteLine("Displaying All Data........"); //repo.AddEmployee(employeePayroll); Console.WriteLine("Retrieving Employee from date range......."); employeePayroll.startDate = DateTime.Parse("2020-09-05"); employeePayroll.endDate = DateTime.Parse("2021-06-20"); repo.RetrieveEmployeeBasedOnStartDate(employeePayroll); Console.WriteLine("Retrieving Sum Avg Min Max from Employee"); repo.UsingDatabaseFunction(employeePayroll); PayrollModel payroll = new PayrollModel(); employeePayroll.employeeName = "Sunny"; employeePayroll.phoneNumber = "7521369845"; employeePayroll.address = "SanDiago"; employeePayroll.Gender = "M"; payroll.BasicPay = 5000.00; payroll.Deductions = 500; payroll.IncomeTax = 200; repo.AddEmployeeToPayroll(payroll, employeePayroll); }
public bool AddEmployee(EmployeePayroll payroll) { try { using (this.connection) { SqlCommand command = new SqlCommand("spAddEmployeeDetail", this.connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@name", payroll.employeeName); command.Parameters.AddWithValue("@basic_pay", payroll.basic_pay); command.Parameters.AddWithValue("@start_date", payroll.startDate); command.Parameters.AddWithValue("@Gender", payroll.Gender); command.Parameters.AddWithValue("@phonenumber", payroll.phoneNumber); command.Parameters.AddWithValue("@address", payroll.address); command.Parameters.AddWithValue("@department", payroll.department); command.Parameters.AddWithValue("@Deductions", payroll.deductions); command.Parameters.AddWithValue("@taxable_pay", payroll.taxablePay); command.Parameters.AddWithValue("@income_tax", payroll.tax); command.Parameters.AddWithValue("@net_pay", payroll.netPay); connection.Open(); var result = command.ExecuteNonQuery(); connection.Close(); if (result != 0) { return(true); } return(false); } } catch (Exception e) { Console.WriteLine(e.Message); return(false); } finally { connection.Close(); } }