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