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