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.flights_id)
JOIN cities ON (cities_flights.cities_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 cityName = rdr.GetString(1);
string cityAirline = rdr.GetString(2);
City newCity = new City(cityName, cityAirline);
cities.Add(newCity);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(cities);
}
public static Flight Find(int id)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM flights WHERE id = (@searchId);";
MySqlParameter searchId = new MySqlParameter();
searchId.ParameterName = "@searchId";
searchId.Value = id;
cmd.Parameters.Add(searchId);
var rdr = cmd.ExecuteReader() as MySqlDataReader;
int flightId = 0;
string flightName = "";
string flightDepartTime = "";
string flightStatus = "";
while (rdr.Read())
{
flightId = rdr.GetInt32(0);
flightName = rdr.GetString(1);
flightDepartTime = rdr.GetString(2);
flightStatus = rdr.GetString(3);
}
Flight foundFlight = new Flight(flightName, flightDepartTime, flightStatus, flightId);
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(foundFlight);
}
//Delete single flight instance from flights table
public void Delete()
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"DELETE FROM flights WHERE id = @FlightId;
DELETE FROM cities_flights
WHERE flight_id = @FlightId;";
MySqlParameter flightIdParameter = new MySqlParameter();
flightIdParameter.ParameterName = "@FlightId";
flightIdParameter.Value = this.GetId();
cmd.Parameters.Add(flightIdParameter);
cmd.ExecuteNonQuery();
conn.Close();
if (conn != null)
{
conn.Dispose();
}
}
//read input to join-table
public List <ArrivalCity> GetArrivals()
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT arrival_cities.* FROM departure_cities
JOIN flights ON (departure_cities.id = flights.departure_city_id)
JOIN arrival_cities ON (flights.arrival_city_id = arrival_cities.id)
WHERE departure_cities.id = @DepartureCityId;";
MySqlParameter departureCityIdParameter = new MySqlParameter();
departureCityIdParameter.ParameterName = "@DepartureCityId";
departureCityIdParameter.Value = _id;
cmd.Parameters.Add(departureCityIdParameter);
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
List <ArrivalCity> arrivalCities = new List <ArrivalCity> {
};
while (rdr.Read())
{
int arrivalCityId = rdr.GetInt32(0);
string arrivalCityName = rdr.GetString(1);
ArrivalCity newArrivalCity = new ArrivalCity(arrivalCityName, arrivalCityId);
arrivalCities.Add(newArrivalCity);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(arrivalCities);
}
public void Save()
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"INSERT INTO departure_cities (departure_city) VALUES (@departure_city);";
MySqlParameter departureCity = new MySqlParameter();
departureCity.ParameterName = "@departure_city";
departureCity.Value = this._departureCity;
cmd.Parameters.Add(departureCity);
cmd.ExecuteNonQuery();
_id = (int)cmd.LastInsertedId;
conn.Close();
if (conn != null)
{
conn.Dispose();
}
}
//Update single flight instance in flights table
public void UpdateStatus(string newName, string newDate, string newDeparture, string newArrival, string newStatus)
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"UPDATE flights SET flight_name = @newName, date = @newDate, dept_city = @newDept, arrival_city = @newArrival, status = @newStatus WHERE id = @searchId;";
MySqlParameter searchId = new MySqlParameter();
searchId.ParameterName = "@searchId";
searchId.Value = _id;
cmd.Parameters.Add(searchId);
//cmd.Parameters.Add(newMySqlParameter("@searchId", _id));
MySqlParameter flight_name = new MySqlParameter();
flight_name.ParameterName = "@newName";
flight_name.Value = newName;
cmd.Parameters.Add(flight_name);
//cmd.Parameters.Add(new MySqlParameter("@newName", _flightName));
MySqlParameter date = new MySqlParameter();
date.ParameterName = "@newDate";
date.Value = newDate;
cmd.Parameters.Add(date);
//cmd.Parameters.Add(new MySqlParameter("@newDate", _date));
MySqlParameter dept_city = new MySqlParameter();
dept_city.ParameterName = "@newDept";
dept_city.Value = newDeparture;
cmd.Parameters.Add(dept_city);
//cmd.Parameters.Add(new MySqlParameter("@newDept", _deptCity));
MySqlParameter arrival_city = new MySqlParameter();
arrival_city.ParameterName = "@newArrival";
arrival_city.Value = newStatus;
cmd.Parameters.Add(arrival_city);
//cmd.Parameters.Add(new MySqlParameter("@newArrival", _arrivalCity));
MySqlParameter status = new MySqlParameter();
status.ParameterName = "@newStatus";
status.Value = newStatus;
cmd.Parameters.Add(status);
//cmd.Parameters.Add(new MySqlParameter("@newStatus", _status));
cmd.ExecuteNonQuery();
_flightName = newName;
_date = newDate;
_deptCity = newDeparture;
_arrivalCity = newArrival;
_status = newStatus;
conn.Close();
if (conn != null)
{
conn.Dispose();
}
}
