/// <summary>
/// This will delete the passenger
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void deleteButton_Click(object sender, EventArgs e)
{
try
{
int i = 0;
DataSet ds = _data.ExecuteSQLStatement(clsAirlineSQL.selectPassengerID(fName, lName), ref i); // The DataSet of all the flights
foreach (DataRow row in ds.Tables[0].Rows) // Each row (each individual invoice) in the Invoices table
{
passengerId = int.Parse(row[0].ToString());
}
//Delete the passenger flight info
_data.ExecuteNonQuery(clsAirlineSQL.deletePassengerIDFromFlightLink(passengerId));
//Delete the passenger
_data.ExecuteNonQuery(clsAirlineSQL.deletePassengerIDPassengerTable(passengerId));
fName = "";
lName = "";
this.Close();
}
catch (Exception ex)
{
HandleError(MethodInfo.GetCurrentMethod().DeclaringType.Name,
MethodInfo.GetCurrentMethod().Name, ex.Message);
}
}
/// <summary>
/// Adds a passenger to the database
/// </summary>
/// <param name="name">Passenger first name</param>
/// <param name="last">Passenger last name</param>
public void addPassengerToDB(string name, string last)
{
try
{
accessDatabase.ExecuteNonQuery(SQL.addPassenger(name, last));
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// Updates a seat assoiciated with the flightId, passengerId, and seatNumber
/// </summary>
/// <param name="flightId"></param>
/// <param name="passengerId"></param>
/// <param name="seatNumber"></param>
private void UpdateSeat(int flightId, int passengerId, string seatNumber)
{
try
{
var sql = $"update Flight_Passenger_Link set Seat_Number = '{seatNumber}' where flight_id = {flightId.ToString(NumberFormatInfo.InvariantInfo)} and passenger_id = {passengerId.ToString(NumberFormatInfo.InvariantInfo)}";
clsData.ExecuteNonQuery(sql);
return;
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// Insert a passenger into the database
/// </summary>
/// <param name="passenger"></param>
/// <returns></returns>
internal static int InsertPassenger(Passenger passenger)
{
try
{
var repository = new clsDataAccess();
var sql = "select max(passenger_id) from passenger";
var result = repository.ExecuteScalarSQL(sql);
if (!int.TryParse(result, out var passengerId))
{
passengerId = 1;
}
else
{
++passengerId;
}
sql = $"Insert into Passenger (passenger_id, first_name, last_name) values ({passengerId}, '{passenger.FirstName}', '{passenger.LastName}')";
repository.ExecuteNonQuery(sql);
return(passengerId);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// SQL for adding a new passenger
/// </summary>
/// <param name="FirstName"></param>
/// <param name="LastName"></param>
/// <param name="iFlightID"></param>
public void AddPassenger(string FirstName, string LastName, int iFlightID)
{
try
{
//new instance of clsPassengers class
passenger = new clsPassengers();
//set passengers first name
passenger.sFirstName = FirstName;
//set passengers last name
passenger.sLastName = LastName;
//SQL that adds passenger to database
string sSQL = "INSERT INTO PASSENGER(First_Name, Last_Name) VALUES('" + FirstName + "','" + LastName + "')";
//new instance of clsDataAccess creates an object called db
db = new clsDataAccess();
//excute
db.ExecuteNonQuery(sSQL);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// Seat change updates database bases off the flight number and
/// passenger ID
/// </summary>
/// <param name="flightID"></param>
/// <param name="passengerID"></param>
/// <param name="seatNum"></param>
public void SeatChange(string flightID, string passengerID, string seatNum)
{
try
{
changeSeat = true;
//mw.cmdChangeSeat.IsEnabled = false;
//mw.AddPassenger.IsEnabled = false;
//mw.DeletePassenger.IsEnabled = false;
//mw.cbChooseFlight.IsEnabled = false;
//mw.cbChoosePassenger.IsEnabled = false;
var updateSQL = sql.UpdatePassengerSeat(flightID, passengerID, seatNum);
data.ExecuteNonQuery(updateSQL);
}catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// Deletes a passenger in the database associated with the corresponding Id
/// </summary>
/// <param name="passengerId"></param>
public static void DeletePassenger(int passengerId)
{
try
{
var repository = new clsDataAccess();
var sql = $"delete from FLIGHT_PASSENGER_LINK where Passenger_ID = {passengerId.ToString(NumberFormatInfo.InvariantInfo)}";
repository.ExecuteNonQuery(sql);
sql = $"delete from Passenger where Passenger_ID = {passengerId.ToString(NumberFormatInfo.InvariantInfo)}";
repository.ExecuteNonQuery(sql);
return;
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// SQL Statement for deleting a passenger by ID
/// </summary>
/// <param name="PassengerID"></param>
public void DeletePassenger(int PassengerID)
{
//Delete the passenger by ID
string sSQL = "Delete FROM PASSENGER WHERE PASSENGER_ID = " + PassengerID;
db = new clsDataAccess();
//excute
db.ExecuteNonQuery(sSQL);
}
/// <summary>
/// Update the passenger's seat number for their specified flight
/// </summary>
/// <param name="flightID">The flight ID</param>
/// <param name="passengerID">The passenger's ID</param>
/// <param name="seatNumber">The new seat number</param>
public bool UpdatePassengerSeatNumber(String flightID, String passengerID, String seatNumber)
{
bool seatChanged = false;
try
{
seatChanged = (GetPassengerBySeat(flightID, seatNumber) == null) ? true : false;
if (seatChanged)
{
clsData.ExecuteNonQuery(sql.UpdatePassengerSeat(flightID, passengerID, seatNumber));
}
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
return(seatChanged);
}
/// <summary>
/// SQL Statement for deleting a passengers database table link
/// </summary>
/// <param name="iFlightID"></param>
/// <param name="PassengerID"></param>
public void DeletePassengerLink(int iFlightID, int PassengerID)
{
//SQL Statement for deleting a passengers database table link
string sSQL = "Delete FROM FLIGHT_PASSENGER_LINK " +
"WHERE FLIGHT_ID = " + iFlightID + " AND " +
"PASSENGER_ID = " + PassengerID;
db = new clsDataAccess();
//excute
db.ExecuteNonQuery(sSQL);
}
public void insertIntoLinkTable(string sFlighID, int iSeatNumber)
{
int iPassengerID = getPassengerID();
clsData = new clsDataAccess();
sSQL = sqlStatements.insertIntoLinkTable(sFlighID, iPassengerID, iSeatNumber);
int iRet = 0;
iRet = clsData.ExecuteNonQuery(sSQL);
bNewPassangerAdded = false;
}
public void insertPassangerIntoDB(string firstName, string lastName)
{
sSQL = sqlStatements.insertAPassenger(firstName, lastName);
this.sPassgenerFirstName = firstName;
this.sPassgenerLastName = lastName;
clsData = new clsDataAccess();
int iRet = 0;
iRet = clsData.ExecuteNonQuery(sSQL);
bNewPassangerAdded = true;
}
/// <summary>
/// Updates the passengers first name and last name.
/// </summary>
/// <param name="passenger"></param>
internal static void UpdatePassenger(Passenger passenger)
{
try
{
var sql = $"update passenger set First_Name = '{passenger.FirstName}', Last_Name = '{passenger.LastName}' where passenger_id = {passenger.PassengerID}";
var repository = new clsDataAccess();
repository.ExecuteNonQuery(sql);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// This will add a passenger to the link table
/// </summary>
/// <param name="flightID">This is the flightID they want</param>
/// <param name="insertFname">This is their firstName</param>
/// <param name="insertLName">This is their lastName</param>
/// <param name="seat">This is the seatnumber they want</param>
public static void addPassenger(string flightID, string insertFname, string insertLName, string seat)
{
try
{
_data.ExecuteNonQuery(clsAirlineSQL.insertPassenger(insertFname, insertLName));
int iRet = 0;
DataSet tmp = _data.ExecuteSQLStatement(clsAirlineSQL.selectPassengerID(insertFname, insertLName), ref iRet); // The DataSet of all the flights
List <Passenger> passId = new List <Passenger>(); // The list of flights to be returned
foreach (DataRow row in tmp.Tables[0].Rows) // Each row (each individual invoice) in the Invoices table
{
passengerId = row[0].ToString();
}
_data.ExecuteNonQuery(clsAirlineSQL.InsertPassengerLink(flightID, passengerId, seat));
}
catch (Exception ex)
{
System.IO.File.AppendAllText("C:\\Error.txt", Environment.NewLine + "HandleError Exception: " + ex.Message);
return;
}
}
public void addPassenger(string firstName, string lastName)
{
int newID;
int Count = 0;
string sSQL;
sSQL = "Insert INTO passenger (Passegner_ID, First_name, Last_name) values (";
db.ExecuteSQLStatement("Select * FROM Passenger ", ref Count);
newID = Count + 1;
sSQL = sSQL + newID.ToString() + ", " + firstName + ", " + lastName + ")";
db.ExecuteNonQuery(sSQL);
}
/// <summary>
/// Method to add passenger to DB
/// </summary>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
public void addPassengerToDB(string firstName, string lastName)
{
try
{
clsDataAccess dbaddPassenger = new clsDataAccess();
int numRowsadded = 0;
string SQLaddPassenger;
SQLaddPassenger = sql.InsertIntoPassenger(firstName, lastName);
numRowsadded = dbaddPassenger.ExecuteNonQuery(SQLaddPassenger);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." +
MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// SQL for adding a new passengers data base table link
/// </summary>
/// <param name="iFlightID"></param>
/// <param name="PassengerID"></param>
/// <param name="iSeat"></param>
public void AddPassengerLink(int iFlightID, int PassengerID, int iSeat)
{
try
{
//SQL statement for adding passenger link
string sSQL = "INSERT INTO Flight_Passenger_Link(Flight_ID, Passenger_ID, Seat_Number) VALUES( " + iFlightID + " , " + PassengerID + " , " + iSeat + ")";
db = new clsDataAccess();
//excute
db.ExecuteNonQuery(sSQL);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// SQL Statement for updating a passengers seat number
/// </summary>
/// <param name="Seat_Number"></param>
/// <param name="Flight_ID"></param>
/// <param name="Passenger_ID"></param>
public void UpdateSeatNumber(string Seat_Number, int Flight_ID, int Passenger_ID)
{
try
{
//Updating seat numbers
string sSQL = "UPDATE FLIGHT_PASSENGER_LINK SET Seat_Number = " + Seat_Number + " WHERE FLIGHT_ID = " + Flight_ID + " AND PASSENGER_ID = " + Passenger_ID;
db = new clsDataAccess();
//excute
db.ExecuteNonQuery(sSQL);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
