/// <summary>
/// Gets the employee details.
/// --UC2 Fetching the employee records from the database
/// </summary>
public void GetEmployeeDetails()
{
//Establishing connection
DBConnection con = new DBConnection();
connection = con.GetConnection();
//Creating model employee to map the details
EmployeeModel model = new EmployeeModel();
try
{
using (connection)
{
//Query to get the entries of the table
string query = @"select * from dbo.employeepayroll";
//COnverting to sqlcommand text
SqlCommand command = new SqlCommand(query, connection);
//Opening the connection to database
connection.Open();
//Executing the command and reading the files by executing query
SqlDataReader reader = command.ExecuteReader();
//Checking for null values in the row and if not then get details
if (reader.HasRows)
{
while (reader.Read())
{
//mapping the details as per entry
model.id = reader.GetInt32(0);
model.name = reader.GetString(1);
model.start = reader.GetDateTime(2);
model.gender = reader.GetString(3);
model.phone_number = reader.GetString(4);
model.address = reader.GetString(5);
model.department = reader.GetString(6);
model.basic_pay = reader.GetDouble(7);
model.deductions = reader.GetInt32(8);
model.taxable_pay = reader.GetDouble(9);
model.income_tax = reader.GetDouble(10);
model.net_pay = reader.GetDouble(11);
Console.WriteLine(model.id + " " + model.name + " " + model.start + " " + model.gender + " "
+ model.phone_number + " " + model.address + " " + model.department + " " + model.basic_pay + " " + model.deductions + " "
+ model.taxable_pay + " " + model.income_tax + " " + model.net_pay + " ");
}
}
else
{
Console.WriteLine("NO DATA FOUND");
}
reader.Close();
}
}
//Catching exception in case if any discrepancy
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
//closing the connection
finally
{
connection.Close();
}
}
public void GetEmployeeDetailsJoiningBetweenDate(DateTime date)
{
//Establishing connection
DBConnection con = new DBConnection();
connection = con.GetConnection();
try
{
/// Using the connection established
using (connection)
{
/// Query to get the data from the table
string query = @"select * from dbo.employeepayroll where start between CAST(@parameter as date)
and CAST(getdate() as date)";
/// Impementing the command on the connection fetched database table
SqlCommand command = new SqlCommand(query, connection);
/// Binding the parameter to the formal parameters
command.Parameters.AddWithValue("@parameter", date);
/// Opening the connection to start mapping
connection.Open();
/// executing the sql data reader to fetch the records
SqlDataReader reader = command.ExecuteReader();
/// executing for not null
if (reader.HasRows)
{
EmployeeModel model = new EmployeeModel();
/// Moving to the next record from the table
/// Mapping the data to the employee model class object
while (reader.Read())
{
model.id = reader.GetInt32(0);
model.name = reader.GetString(1);
model.start = reader.GetDateTime(2);
model.gender = reader.GetString(3);
model.phone_number = reader.GetString(4);
model.address = reader.GetString(5);
model.department = reader.GetString(6);
model.basic_pay = reader.GetDouble(7);
model.deductions = reader.GetInt32(8);
model.taxable_pay = reader.GetDouble(9);
model.income_tax = reader.GetDouble(10);
model.net_pay = reader.GetDouble(11);
Console.WriteLine(model.id + " " + model.name + " " + model.start + " " + model.gender + " "
+ model.phone_number + " " + model.address + " " + model.department + " " + model.basic_pay + " " + model.deductions + " "
+ model.taxable_pay + " " + model.income_tax + " " + model.net_pay + " ");
}
}
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
{
connection.Close();
}
}