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