public void SetDepartures(DepartureCity newDepartureCity)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"INSERT INTO flights (arrival_city_id, departure_city_id) VALUES (@ArrivalCityId, @DepartureCityId);";
MySqlParameter departure_city_id = new MySqlParameter();
departure_city_id.ParameterName = "@DepartureCityId";
departure_city_id.Value = newDepartureCity.GetId();
cmd.Parameters.Add(departure_city_id);
MySqlParameter arrival_city_id = new MySqlParameter();
arrival_city_id.ParameterName = "@ArrivalCityId";
arrival_city_id.Value = _id;
cmd.Parameters.Add(arrival_city_id);
cmd.ExecuteNonQuery();
conn.Close();
if (conn != null)
{
conn.Dispose();
}
}
public static DepartureCity Find(int id)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM departure_cities WHERE id = (@searchId);";
MySqlParameter searchId = new MySqlParameter();
searchId.ParameterName = "@searchId";
searchId.Value = id;
cmd.Parameters.Add(searchId);
var rdr = cmd.ExecuteReader() as MySqlDataReader;
int departureCityId = 0;
string departureCity = "";
while (rdr.Read())
{
departureCityId = rdr.GetInt32(0);
departureCity = rdr.GetString(1);
}
DepartureCity newDepartureCity = new DepartureCity(departureCity, departureCityId);
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(newDepartureCity);
}
public static List <DepartureCity> GetAll()
{
List <DepartureCity> allDepartureCity = new List <DepartureCity> {
};
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM departure_cities;";
var rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
int departureCityId = rdr.GetInt32(0);
string departureCity = rdr.GetString(1);
DepartureCity newDepartureCity = new DepartureCity(departureCity, departureCityId);
allDepartureCity.Add(newDepartureCity);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(allDepartureCity);
}
public List <DepartureCity> GetArrivals()
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT departure_cities.* FROM arrival_cities
JOIN flights ON (arrival_cities.id = flights.arrival_city_id)
JOIN departure_cities ON (flights.departure_city_id = departure_cities.id)
WHERE arrival_cities.id = @ArrivalCityId;";
MySqlParameter arrivalCityIdParameter = new MySqlParameter();
arrivalCityIdParameter.ParameterName = "@ArrivalCityId";
arrivalCityIdParameter.Value = _id;
cmd.Parameters.Add(arrivalCityIdParameter);
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
List <DepartureCity> departureCities = new List <DepartureCity> {
};
while (rdr.Read())
{
int departureCityId = rdr.GetInt32(0);
string departureCityName = rdr.GetString(1);
DepartureCity newDepartureCity = new DepartureCity(departureCityName, departureCityId);
departureCities.Add(newDepartureCity);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(departureCities);
}