public void UpdateCity(string newCity, string newState) { MySqlConnection conn = DB.Connection(); conn.Open(); var cmd = conn.CreateCommand() as MySqlCommand; cmd.CommandText = @"UPDATE items SET city - @city, state = @state WHERE id = @searchId;"; MySqlParameter searchId = new MySqlParameter(); searchId.ParameterName = "@searchId"; searchId.Value = _id; cmd.Parameters.Add(searchId); MySqlParameter city = new MySqlParameter(); city.ParameterName = "@city"; city.Value = newCity; cmd.Parameters.Add(city); MySqlParameter state = new MySqlParameter(); state.ParameterName = "@state"; state.Value = newState; cmd.Parameters.Add(state); cmd.ExecuteNonQuery(); _city = newCity; _state = newState; conn.Close(); if (conn != null) { conn.Dispose(); } }
public static List <Flight> GetFlightsByCity(int id) { MySqlConnection conn = DB.Connection(); conn.Open(); var cmd = conn.CreateCommand() as MySqlCommand; cmd.CommandText = @"SELECT flight_number FROM cities_flights WHERE city_id = @CityId;"; MySqlParameter cityIdParameter = new MySqlParameter(); cityIdParameter.ParameterName = "@CityId"; cityIdParameter.Value = id; cmd.Parameters.Add(cityIdParameter); var rdr = cmd.ExecuteReader() as MySqlDataReader; List <int> flightNumbers = new List <int> { }; ////////////// while (rdr.Read()) { int flightNumber = rdr.GetInt32(0); flightNumbers.Add(flightNumber); } rdr.Dispose(); List <Flight> flight = new List <Flight> { }; foreach (int flightNum in flightNumbers) { var flightQuery = conn.CreateCommand() as MySqlCommand; flightQuery.CommandText = @"SELECT * FROM flights WHERE flight_number = @FlightNumber;"; MySqlParameter flightNumberParameter = new MySqlParameter(); flightNumberParameter.ParameterName = "@FlightNumber"; flightNumberParameter.Value = flightNum; flightQuery.Parameters.Add(flightNumberParameter); var flightQueryRdr = flightQuery.ExecuteReader() as MySqlDataReader; while (flightQueryRdr.Read()) { int newFlightId = flightQueryRdr.GetInt32(0); int newFlightNumber = flightQueryRdr.GetInt32(1); string newFlightTime = flightQueryRdr.GetString(2); int newFlightDepartId = flightQueryRdr.GetInt32(3); int newFlightArriveId = flightQueryRdr.GetInt32(4); string newStatus = flightQueryRdr.GetString(5); Flight foundFlight = new Flight(newFlightNumber, newFlightTime, newFlightDepartId, newFlightArriveId, newStatus, newFlightId); flight.Add(foundFlight); } flightQueryRdr.Dispose(); } conn.Close(); if (conn != null) { conn.Dispose(); } return(flight); }