//show all passengers
private void btnShowPassengers_Click(object sender, EventArgs e)
{
//get all passenger informations from all three tables (will inner join)
//place the result into DataTable and display the result in Lookups form
//when focus is back, repopulate the list box with updated records
using (var conn = new OleDbConnection(DatabaseObjects.ConnectionString))
{
conn.Open();
command = new OleDbCommand(
"SELECT p.PassengerID AS ID, p.PassengerName AS Name, s.SeatRow, s.SeatColumn, p.PassengerOnWaitingList AS WaitList " +
"FROM (Passengers p " +
"INNER JOIN PassengerSeats ps ON p.PassengerID = ps.PassengerID) " +
"INNER JOIN Seats s ON s.SeatID = ps.SeatID " +
"UNION " +
"SELECT p.PassengerID, p.PassengerName, null, null, p.PassengerOnWaitingList " +
"FROM Passengers p " +
"WHERE p.PassengerOnWaitingList = true " +
"ORDER BY s.SeatRow, s.SeatColumn"
, conn);
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
PassengerLookup form = new PassengerLookup(dt);//passing the query results to the Passenger lookup form
form.ShowDialog();
PopulateAirplane();
}
}
//search passengers
private void btnSearchPassenger_Click(object sender, EventArgs e)
{
//make sure a search string was entered in the text box
//get all the passengers that match the search string. Get all the information from all three tables.
//place the result in a DataTable and then display it in Lookups form
using (var conn = new OleDbConnection(DatabaseObjects.ConnectionString))
{
conn.Open();
if (!txtName.Text.Trim().Equals(""))
{
command = new OleDbCommand(
"SELECT p.PassengerID AS ID, p.PassengerName AS Name, s.SeatRow, s.SeatColumn, p.PassengerOnWaitingList AS WaitList " +
"FROM (Passengers p " +
"INNER JOIN PassengerSeats ps ON p.PassengerID = ps.PassengerID) " +
"INNER JOIN Seats s ON s.SeatID = ps.SeatID " +
"WHERE p.PassengerName LIKE @PassengerName " +
"UNION " +
"SELECT p.PassengerID, p.PassengerName, null, null, p.PassengerOnWaitingList " +
"FROM Passengers p " +
"WHERE p.PassengerOnWaitingList = true AND p.PassengerName LIKE @PassengerName " +
"ORDER BY s.SeatRow, s.SeatColumn"
, conn);
command.Parameters.Add(new OleDbParameter("PassengerName", "%" + txtName.Text + "%"));
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
PassengerLookup form = new PassengerLookup(dt);//passing the query results to the Passenger lookup form
form.ShowDialog();
PopulateAirplane();
}
else
{
MessageBox.Show("Please enter a valid name", "Invalid Input", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
}