/// <summary> /// Method responsible for getting Flights from the Flights table /// </summary> public static List <FlightsTable> getFlights() { List <FlightsTable> flightLists = new List <FlightsTable>(); FlightsTable flights; SqlConnection connection = UnversalDBControls.GetConnection(); string selectStatement = "SELECT FlightId, FltPlaneNo, FltDepart, FltReturn, FltLocation, FltDestination, RegionId, FltTicketPrice " + "FROM FlightsTable"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader dr = selectCommand.ExecuteReader(); while (dr.Read()) { flights = new FlightsTable(); flights.FlightId = (int)dr["FlightId"]; flights.FltPlaneNo = (int)dr["FltPlaneNo"]; flights.FltDepart = (DateTime)dr["FltDepart"]; flights.FltReturn = (DateTime)dr["FltReturn"]; flights.FltLocation = dr["FltLocation"].ToString(); flights.FltDestination = dr["FltDestination"].ToString(); flights.RegionId = dr["RegionId"].ToString(); flights.FltTicketPrice = Convert.ToDouble(dr["FltTicketPrice"]); flightLists.Add(flights); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(flightLists); }
/// <summary> /// Method responsible for getting Flights according to the depart and return plane Id /// </summary> public static List <FlightsTable> getFlightsById(int DepartPlnId, int ReturnPlnId) { List <FlightsTable> flightLists = new List <FlightsTable>(); FlightsTable flights; SqlConnection connection = UnversalDBControls.GetConnection(); string selectStatement = "SELECT FlightId, FltPlaneNo, FltDepart, FltReturn, FltLocation, FltDestination, RegionId, FltTicketPrice " + "FROM FlightsTable " + "WHERE FlightId = @DepartPlnId " + "AND FlightId <> 0"; SqlCommand selectCommand1 = new SqlCommand(selectStatement, connection); selectCommand1.Parameters.AddWithValue("@DepartPlnId", DepartPlnId); selectStatement = "SELECT FlightId, FltPlaneNo, FltDepart, FltReturn, FltLocation, FltDestination, RegionId, FltTicketPrice " + "FROM FlightsTable " + "WHERE FlightId = @ReturnPlnId " + "AND FlightId <> 0"; SqlCommand selectCommand2 = new SqlCommand(selectStatement, connection); selectCommand2.Parameters.AddWithValue("@ReturnPlnId", ReturnPlnId); try { connection.Open(); SqlDataReader dr = selectCommand1.ExecuteReader(CommandBehavior.SingleRow); while (dr.Read()) { flights = new FlightsTable(); flights.FlightId = (int)dr["FlightId"]; flights.FltPlaneNo = (int)dr["FltPlaneNo"]; flights.FltDepart = (DateTime)dr["FltDepart"]; flights.FltReturn = (DateTime)dr["FltReturn"]; flights.FltLocation = dr["FltLocation"].ToString(); flights.FltDestination = dr["FltDestination"].ToString(); flights.RegionId = dr["RegionId"].ToString(); flights.FltTicketPrice = Convert.ToDouble(dr["FltTicketPrice"]); flightLists.Add(flights); } connection.Close(); connection.Open(); dr = selectCommand2.ExecuteReader(CommandBehavior.SingleRow); while (dr.Read()) { flights = new FlightsTable(); flights.FlightId = (int)dr["FlightId"]; flights.FltPlaneNo = (int)dr["FltPlaneNo"]; flights.FltDepart = (DateTime)dr["FltDepart"]; flights.FltReturn = (DateTime)dr["FltReturn"]; flights.FltLocation = dr["FltLocation"].ToString(); flights.FltDestination = dr["FltDestination"].ToString(); flights.RegionId = dr["RegionId"].ToString(); flights.FltTicketPrice = Convert.ToDouble(dr["FltTicketPrice"]); flightLists.Add(flights); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(flightLists); }