public List <City> GetCities() { MySqlConnection conn = DB.Connection(); conn.Open(); MySqlCommand cmd = conn.CreateCommand() as MySqlCommand; cmd.CommandText = @"SELECT cities.* FROM flights JOIN cities_flights ON (flights.id = cities_flights.flight_id) JOIN cities ON (cities_flights.city_id = cities.id) WHERE flights.id = @FlightId;"; MySqlParameter flightIdParameter = new MySqlParameter(); flightIdParameter.ParameterName = "@FlightId"; flightIdParameter.Value = _id; cmd.Parameters.Add(flightIdParameter); MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader; List <City> cities = new List <City> { }; while (rdr.Read()) { int cityId = rdr.GetInt32(0); string cityDescription = rdr.GetString(1); City newCity = new City(cityDescription, cityId); cities.Add(newCity); } conn.Close(); if (conn != null) { conn.Dispose(); } return(cities); }
public List <Flight> GetFlights() { MySqlConnection conn = DB.Connection(); conn.Open(); var cmd = conn.CreateCommand() as MySqlCommand; cmd.CommandText = @"SELECT flight_id FROM cities_flights WHERE city_id = @cityId;"; MySqlParameter cityIdParameter = new MySqlParameter(); cityIdParameter.ParameterName = "@cityId"; cityIdParameter.Value = _id; cmd.Parameters.Add(cityIdParameter); var rdr = cmd.ExecuteReader() as MySqlDataReader; List <int> flightIds = new List <int> { }; while (rdr.Read()) { int flightId = rdr.GetInt32(0); flightIds.Add(flightId); } rdr.Dispose(); List <Flight> flights = new List <Flight> { }; foreach (int flightId in flightIds) { var flightQuery = conn.CreateCommand() as MySqlCommand; flightQuery.CommandText = @"SELECT * FROM flights WHERE id = @FlightId;"; MySqlParameter flightIdParameter = new MySqlParameter(); flightIdParameter.ParameterName = "@FlightId"; flightIdParameter.Value = flightId; flightQuery.Parameters.Add(flightIdParameter); var flightQueryRdr = flightQuery.ExecuteReader() as MySqlDataReader; while (flightQueryRdr.Read()) { int thisFlightId = flightQueryRdr.GetInt32(0); string flightName = flightQueryRdr.GetString(1); int flightDepartureTime = flightQueryRdr.GetInt32(2); string flightDeparture = flightQueryRdr.GetString(3); string flightArrival = flightQueryRdr.GetString(4); string flightStatus = flightQueryRdr.GetString(5); Flight foundFlight = new Flight(flightName, flightDepartureTime, flightDeparture, flightArrival, flightStatus, thisFlightId); flights.Add(foundFlight); } flightQueryRdr.Dispose(); } conn.Close(); if (conn != null) { conn.Dispose(); } return(flights); }