示例#1
0
        //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();
            }
        }
示例#2
0
        //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);
                }
            }
        }