public static List <JoinTableClass> GetJoinTable(int cityOneId)
{
{
List <JoinTableClass> fullInfo = new List <JoinTableClass> {
};
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT flights.*, cities_flights.city_one_id, cities_flights.city_two_id FROM
cities JOIN cities_flights ON (cities.id = cities_flights.city_one_id)
JOIN flights ON (cities_flights.flight_id = flights.id)
WHERE cities.id = " + cityOneId + ";";
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
int id = rdr.GetInt32(0);
DateTime departureTime = (DateTime)rdr.GetDateTime(1);
string status = rdr.GetString(2);
string cityOne = CityClass.FindById(rdr.GetInt32(3)).GetCity();
string cityTwo = CityClass.FindById(rdr.GetInt32(4)).GetCity();
JoinTableClass fullFlightInfo = new JoinTableClass(id, departureTime.ToString("MM/dd/yyyy h:mm tt"), status, cityOne, cityTwo);
fullInfo.Add(fullFlightInfo);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(fullInfo);
}
}
public static CityClass FindById(int id)
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM cities WHERE id = " + id + ";";
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
int idz = 0;
string city = "";
string state = "";
while (rdr.Read())
{
idz = rdr.GetInt32(0);
city = rdr.GetString(1);
state = rdr.GetString(2);
}
CityClass newCity = new CityClass(idz, city, state);
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(newCity);
}
public static List <CityClass> GetAll()
{
List <CityClass> allCities = new List <CityClass> {
};
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM cities;";
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
int id = rdr.GetInt32(0);
string cityName = rdr.GetString(1);
string state = rdr.GetString(2);
CityClass newCity = new CityClass(id, cityName, state);
allCities.Add(newCity);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(allCities);
}
public override bool Equals(System.Object otherCity)
{
if (!(otherCity is CityClass))
{
return(false);
}
else
{
CityClass newCity = (CityClass)otherCity;
bool nameEquality = (this.GetCity() == newCity.GetCity());
return(nameEquality);
}
}
