public double UpdateEmployeeSalary(EmployeePayrollModel model) { try { using (this.connection) { SqlCommand command = new SqlCommand("SpUpdateSalary", connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@Empname", model.employee_name); command.Parameters.AddWithValue("@updateSalary", model.salary); this.connection.Open(); command.ExecuteNonQuery(); Console.WriteLine("Salary Updated Successfully !"); this.connection.Close(); } return(model.salary); } catch (Exception e) { throw new Exception(e.Message); } finally { this.connection.Close(); } }
public int GetData() { int result = 0; EmployeePayrollModel model = new EmployeePayrollModel(); using (this.connection) { SqlCommand command = new SqlCommand("RetrieveData", connection); command.CommandType = CommandType.StoredProcedure; connection.Open(); SqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { model.employee_id = reader.GetInt32(0); model.employee_name = reader.GetString(1); model.job_description = reader.GetString(2); model.salary = Convert.ToDouble(reader.GetDecimal(3)); model.joining_date = reader.GetDateTime(4); result++; Console.WriteLine("{0}, {1}, {2}, {3}, {4}", model.employee_id, model.employee_name, model.job_description, model.salary, model.joining_date); Console.WriteLine("\n"); } } reader.Close(); this.connection.Close(); } return(result); }
static void Main(string[] args) { Console.WriteLine("Welcome to Employee Payroll Project with ADO solved using TDD Approach"); EmployeePayroll employeePayroll = new EmployeePayroll(); //employeePayroll.GetData(); EmployeePayrollModel model = new EmployeePayrollModel(); model.employee_name = "Barkha"; model.salary = 300000.00; //employeePayroll.UpdateEmployeeSalary(model); //employeePayroll.GetEmployeeBetweenPerticularDateRange(); //employeePayroll.GetAggregateFunctionResult(); EmployeePayrollModel employee = new EmployeePayrollModel(); //employeePayroll.AddNewEmployee(employee); /*model.employee_id = 9; * model.employee_name = "Rama"; * model.job_description = "Construction"; * model.joining_date = new DateTime(2020, 01, 20); * model.salary = 450000.00; * model.geneder = "F";*/ model.employee_id = 6; employeePayroll.CheckEmployeeISActive(model); List <EmployeePayrollModel> employeeList = new List <EmployeePayrollModel>(); employeeList.Add(new EmployeePayrollModel(employee_id: 101, employee_name: "Simran", job_description: "Tech", salary: 300000.00, new DateTime(2019 - 10 - 11), geneder: "F", companyId: 1, departmentId: 2, is_employee_active: true)); employeeList.Add(new EmployeePayrollModel(employee_id: 102, employee_name: "Adesh", job_description: "Support", salary: 400000.00, new DateTime(2019 - 11 - 11), geneder: "M", companyId: 3, departmentId: 3, is_employee_active: true)); employeeList.Add(new EmployeePayrollModel(employee_id: 103, employee_name: "Sandesh", job_description: "Production", salary: 300000.00, new DateTime(2019 - 10 - 11), geneder: "M", companyId: 3, departmentId: 1, is_employee_active: true)); employeeList.Add(new EmployeePayrollModel(employee_id: 104, employee_name: "Ruchita", job_description: "Management", salary: 400000.00, new DateTime(2019 - 10 - 11), geneder: "F", companyId: 2, departmentId: 3, is_employee_active: true)); employeeList.Add(new EmployeePayrollModel(employee_id: 105, employee_name: "Suchita", job_description: "Management", salary: 400000.00, new DateTime(2019 - 10 - 11), geneder: "F", companyId: 1, departmentId: 2, is_employee_active: true)); employeeList.Add(new EmployeePayrollModel(employee_id: 106, employee_name: "Shital", job_description: "Tech", salary: 300000.00, new DateTime(2019 - 10 - 11), geneder: "F", companyId: 1, departmentId: 2, is_employee_active: true)); employeeList.Add(new EmployeePayrollModel(employee_id: 107, employee_name: "Akash", job_description: "Support", salary: 400000.00, new DateTime(2019 - 11 - 11), geneder: "M", companyId: 3, departmentId: 3, is_employee_active: true)); employeeList.Add(new EmployeePayrollModel(employee_id: 108, employee_name: "Lalit", job_description: "Production", salary: 300000.00, new DateTime(2019 - 10 - 11), geneder: "M", companyId: 3, departmentId: 1, is_employee_active: true)); employeeList.Add(new EmployeePayrollModel(employee_id: 109, employee_name: "Rekha", job_description: "Management", salary: 400000.00, new DateTime(2019 - 10 - 11), geneder: "F", companyId: 2, departmentId: 3, is_employee_active: true)); employeeList.Add(new EmployeePayrollModel(employee_id: 110, employee_name: "Rani", job_description: "Finance", salary: 400000.00, new DateTime(2019 - 10 - 11), geneder: "F", companyId: 1, departmentId: 1, is_employee_active: true)); EmployeePayrollOperations employeePayrollOperations = new EmployeePayrollOperations(); DateTime startDataTime = DateTime.Now; employeePayrollOperations.addEmplyeeToPayroll(employeeList); DateTime stopDateTime = DateTime.Now; Console.WriteLine("Duration for Insertion in List without thread : " + (stopDateTime - startDataTime)); DateTime startDataTimeThread = DateTime.Now; employeePayrollOperations.addEmplyeeToPayrollWithThread(employeeList); DateTime stopDateTimeThread = DateTime.Now; Console.WriteLine("Duration for Insertion in List with thread : " + (stopDateTimeThread - startDataTimeThread)); }
public bool AddNewEmployee(EmployeePayrollModel model) { try { SqlCommand command = new SqlCommand("InsertInto", this.connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@EmpId", model.employee_id); command.Parameters.AddWithValue("@EmpName", model.employee_name); command.Parameters.AddWithValue("@JobDescription", model.job_description); command.Parameters.AddWithValue("@Salary", model.salary); command.Parameters.AddWithValue("@JoiningDate", model.joining_date); command.Parameters.AddWithValue("@Geneder", model.geneder); command.Parameters.AddWithValue("@CompanyId", model.companyId); command.Parameters.AddWithValue("@DepartmentId", model.departmentId); command.Parameters.AddWithValue("@IsActive", model.is_employee_active); this.connection.Open(); command.ExecuteNonQuery(); this.connection.Close(); int employee_id = model.employee_id; double deduction = model.salary * 0.2; double taxable_pay = model.salary - deduction; double tax = taxable_pay * 0.1; double net_salary = model.salary - tax; SqlCommand sqlCommand = new SqlCommand("InsertIntoAlongWithSalaryDetails", connection); sqlCommand.CommandType = CommandType.StoredProcedure; sqlCommand.Parameters.AddWithValue("@empId", (model.employee_id)); sqlCommand.Parameters.AddWithValue("@deduction", (model.salary * 0.2)); sqlCommand.Parameters.AddWithValue("@taxable_pay", (model.salary - deduction)); sqlCommand.Parameters.AddWithValue("@tax", (taxable_pay * 0.1)); sqlCommand.Parameters.AddWithValue("@net_salary", (model.salary - tax)); this.connection.Open(); var result1 = sqlCommand.ExecuteNonQuery(); this.connection.Close(); if (result1 == 0) { return(false); } return(true); } catch (Exception e) { throw new Exception(e.Message); } finally { this.connection.Close(); } }
public int CheckEmployeeISActive(EmployeePayrollModel model) { int count = 0; using (this.connection) { SqlCommand command = new SqlCommand("EmployeeIsActive", connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@EmpId", model.employee_id); connection.Open(); SqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { model.employee_id = reader.GetInt32(0); Console.WriteLine("Emplyee With Id {0} is Set to Inactive Now", model.employee_id); Console.WriteLine("\n"); } } reader.Close(); this.connection.Close(); //Query to retrieve only active employee from db string query = @"select * from employee_payroll where is_active = 'true'"; SqlCommand selectCommand = new SqlCommand(query, connection); connection.Open(); SqlDataReader retrieveReader = selectCommand.ExecuteReader(); if (retrieveReader.HasRows) { while (retrieveReader.Read()) { model.employee_id = retrieveReader.GetInt32(0); model.is_employee_active = retrieveReader.GetBoolean(8); count++; Console.WriteLine("{0}, {1}", model.employee_id, model.is_employee_active); Console.WriteLine("\n"); } } retrieveReader.Close(); this.connection.Close(); } //This Count returns number of active employee from database return(count); }
public int GetEmployeeBetweenPerticularDateRange() { int result = 0; try { EmployeePayrollModel model = new EmployeePayrollModel(); using (this.connection) { string query = @"select count(employee_id) from employee_payroll where joining_date between CAST('2018-12-25' as date) AND GETDATE();"; SqlCommand command = new SqlCommand(query, connection); connection.Open(); result = (int)command.ExecuteScalar(); SqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { model.employee_id = reader.GetInt32(0); Console.WriteLine("{0}", model.employee_id); Console.WriteLine("\n"); } } reader.Close(); this.connection.Close(); } return(result); } catch (Exception e) { throw new Exception(e.Message); } finally { this.connection.Close(); } }
/// <summary> /// Method to add employee to the employee list /// </summary> /// <param name="emp"></param> private void addEmplyeePayroll(EmployeePayrollModel emp) { employeeList.Add(emp); }
public List <string> GetAggregateFunctionResult() { List <string> str = new List <string>(); try { EmployeePayrollModel employeeModel = new EmployeePayrollModel(); using (this.connection) { using (SqlCommand command = new SqlCommand( @"SELECT SUM(salary) FROM employee_payroll WHERE gender = 'F' GROUP BY gender; SELECT MIN(salary) FROM employee_payroll WHERE gender = 'F' GROUP BY gender; SELECT MAX(salary) FROM employee_payroll WHERE gender = 'F' GROUP BY gender; SELECT COUNT(employee_id) FROM employee_payroll WHERE gender = 'F'; SELECT SUM(salary) FROM employee_payroll WHERE gender = 'M' GROUP BY gender; SELECT AVG(salary) FROM employee_payroll WHERE gender = 'M' GROUP BY gender; SELECT COUNT(employee_id) FROM employee_payroll WHERE gender = 'M';", connection)) { connection.Open(); using (SqlDataReader reader = command.ExecuteReader()) { Console.WriteLine("\n---------------Aggregate Function Operation on Female Employee---------------"); while (reader.Read()) { employeeModel.salary = Convert.ToDouble(reader.GetDecimal(0)); str.Add(employeeModel.salary.ToString()); Console.WriteLine("Overall Sum of Basic Pay of Female Employee is : {0}", employeeModel.salary); } if (reader.NextResult()) { while (reader.Read()) { employeeModel.salary = Convert.ToDouble(reader.GetDecimal(0)); str.Add(employeeModel.salary.ToString()); Console.WriteLine("Minimum of Basic Pay of Female Employee is : {0}", employeeModel.salary); } } if (reader.NextResult()) { while (reader.Read()) { employeeModel.salary = Convert.ToDouble(reader.GetDecimal(0)); str.Add(employeeModel.salary.ToString()); Console.WriteLine("Maximum of Basic Pay of Female Employee is : {0}", employeeModel.salary); } } if (reader.NextResult()) { while (reader.Read()) { employeeModel.employee_id = reader.GetInt32(0); Console.WriteLine("Number of Female Employee present : {0}", employeeModel.employee_id); } } Console.WriteLine("\n---------------Aggregate Function Operation on Male Employee---------------"); if (reader.NextResult()) { while (reader.Read()) { employeeModel.salary = Convert.ToDouble(reader.GetDecimal(0)); str.Add(employeeModel.salary.ToString()); Console.WriteLine("Overall Sum of Basic Pay of Male Employee is : {0}", employeeModel.salary); } } if (reader.NextResult()) { while (reader.Read()) { employeeModel.salary = Convert.ToDouble(reader.GetDecimal(0)); str.Add(employeeModel.salary.ToString()); Console.WriteLine("Average of Basic Pay of Male Employee is : {0}", employeeModel.salary); } } if (reader.NextResult()) { while (reader.Read()) { employeeModel.employee_id = reader.GetInt32(0); Console.WriteLine("Number of Male Employee present : {0}", employeeModel.employee_id); } } } } } return(str); } catch (Exception e) { throw new Exception(e.Message); } finally { this.connection.Close(); } }