public bool AddRecords(OfficeCommuteDataInfo commuteData) { try { using (_sqlConn = new SqlConnection(ConnectionString)) { using (SqlCommand cmdAddCommuteRecord = new SqlCommand(insertRecords, _sqlConn)) { cmdAddCommuteRecord.Parameters.AddWithValue("@Employee_Id", commuteData.EmployeeId); cmdAddCommuteRecord.Parameters.AddWithValue("@Employee_Name", commuteData.EmployeeName); cmdAddCommuteRecord.Parameters.AddWithValue("@Employee_Type", commuteData.EmployeeType); cmdAddCommuteRecord.Parameters.AddWithValue("@Travel_Distance", commuteData.TravelDistance); cmdAddCommuteRecord.Parameters.AddWithValue("@Commute_Charge", commuteData.CommuteCharge); _sqlConn.Open(); int rowsAdded = cmdAddCommuteRecord.ExecuteNonQuery(); if (rowsAdded > 0) { return(true); } else { return(false); } } } } catch (SqlException) { return(false); } }
public IList <OfficeCommuteDataInfo> DisplayRecordsByHigestCommuteFare() { List <OfficeCommuteDataInfo> commuteDataList = new List <OfficeCommuteDataInfo>(); try { using (_sqlConn = new SqlConnection(ConnectionString)) { using (SqlCommand cmdDisplayCommuteRecordsByHigestFare = new SqlCommand(displayEmployeesWithMaxCommuteFare, _sqlConn)) { _sqlConn.Open(); using (SqlDataReader sqlData = cmdDisplayCommuteRecordsByHigestFare.ExecuteReader()) { if (sqlData.HasRows) { while (sqlData.Read()) { OfficeCommuteDataInfo commuteData = new OfficeCommuteDataInfo { EmployeeId = sqlData["Employee_Id"].ToString(), EmployeeName = sqlData["Employee_Name"].ToString(), EmployeeType = sqlData["Employee_Type"].ToString(), TravelDistance = float.Parse(sqlData["Travel_Distance"].ToString()), CommuteCharge = double.Parse(sqlData["Commute_Charge"].ToString()) }; commuteDataList.Add(commuteData); } } } } } } catch (SqlException) { return(null); } return(commuteDataList);; }
public void Menu() { OfficeCommuteDataInfo commuteData = new OfficeCommuteDataInfo(); OfficeCommuteDataOperations commuteOperations = new OfficeCommuteDataOperations(); string loopInput = string.Empty; do { Console.WriteLine("\nMenu:\nEnter 1 to Add Employee Commute Records\n" + "Enter 2 to Display All Employee Commute Records\n" + "Enter 3 to Display All Employee Commute Records Who Are Paying Highest Fare"); int choice = 0; try { choice = int.Parse(Console.ReadLine()); } catch (FormatException fe) { Console.WriteLine("Menu Entry is not in correct format. Enter numbers only"); } switch (choice) { case 1: bool employeeIdCheckFormatLoopInput = false; bool employeeIdCheckDuplicateLoopInput = false; string employeeId = string.Empty; do { Console.WriteLine("Enter Employee Id:"); employeeId = Console.ReadLine(); if (/*Call the CheckEmployeeIdFormat method here and check if the return is false*/) { Console.WriteLine("Wrong Employee Id Entered..Try Again"); employeeIdCheckFormatLoopInput = true; employeeIdCheckDuplicateLoopInput = false; } else if (/*Call the CheckForDuplicateEmployeeId method here and check if the return is false*/) { Console.WriteLine("Same Employee Id already present in database..Try With another ID"); employeeIdCheckDuplicateLoopInput = true; employeeIdCheckFormatLoopInput = false; } else { commuteData.EmployeeId = employeeId; employeeIdCheckDuplicateLoopInput = false; employeeIdCheckFormatLoopInput = false; } }while (employeeIdCheckFormatLoopInput == true || employeeIdCheckDuplicateLoopInput == true); Console.WriteLine("Enter Employee Name:"); commuteData.EmployeeName = Console.ReadLine(); bool employeeTypeCheckLoop = false; bool employeeTypeWithEmployeeIdMatchLoop = false; string employeeType = string.Empty; do { Console.WriteLine("Enter Employee Type(External or Internal):"); employeeType = Console.ReadLine().ToUpper(); if (/*Call the CheckEmployeeTypeInput method here and check if the return is false*/) { Console.WriteLine("Invalid Employee Type..Try Again"); employeeTypeCheckLoop = true; employeeTypeWithEmployeeIdMatchLoop = false; } else if (/*Call the CheckIfEmployeeTypeMatchesWithEmployeeIdType method here and check if the return is false*/) { Console.WriteLine("Employee Type does not match with Employee Id type..Try Again"); employeeTypeWithEmployeeIdMatchLoop = true; employeeTypeCheckLoop = false; } else { commuteData.EmployeeType = employeeType; employeeTypeCheckLoop = false; employeeTypeWithEmployeeIdMatchLoop = false; } }while (employeeTypeCheckLoop == true || employeeTypeWithEmployeeIdMatchLoop == true); float travelDistance = 0.0F; bool travelDistanceCheckLoop = false; do { Console.WriteLine("Enter Travel Distance:"); travelDistance = float.Parse(Console.ReadLine()); if (/*Call the CheckTravelDistance method here and check if the return is false*/) { Console.WriteLine("Invalid Travel Distance..Try Again"); travelDistanceCheckLoop = true; } else { commuteData.TravelDistance = travelDistance; travelDistanceCheckLoop = false; } }while (travelDistanceCheckLoop == true); double commuteCharge = /* Call the FareCalculation method here to calculate fare and store * it under the commuteCharge Variable */ commuteData.CommuteCharge = commuteCharge; bool insertResult = /* Call the AddRecords methods here to add commute record into the database */ if (insertResult == true) { Console.WriteLine("Data Insertion Successful"); } else { Console.WriteLine("Data Insertion Failed"); } break; case 2: Console.WriteLine("\nDisplay All Employees Commute Infomation:"); Console.WriteLine("{0,-15}{1,-15}{2,-15}{3,-15}{4}", "Employee Id", "Name", "Employee Type", "Distance", "Commute Fare"); /*Display all commute records from the returned List with 15 spaces between each record*/ break; case 3: Console.WriteLine("\nDisplay Employees With Highest Commute Fare:"); Console.WriteLine("{0,-15}{1,-15}{2,-15}{3,-15}{4}", "Employee Id", "Name", "Employee Type", "Distance", "Commute Fare"); /*Display all highest fare commute records from the returned List with 15 spaces between each record*/ break; } Console.WriteLine("\nPress yes to continue your work..Any other key to terminate operation"); loopInput = Console.ReadLine(); }while (loopInput.Equals("yes", StringComparison.InvariantCultureIgnoreCase)); }
public void Menu() { OfficeCommuteDataInfo commuteData = new OfficeCommuteDataInfo(); OfficeCommuteDataOperations commuteOperations = new OfficeCommuteDataOperations(); string loopInput = string.Empty; do { Console.WriteLine("\nMenu:\nEnter 1 to Add Employee Commute Records\n" + "Enter 2 to Display All Employee Commute Records\n" + "Enter 3 to Display All Employee Commute Records Who Are Paying Highest Fare"); int choice = 0; try { choice = int.Parse(Console.ReadLine()); } catch (FormatException fe) { Console.WriteLine("Menu Entry is not in correct format. Enter numbers only"); } switch (choice) { case 1: bool employeeIdCheckFormatLoopInput = false; bool employeeIdCheckDuplicateLoopInput = false; string employeeId = string.Empty; do { Console.WriteLine("Enter Employee Id:"); employeeId = Console.ReadLine(); if (CheckEmployeeIdFormat(employeeId) == false) { Console.WriteLine("Wrong Employee Id Entered..Try Again"); employeeIdCheckFormatLoopInput = true; employeeIdCheckDuplicateLoopInput = false; } else if (CheckForDuplicateEmployeeId(employeeId) == false) { Console.WriteLine("Same Employee Id already present in database..Try With another ID"); employeeIdCheckDuplicateLoopInput = true; employeeIdCheckFormatLoopInput = false; } else { commuteData.EmployeeId = employeeId; employeeIdCheckDuplicateLoopInput = false; employeeIdCheckFormatLoopInput = false; } }while (employeeIdCheckFormatLoopInput == true || employeeIdCheckDuplicateLoopInput == true); Console.WriteLine("Enter Employee Name:"); commuteData.EmployeeName = Console.ReadLine(); bool employeeTypeCheckLoop = false; bool employeeTypeWithEmployeeIdMatchLoop = false; string employeeType = string.Empty; do { Console.WriteLine("Enter Employee Type(External or Internal):"); employeeType = Console.ReadLine().ToUpper(); if (CheckEmployeeTypeInput(employeeType) == false) { Console.WriteLine("Invalid Employee Type..Try Again"); employeeTypeCheckLoop = true; employeeTypeWithEmployeeIdMatchLoop = false; } else if (CheckIfEmployeeTypeMatchesWithEmployeeIdType(employeeId, employeeType) == false) { Console.WriteLine("Employee Type does not match with Employee Id type..Try Again"); employeeTypeWithEmployeeIdMatchLoop = true; employeeTypeCheckLoop = false; } else { commuteData.EmployeeType = employeeType; employeeTypeCheckLoop = false; employeeTypeWithEmployeeIdMatchLoop = false; } }while (employeeTypeCheckLoop == true || employeeTypeWithEmployeeIdMatchLoop == true); float travelDistance = 0.0F; bool travelDistanceCheckLoop = false; do { Console.WriteLine("Enter Travel Distance:"); travelDistance = float.Parse(Console.ReadLine()); if (CheckTravelDistance(travelDistance) == false) { Console.WriteLine("Invalid Travel Distance..Try Again"); travelDistanceCheckLoop = true; } else { commuteData.TravelDistance = travelDistance; travelDistanceCheckLoop = false; } }while (travelDistanceCheckLoop == true); double commuteCharge = FareCalculation(employeeType, travelDistance); commuteData.CommuteCharge = commuteCharge; bool insertResult = commuteOperations.AddRecords(commuteData); if (insertResult == true) { Console.WriteLine("Data Insertion Successful"); } else { Console.WriteLine("Data Insertion Failed"); } break; case 2: Console.WriteLine("\nDisplay All Employees Commute Infomation:"); Console.WriteLine("{0,-15}{1,-15}{2,-15}{3,-15}{4}", "Employee Id", "Name", "Employee Type", "Distance", "Commute Fare"); IList <OfficeCommuteDataInfo> commuteList = commuteOperations.DisplayRecords(); foreach (var commuteRecord in commuteList) { Console.WriteLine("{0,-15}{1,-15}{2,-15}{3,-15}{4}", commuteRecord.EmployeeId, commuteRecord.EmployeeName, commuteRecord.EmployeeType, commuteRecord.TravelDistance, commuteRecord.CommuteCharge.ToString("0.00")); } break; case 3: Console.WriteLine("\nDisplay Employees With Highest Commute Fare:"); Console.WriteLine("{0,-15}{1,-15}{2,-15}{3,-15}{4}", "Employee Id", "Name", "Employee Type", "Distance", "Commute Fare"); IList <OfficeCommuteDataInfo> highestCommuteList = commuteOperations.DisplayRecordsByHigestCommuteFare(); foreach (var commuteRecord in highestCommuteList) { Console.WriteLine("{0,-15}{1,-15}{2,-15}{3,-15}{4}", commuteRecord.EmployeeId, commuteRecord.EmployeeName, commuteRecord.EmployeeType, commuteRecord.TravelDistance, commuteRecord.CommuteCharge.ToString("0.00")); } break; } Console.WriteLine("\nPress yes to continue your work..Any other key to terminate operation"); loopInput = Console.ReadLine(); }while (loopInput.Equals("yes", StringComparison.InvariantCultureIgnoreCase)); }