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(); } }
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); }