/// <summary>
/// UC5 -- Function to execute the query to print the detail of the employee joining between a given date and current date
/// </summary>
/// <param name="date"></param>
public void GetDetailOfEmployeeStartingBetweenDate(DateTime date)
{
/// Creates a new connection for every method to avoid "ConnectionString property not initialized" exception
DBConnection dbc = new DBConnection();
/// Calling the Get connection method to establish the connection to the Sql Server
connectionToServer = dbc.GetConnection();
try
{
/// Using the connection established
using (connectionToServer)
{
/// Query to get the data from the table
string query = @"select * from dbo.employee_payroll_services where StartDate between CAST(@parameter as date)
and CAST(getdate() as date)";
/// Impementing the command on the connection fetched database table
SqlCommand command = new SqlCommand(query, connectionToServer);
/// Binding the parameter to the formal parameters
command.Parameters.AddWithValue("@parameter", date);
/// Opening the connection to start mapping
connectionToServer.Open();
/// executing the sql data reader to fetch the records
SqlDataReader reader = command.ExecuteReader();
/// executing for not null
if (reader.HasRows)
{
EmployeeModel employeeObject = new EmployeeModel();
/// Moving to the next record from the table
/// Mapping the data to the employee model class object
while (reader.Read())
{
employeeObject.EmployeeID = reader.GetInt32(0);
employeeObject.EmployeeName = reader.GetString(1);
employeeObject.BasicPay = reader.GetDouble(2);
employeeObject.StartDate = reader.GetDateTime(3);
employeeObject.PhoneNumber = reader.GetInt64(4);
employeeObject.Address = reader.GetString(5);
employeeObject.Department = reader.GetString(6);
employeeObject.Gender = reader.GetString(7);
employeeObject.Deductions = reader.GetDouble(8);
employeeObject.TaxablePay = reader.GetDouble(9);
employeeObject.Tax = reader.GetDouble(10);
employeeObject.NetPay = reader.GetDouble(11);
Console.WriteLine("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11}",
employeeObject.EmployeeID, employeeObject.EmployeeName, employeeObject.Gender,
employeeObject.Address, employeeObject.BasicPay, employeeObject.StartDate,
employeeObject.PhoneNumber, employeeObject.Address, employeeObject.Department,
employeeObject.Deductions, employeeObject.TaxablePay, employeeObject.Tax, employeeObject.NetPay);
Console.WriteLine("\n");
}
}
else
{
Console.WriteLine("No data found");
}
reader.Close();
}
}
/// Catching any type of exception generated during the run time
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
connectionToServer.Close();
}
}
/// <summary>
/// UC2-- Getting all the stored records in the employee payroll services table by fetching all the records
/// </summary>
public void GetAllEmployeesRecords()
{
/// Creates a new connection for every method to avoid "ConnectionString property not initialized" exception
DBConnection dbc = new DBConnection();
/// Calling the Get connection method to establish the connection to the Sql Server
connectionToServer = dbc.GetConnection();
/// Creating the employee model class object
EmployeeModel employeeObject = new EmployeeModel();
try
{
using (connectionToServer)
{
/// Query to get all the data from the table
string query = @"select * from dbo.employee_payroll_services";
/// Impementing the command on the connection fetched database table
SqlCommand command = new SqlCommand(query, connectionToServer);
/// Opening the connection to start mapping
connectionToServer.Open();
/// executing the sql data reader to fetch the records
SqlDataReader reader = command.ExecuteReader();
/// executing for not null
if (reader.HasRows)
{
/// Moving to the next record from the table
/// Mapping the data to the employee model class object
while (reader.Read())
{
employeeObject.EmployeeID = reader.GetInt32(0);
employeeObject.EmployeeName = reader.GetString(1);
employeeObject.BasicPay = reader.GetDouble(2);
employeeObject.StartDate = reader.GetDateTime(3);
employeeObject.PhoneNumber = reader.GetInt64(4);
employeeObject.Address = reader.GetString(5);
employeeObject.Department = reader.GetString(6);
employeeObject.Gender = reader.GetString(7);
employeeObject.Deductions = reader.GetDouble(8);
employeeObject.TaxablePay = reader.GetDouble(9);
employeeObject.Tax = reader.GetDouble(10);
employeeObject.NetPay = reader.GetDouble(11);
Console.WriteLine("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11}",
employeeObject.EmployeeID, employeeObject.EmployeeName, employeeObject.Gender,
employeeObject.Address, employeeObject.BasicPay, employeeObject.StartDate,
employeeObject.PhoneNumber, employeeObject.Address, employeeObject.Department,
employeeObject.Deductions, employeeObject.TaxablePay, employeeObject.Tax, employeeObject.NetPay);
Console.WriteLine("\n");
}
}
else
{
Console.WriteLine("No data found");
}
reader.Close();
}
}
/// Catching the null record exception
catch (Exception ex)
{
throw new Exception(ex.Message);
}
/// Alway ensuring the closing of the connection
finally
{
connectionToServer.Close();
}
}
/// <summary>
/// Defines the entry point of the application.
/// </summary>
/// <param name="args">The arguments.</param>
static void Main(string[] args)
{
int choice;
Console.WriteLine("Welcome to Employee Payroll Service");
EmployeeRepo repo = new EmployeeRepo();
do
{
Console.WriteLine("1.Get all records.\n2.Add new Employee.\n3.Update salary.\n4.Get Employeesby Hire Date." +
"\n5.Get Aggregate Salary Details By Gender.\n6.Delete record.\n7.Exit.");
choice = Convert.ToInt32(Console.ReadLine());
switch (choice)
{
case 1:
repo.GetAllEmployeeRecord();
break;
case 2:
EmployeeModel employee = new EmployeeModel();
employee.Name = "Robert Shaw";
employee.start_date = Convert.ToDateTime("12-08-2018");
employee.Gender = "F";
employee.EmployeeAddress = "Brooklyn";
employee.Department = "Delivery";
employee.PhoneNumber = "7895478596";
employee.Basic_Pay = 10000;
employee.Deductions = 500;
employee.Taxable_Pay = 500;
employee.Income_Tax = 500;
employee.Net_Pay = 11500;
var record = repo.AddEmployee(employee);
Console.WriteLine("Record added successfully: " + record);
break;
case 3:
Console.WriteLine("Enter employee name");
string name = Console.ReadLine();
Console.WriteLine("Enter new salary");
decimal salary = Convert.ToDecimal(Console.ReadLine());
repo.UpdateEmployeeSalary(name, salary);
Console.WriteLine("Salary updated successfully");
break;
case 4:
repo.GetAllemployeeStartedInADateRange();
break;
case 5:
repo.GetAggregateSalaryDetailsByGender();
break;
case 6:
Console.WriteLine("Enter Employee Id");
int id = Convert.ToInt32(Console.ReadLine());
repo.RemoveEmployee(id);
break;
case 7:
Console.WriteLine("Thank you for using Employee Payroll System.");
break;
}
}while (choice != 7);
Console.ReadKey();
}