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