public static void GetAllEmployee() { employeePayrolls = new List <EmployeePayroll>(); try { using (connection) { var query = @"SELECT * FROM [dbo].[Employee] AS emp INNER JOIN [dbo].[Payroll] AS pay ON emp.Id = pay.Id" ; var sqlCommand = new SqlCommand(query, connection); connection.Open(); var dataReader = sqlCommand.ExecuteReader(); if (dataReader.HasRows) { while (dataReader.Read()) { var employeePayroll = new EmployeePayroll(); var employeeModel = employeePayroll.employeeModel; employeeModel.EmployeeID = dataReader.GetInt32(0); employeeModel.EmployeeName = dataReader.GetString(1); employeeModel.StartDate = dataReader.GetDateTime(6); employeeModel.Gender = Convert.ToChar(dataReader.GetString(5)); employeeModel.PhoneNumber = dataReader.GetString(3); employeeModel.Address = dataReader.GetString(2); employeeModel.Salary = dataReader.GetDouble(10); employeeModel.BasicPay = dataReader.GetDouble(11); employeeModel.TaxablePay = dataReader.GetDouble(13); employeeModel.Tax = dataReader.GetDouble(14); employeeModel.NetPay = dataReader.GetDouble(14); employeeModel.Display(); employeePayrolls.Add(employeePayroll); } } else { Console.WriteLine("No Data Found"); } dataReader.Close(); connection.Close(); } } catch (Exception ex) { Console.WriteLine(ex.StackTrace); throw new Exception(ex.Message); } finally { connection.Close(); } }
//SqlConnection connection = new SqlConnection(connectionString); 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.BasicPay = (double)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 = (double)dr.GetDecimal(8); employeePayroll.TaxablePay = (double)dr.GetDecimal(9); employeePayroll.Tax = (double)dr.GetDecimal(10); employeePayroll.NetPay = (double)dr.GetDecimal(11); //Display retrieved record - UC 2 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 GetAllEmployee() { try { EmployeePayroll employee = new EmployeePayroll(); using (this.connection) { string query = @"select empName,deptName,netPay,companyName from employee,department,employeeDepartment,payroll,company where employeeDepartment.empId = employee.empId AND department.deptId = employeeDepartment.deptId AND payroll.empId = employeeDepartment.empId;"; SqlCommand cmd = new SqlCommand(query, this.connection); this.connection.Open(); Console.WriteLine("\nConnection established with the database"); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { Console.WriteLine("\nReading Data Rows : "); while (dr.Read()) { employee.EmployeeName = dr.GetString(0); employee.Department = dr.GetString(1); employee.Country = dr.GetString(3); Console.WriteLine("\n" + employee.EmployeeName + " " + employee.Department + " " + employee.Country); } } else { Console.WriteLine("No data"); } dr.Close(); this.connection.Close(); } } catch (Exception e) { Console.WriteLine(e.Message); } finally { this.connection.Close(); } }
public bool AddEmployee(EmployeePayroll payroll) { try { using (this.connection) { SqlCommand command = new SqlCommand("AddNewEmployee", this.connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@Name", payroll.EmployeeName); command.Parameters.AddWithValue("@basic_pay", payroll.BasicPay); command.Parameters.AddWithValue("@start_Date", payroll.StartDate); command.Parameters.AddWithValue("@gender", payroll.Gender); command.Parameters.AddWithValue("@phone_no", 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(); } }
static void Main(string[] args) { Console.WriteLine("welcome to employee payroll"); EmployeeRepo repo = new EmployeeRepo(); EmployeePayroll employee = new EmployeePayroll(); employee.EmployeeName = "harish"; employee.StartDate = Convert.ToDateTime("2020-10-01"); employee.Gender = 'M'; employee.Address = "mumbai"; employee.PhoneNumber = "+91 9866528888"; string query = @"select * from Employee_payroll"; // repo.GetAllEmployee(query); // repo.UpdateEmployeeAddressUsingStoredProcedure("lokendra","HYD"); //repo.AddEmployeeDetailsUsingStoredProcedure(employee); //repo.RetrieveAllSalaries(); repo.RemoveAnEmployee(); }
public static void AddEmployee(EmployeePayroll employeePayroll) { var updatedEmployeePayroll = new EmployeePayroll(); try { SetConnection(); var sqlCommand = new SqlCommand("SpAddEmployeeDetails", connection); sqlCommand.CommandType = CommandType.StoredProcedure; sqlCommand.Parameters.AddWithValue("@EmployeeName", employeePayroll.employeeModel.EmployeeName); sqlCommand.Parameters.AddWithValue("@Salary", (float)employeePayroll.employeeModel.Salary); sqlCommand.Parameters.AddWithValue("@StartDate", employeePayroll.employeeModel.StartDate); sqlCommand.Parameters.AddWithValue("@Gender", employeePayroll.employeeModel.Gender); sqlCommand.Parameters.AddWithValue("@PhoneNumber", employeePayroll.employeeModel.PhoneNumber); sqlCommand.Parameters.AddWithValue("@Address", employeePayroll.employeeModel.Address); sqlCommand.Parameters.AddWithValue("@Department", employeePayroll.employeeModel.Department); connection.Open(); var reader = sqlCommand.ExecuteReader(); counter++; Console.WriteLine("Counter : {0}", counter); if (reader.Read()) { var model = new EmployeeModel { EmployeeName = reader.GetString(1) }; updatedEmployeePayroll.employeeModel = model; Console.WriteLine("Employee {0} Added Successfully.", updatedEmployeePayroll.employeeModel.EmployeeName); } } catch (Exception e) { Console.WriteLine(e.StackTrace); throw new Exception(e.Message); } finally { connection.Close(); } }
public void AddEmployeeWithThread(EmployeePayroll employee) { try { Task thread = new Task( () => { SqlCommand command = new SqlCommand("sqAddEmployeeSalaryDetails", this.connection); this.connection.Open(); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@name", employee.EmployeeName); command.Parameters.AddWithValue("@start_Date", employee.StartDate); command.Parameters.AddWithValue("@department", employee.Department); command.ExecuteNonQuery(); this.connection.Close(); } ); } catch (Exception exception) { Console.WriteLine(exception.Message); } }