// use ordinals to speed up access to DataReader // links: // crud definition: https://en.wikipedia.org/wiki/Create,_read,_update_and_delete // docLink: http://sql2x.org/documentationLink/327451c3-64a8-4de8-b359-76742d634497 // parameters: // reader: IDataReader from SQLClient public void Populate(IDataReader reader, FlightsForBookingWithFareDataOrdinals ordinals) { if (!reader.IsDBNull(ordinals.FlightId)) { FlightId = reader.GetGuid(ordinals.FlightId); } if (!reader.IsDBNull(ordinals.AirlineIdentifierCode)) { AirlineIdentifierCode = reader.GetString(ordinals.AirlineIdentifierCode); } if (!reader.IsDBNull(ordinals.FlightNumber)) { FlightNumber = reader.GetString(ordinals.FlightNumber); } if (!reader.IsDBNull(ordinals.DepartureAirportIdentifierCode)) { DepartureAirportIdentifierCode = reader.GetString(ordinals.DepartureAirportIdentifierCode); } if (!reader.IsDBNull(ordinals.DepartureAirportName)) { DepartureAirportName = reader.GetString(ordinals.DepartureAirportName); } if (!reader.IsDBNull(ordinals.ArrivalAirportIdentifierCode)) { ArrivalAirportIdentifierCode = reader.GetString(ordinals.ArrivalAirportIdentifierCode); } if (!reader.IsDBNull(ordinals.ArrivalAirportName)) { ArrivalAirportName = reader.GetString(ordinals.ArrivalAirportName); } if (!reader.IsDBNull(ordinals.FromDateTime)) { FromDateTime = reader.GetDateTime(ordinals.FromDateTime); } if (!reader.IsDBNull(ordinals.UntilDateTime)) { UntilDateTime = reader.GetDateTime(ordinals.UntilDateTime); } if (!reader.IsDBNull(ordinals.BookingOpenFlag)) { BookingOpenFlag = reader.GetBoolean(ordinals.BookingOpenFlag); } if (!reader.IsDBNull(ordinals.Amount)) { Amount = reader.GetDecimal(ordinals.Amount); } if (!reader.IsDBNull(ordinals.FinancialCurrencyTypeCode)) { FinancialCurrencyTypeCode = reader.GetString(ordinals.FinancialCurrencyTypeCode); } if (!reader.IsDBNull(ordinals.FinancialCurrencyId)) { FinancialCurrencyId = reader.GetGuid(ordinals.FinancialCurrencyId); } if (!reader.IsDBNull(ordinals.FinancialFareId)) { FinancialFareId = reader.GetGuid(ordinals.FinancialFareId); } }
/// <summary>Get current schedule based on airport and date</summary> /// <summary>with fare information</summary> /// <cardinality>Many</cardinality> public List <FlightsForBookingWithFareData> FlightsForBookingWithFare( Guid departureAirportId, Guid arrivalAirportId, DateTime fromDateTime, DateTime untilDateTime ) { var ret = new List <FlightsForBookingWithFareData>(); string sql = @" select fl.flight_id ,ai.airline_identifier_code ,fi.flight_identifier_code as flight_number ,dai.airport_identifier_code as departure_airport_identifier_code ,da.airport_name as departure_airport_name ,aai.airport_identifier_code as arrival_airport_identifier_code ,aa.airport_name as arrival_airport_name ,fl.from_date_time ,fl.until_date_time ,dbo.FlightEventOneIsLast( fl.flight_id, dbo.ref('date_time_type_ref.BO'), dbo.ref('date_time_type_ref.BC') ) as booking_open_flag ,ff.amount ,fc.financial_currency_type_code ,ff.financial_currency_id ,ff.financial_fare_id from flight as fl inner join airline_identifier as ai on ai.airline_id = fl.airline_id and ai.airline_identifier_type_rcd = dbo.ref('airline_identifier_type_ref.AC3') inner join airport as da on da.airport_id = fl.departure_airport_id inner join airport as aa on aa.airport_id = fl.arrival_airport_id inner join airport_identifier as dai on dai.airport_id = fl.departure_airport_id and dai.airport_identifier_type_rcd = dbo.ref('airport_identifier_type_ref.AC3') inner join airport_identifier as aai on aai.airport_id = fl.arrival_airport_id and aai.airport_identifier_type_rcd = dbo.ref('airport_identifier_type_ref.AC3') left join flight_identifier as fi on fi.flight_id = fl.flight_id and fi.flight_identifier_type_rcd = dbo.ref('flight_identifier_type_ref.FN3') left join financial_fare as ff on ff.departure_airport_id = fl.departure_airport_id and ff.arrival_airport_id = fl.arrival_airport_id and fl.from_date_time between ff.valid_from_flight_date_time and ff.valid_until_flight_date_time left join financial_currency as fc on fc.financial_currency_id = ff.financial_currency_id --realwhere where fl.from_date_time between @from_date_time and @until_date_time and fl.became_flight_id is null "; if (departureAirportId != Guid.Empty) { sql += " and fl.departure_airport_id = @departure_airport_id\r\n"; } if (arrivalAirportId != Guid.Empty) { sql += " and fl.arrival_airport_id = @arrival_airport_id\r\n"; } sql += " order by fl.from_date_time\r\n"; using (var conn = new SqlConnection(Conn.ConnectionString)) { conn.Open(); conn.BeginTransaction(IsolationLevel.ReadUncommitted).Commit(); using (var command = new SqlCommand(sql, conn)) { command.Parameters.Add("@departure_airport_id", SqlDbType.UniqueIdentifier).Value = departureAirportId; command.Parameters.Add("@arrival_airport_id", SqlDbType.UniqueIdentifier).Value = arrivalAirportId; command.Parameters.Add("@from_date_time", SqlDbType.DateTime).Value = fromDateTime; command.Parameters.Add("@until_date_time", SqlDbType.DateTime).Value = untilDateTime; Logging log = Logging.PerformanceTimeStart("SolutionNorSolutionPort.DataAccessLayer.FlightSearch.FlightsForBookingWithFare"); IDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult); log.PerformanceTimeStop(sql, command); var ordinals = new FlightsForBookingWithFareDataOrdinals(reader); while (reader.Read()) { var data = new FlightsForBookingWithFareData(); data.Populate(reader, ordinals); ret.Add(data); } reader.Close(); } return(ret); } }