/// <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); } } } }