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