public void AggregateFunctionOperations()
{
try
{
EmployeeModel employeeModel = new EmployeeModel();
using (this.connection)
{
using (SqlCommand command = new SqlCommand(
@"SELECT SUM(basic_pay) FROM employee_payroll WHERE gender = 'F' GROUP BY gender;
SELECT MIN(basic_pay) FROM employee_payroll WHERE gender = 'F' GROUP BY gender;
SELECT MAX(basic_pay) FROM employee_payroll WHERE gender = 'F' GROUP BY gender;
SELECT COUNT(id) FROM employee_payroll WHERE gender = 'F';
SELECT SUM(basic_pay) FROM employee_payroll WHERE gender = 'M' GROUP BY gender;
SELECT AVG(basic_pay) FROM employee_payroll WHERE gender = 'M' GROUP BY gender;
SELECT COUNT(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.basic_pay = Convert.ToDouble(reader.GetDecimal(0));
Console.WriteLine("Overall Sum of Basic Pay of Female Employee is : {0}", employeeModel.basic_pay);
}
if (reader.NextResult())
{
while (reader.Read())
{
employeeModel.basic_pay = Convert.ToDouble(reader.GetDecimal(0));
Console.WriteLine("Minimum of Basic Pay of Female Employee is : {0}", employeeModel.basic_pay);
}
}
if (reader.NextResult())
{
while (reader.Read())
{
employeeModel.basic_pay = Convert.ToDouble(reader.GetDecimal(0));
Console.WriteLine("Maximum of Basic Pay of Female Employee is : {0}", employeeModel.basic_pay);
}
}
if (reader.NextResult())
{
while (reader.Read())
{
employeeModel.id = reader.GetInt32(0);
Console.WriteLine("Number of Male Employee present : {0}", employeeModel.id);
}
}
Console.WriteLine("\n---------------Aggregate Function Operation on Male Employee---------------");
if (reader.NextResult())
{
while (reader.Read())
{
employeeModel.basic_pay = Convert.ToDouble(reader.GetDecimal(0));
Console.WriteLine("Overall Sum of Basic Pay of Male Employee is : {0}", employeeModel.basic_pay);
}
}
if (reader.NextResult())
{
while (reader.Read())
{
employeeModel.basic_pay = Convert.ToDouble(reader.GetDecimal(0));
Console.WriteLine("Average of Basic Pay of Male Employee is : {0}", employeeModel.basic_pay);
}
}
if (reader.NextResult())
{
while (reader.Read())
{
employeeModel.id = reader.GetInt32(0);
Console.WriteLine("Number of Male Employee present : {0}", employeeModel.id);
}
}
}
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
this.connection.Close();
}
}
public void GetPerticularEmployeeData()
{
try
{
EmployeeModel employeeModel = new EmployeeModel();
using (this.connection)
{
//Get Basic Pay for Perticular Employee
string queryToViewBasicPayOfPerticularEmp = @"SELECT basic_pay FROM employee_payroll WHERE name = 'Abby'; ";
SqlCommand cmd = new SqlCommand(queryToViewBasicPayOfPerticularEmp, this.connection);
this.connection.Open();
Console.WriteLine("\nDatabased Connection OK !");
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
employeeModel.basic_pay = Convert.ToDouble(dr.GetDecimal(0));
Console.WriteLine("Basic Pay for Abby is : {0}", employeeModel.basic_pay);
Console.WriteLine("\n");
}
}
else
{
Console.WriteLine("No Data Found");
}
dr.Close();
this.connection.Close();
//Get List of Employee who Joined between perticular range of date
string queryToViewEmployeeBetweenDate = @"SELECT * FROM employee_payroll WHERE start_date BETWEEN CAST('2017-01-30' as date) AND GETDATE(); ";
SqlCommand command2 = new SqlCommand(queryToViewEmployeeBetweenDate, this.connection);
this.connection.Open();
SqlDataReader dataReader = command2.ExecuteReader();
if (dataReader.HasRows)
{
Console.WriteLine("Following is list of Employee who joined between Date: 2017-01-30 And 2020-12-29");
Console.WriteLine("\n");
while (dataReader.Read())
{
employeeModel.id = dataReader.GetInt32(0);
employeeModel.name = dataReader.GetString(1);
employeeModel.basic_pay = Convert.ToDouble(dataReader.GetDecimal(2));
employeeModel.start_date = dataReader.GetDateTime(3);
employeeModel.gender = Convert.ToChar(dataReader.GetString(4));
employeeModel.address = dataReader.GetString(5);
employeeModel.phone_number = dataReader.GetString(6);
employeeModel.department = dataReader.GetString(7);
employeeModel.deduction = Convert.ToDouble(dataReader.GetDecimal(8));
employeeModel.taxable_pay = Convert.ToDouble(dataReader.GetDecimal(9));
employeeModel.income_tax = Convert.ToDouble(dataReader.GetDecimal(10));
employeeModel.net_pay = Convert.ToDouble(dataReader.GetDecimal(11));
Console.WriteLine("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}", employeeModel.id, employeeModel.name, employeeModel.basic_pay, employeeModel.start_date, employeeModel.gender, employeeModel.address,
employeeModel.phone_number, employeeModel.department, employeeModel.deduction, employeeModel.taxable_pay, employeeModel.income_tax, employeeModel.net_pay);
Console.WriteLine("\n");
}
}
else
{
Console.WriteLine("No Data Found");
}
dataReader.Close();
this.connection.Close();
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
this.connection.Close();
}
}
