/// <summary>
/// UC2 Ability for Employee Payroll Service to retrieve the Employee Payroll from the Database
/// </summary>
public void GetAllEmployeeData(string query)
{
/// Creates a new connection for every method to avoid "ConnectionString property not initialized" exception
DBConnection dbc = new DBConnection();
connection = dbc.GetConnection();
//Creating Employee model class object
EmployeeModel employee = new EmployeeModel();
try
{
using (connection)
{
//Opening the connection to the statrt mapping.
connection.Open();
//Implementing the command on the connection fetched database table.
SqlCommand command = new SqlCommand(query, connection);
//Executing the Sql datareaeder to fetch the all records.
SqlDataReader dataReader = command.ExecuteReader();
//Checking datareader has rows or not.
if (dataReader.HasRows)
{
//using while loop for read multiple rows.
// Mapping the data to the employee model class object.
while (dataReader.Read())
{
employee.EmployeeId = dataReader.GetInt32(0);
employee.EmployeeName = dataReader.GetString(1);
employee.BasicPay = dataReader.GetDouble(2);
employee.StartDate = dataReader.GetDateTime(3);
employee.Gender = dataReader.GetString(4);
employee.PhoneNumber = dataReader.GetInt64(5);
employee.Department = dataReader.GetString(6);
employee.Address = dataReader.GetString(7);
employee.Deductions = dataReader.GetDouble(8);
employee.TaxablePay = dataReader.GetDouble(9);
employee.Tax = dataReader.GetDouble(10);
employee.NetPay = dataReader.GetDouble(11);
Console.WriteLine("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11}", employee.EmployeeId, employee.EmployeeName,
employee.Gender, employee.Address, employee.BasicPay, employee.StartDate, employee.PhoneNumber, employee.Address,
employee.Department, employee.Deductions, employee.TaxablePay, employee.Tax, employee.NetPay);
Console.WriteLine("\n");
}
}
else
{
Console.WriteLine("no data found ");
}
dataReader.Close();
}
}
/// Catching the null record exception
catch (Exception ex)
{
throw new Exception(ex.Message);
}
//Always ensuring the closing of the connection
finally
{
connection.Close();
}
}
/// <summary>
/// UC7 Inserts data into multiple tables using transactions.
/// </summary>
public void InsertIntoMultipleTablesWithTransactions()
{
DBConnection dbc = new DBConnection();
connection = dbc.GetConnection();
Console.WriteLine("Enter EmployeeID");
int empID = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Enter Name:");
string empName = Console.ReadLine();
DateTime startDate = DateTime.Now;
Console.WriteLine("Enter Address:");
string address = Console.ReadLine();
Console.WriteLine("Enter Gender:");
string gender = Console.ReadLine();
Console.WriteLine("Enter PhoneNumber:");
double phonenumber = Convert.ToDouble(Console.ReadLine());
Console.WriteLine("Enter BasicPay:");
int basicPay = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Enter Deductions:");
int deductions = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Enter TaxablePay:");
int taxablePay = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Enter Tax:");
int tax = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Enter NetPay:");
int netPay = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Enter CompanyId:");
int companyId = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Enter CompanyName:");
string companyName = Console.ReadLine();
Console.WriteLine("Enter DeptId:");
int deptId = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Enter DeptName:");
string deptName = Console.ReadLine();
using (connection)
{
connection.Open();
// Start a local transaction.
SqlTransaction sqlTran = connection.BeginTransaction();
// Enlist a command in the current transaction.
SqlCommand command = connection.CreateCommand();
command.Transaction = sqlTran;
try
{
// Execute 1st command
command.CommandText = "insert into company values(@company_id,@company_name)";
command.Parameters.AddWithValue("@company_id", companyId);
command.Parameters.AddWithValue("@company_name", companyName);
command.ExecuteScalar();
// Execute 2nd command
command.CommandText = "insert into employee values(@emp_id,@EmpName,@gender,@phone_number,@address,@startDate,@company_id)";
command.Parameters.AddWithValue("@emp_id", empID);
command.Parameters.AddWithValue("@EmpName", empName);
command.Parameters.AddWithValue("@startDate", startDate);
command.Parameters.AddWithValue("@gender", gender);
command.Parameters.AddWithValue("@phone_number", phonenumber);
command.Parameters.AddWithValue("@address", address);
command.ExecuteScalar();
// Execute 3rd command
command.CommandText = "insert into payroll values(@emp_id,@Basic_Pay,@Deductions,@Taxable_pay,@Income_tax,@Net_pay)";
command.Parameters.AddWithValue("@Basic_Pay", basicPay);
command.Parameters.AddWithValue("@Deductions", deductions);
command.Parameters.AddWithValue("@Taxable_pay", taxablePay);
command.Parameters.AddWithValue("@Income_tax", tax);
command.Parameters.AddWithValue("@Net_pay", netPay);
command.ExecuteScalar();
// Execute 4th command
command.CommandText = "insert into department values(@dept_id,@dept_name)";
command.Parameters.AddWithValue("@dept_id", deptId);
command.Parameters.AddWithValue("@dept_name", deptName);
command.ExecuteScalar();
// Execute 5th command
command.CommandText = "insert into employee_dept values(@emp_id,@dept_id)";
command.ExecuteNonQuery();
// Commit the transaction after all commands.
sqlTran.Commit();
Console.WriteLine("All records were added into the database.");
}
catch (Exception ex)
{
// Handle the exception if the transaction fails to commit.
Console.WriteLine(ex.Message);
try
{
// Attempt to roll back the transaction.
sqlTran.Rollback();
}
catch (Exception exRollback)
{
// Throws an InvalidOperationException if the connection
// is closed or the transaction has already been rolled
// back on the server.
Console.WriteLine(exRollback.Message);
}
}
}
}
