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