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