/// <summary>
/// On finding given date range on new table
/// Gets the employee data with give range on new table.
/// </summary>
/// <exception cref="Exception"></exception>
public void GetEmployeeDataWithGiveRangeOnNewTable()
{
try
{
/// Creating a references of employee model class
EmployeeModel employeeModel = new EmployeeModel();
using (this.sqlConnection)
{
/// Writing sql query
string query = "select emp.Id, emp.Name, emp.Start, emp.Gender, emp.Address, emp.DepartmentNumber, emp.PhoneNumber," +
"depart.Department, depart.Location" +
"sal.SalaryId, sal.BasicSalaryPay, Sal.Deductions, sal.Taxable_Pay, sal.Income_Tax, sal.Net_Pay" +
"from EmployeePayroll emp, Department depart, Salary sal " +
"where emp.DepartmentNumber = depart.DepartmentNumber and emp.Id = sal.Id and" +
"Start between cast('2019-01-01' as date) and SYSDATETIME()";
SqlCommand command = new SqlCommand(query, this.sqlConnection);
/// Opening connection
this.sqlConnection.Open();
/// Executing the sql query
SqlDataReader dataReader = command.ExecuteReader();
/// If not null
/// Read all data form database
if (dataReader.HasRows)
{
while (dataReader.Read())
{
employeeModel.Id = dataReader.GetInt32(0);
employeeModel.Name = dataReader.GetString(1);
employeeModel.Start = dataReader.GetDateTime(3);
employeeModel.Gender = Convert.ToChar(dataReader.GetString(4));
employeeModel.Address = dataReader.GetString(7);
employeeModel.DepartmentNumber = dataReader.GetInt32(5);
employeeModel.PhoneNumber = dataReader.GetString(6);
employeeModel.Department = dataReader.GetString(6);
employeeModel.Location = dataReader.GetString(6);
employeeModel.SalaryId = dataReader.GetInt32(7);
employeeModel.Basic_Pay = dataReader.GetDecimal(8);
employeeModel.Deductions = dataReader.GetDecimal(9);
employeeModel.Taxable_Pay = dataReader.GetDecimal(10);
employeeModel.Income_Tax = dataReader.GetDecimal(11);
employeeModel.Net_Pay = dataReader.GetDecimal(12);
Console.WriteLine(employeeModel.Id + " , " + employeeModel.Name + " , " + employeeModel.Start + " , " + employeeModel.Gender + " , " +
employeeModel.Address + " , " + employeeModel.DepartmentNumber + " , " + employeeModel.PhoneNumber + " , " + employeeModel.Department + " , " +
employeeModel.Location + " , " + employeeModel.Basic_Pay + " , " + employeeModel.Deductions + " , " + employeeModel.Taxable_Pay + " , " + employeeModel.Income_Tax + " , " + employeeModel.Net_Pay);
Console.WriteLine();
}
}
else
{
Console.WriteLine("No data found");
}
dataReader.Close();
/// Closing connection
this.sqlConnection.Close();
}
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
}
/// <summary>
/// Adds the employee payroll.
/// </summary>
/// <param name="employeeModel">The employee model.</param>
public void AddEmployeePayroll(EmployeeModel employeeModel)
{
employeePayrollModelList.Add(employeeModel);
}
