// 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, FlightsOnBookingDataOrdinals ordinals) { if (!reader.IsDBNull(ordinals.FlightNumber)) { FlightNumber = reader.GetString(ordinals.FlightNumber); } if (!reader.IsDBNull(ordinals.OriginAirportCode)) { OriginAirportCode = reader.GetString(ordinals.OriginAirportCode); } if (!reader.IsDBNull(ordinals.OriginAirportName)) { OriginAirportName = reader.GetString(ordinals.OriginAirportName); } if (!reader.IsDBNull(ordinals.DestinationAirportCode)) { DestinationAirportCode = reader.GetString(ordinals.DestinationAirportCode); } if (!reader.IsDBNull(ordinals.DestinationAirportName)) { DestinationAirportName = reader.GetString(ordinals.DestinationAirportName); } if (!reader.IsDBNull(ordinals.FromDateTime)) { FromDateTime = reader.GetDateTime(ordinals.FromDateTime); } if (!reader.IsDBNull(ordinals.UntilDateTime)) { UntilDateTime = reader.GetDateTime(ordinals.UntilDateTime); } if (!reader.IsDBNull(ordinals.Comment)) { Comment = reader.GetString(ordinals.Comment); } if (!reader.IsDBNull(ordinals.FlightId)) { FlightId = reader.GetGuid(ordinals.FlightId); } }
/// <summary>Get all flights on a booking</summary> /// <cardinality>Many</cardinality> public List <FlightsOnBookingData> FlightsOnBooking( Guid bookingId ) { var ret = new List <FlightsOnBookingData>(); string sql = @" select ( select top 1 flight_identifier_code from flight_identifier as fi where fi.flight_id = fl.flight_id and fi.flight_identifier_type_rcd = 'FN3' ) as flight_number ,( select top 1 airport_identifier_code from airport_identifier as ai where ai.airport_id = fl.departure_airport_id and ai.airport_identifier_type_rcd = 'AC3' ) as origin_airport_code ,ao.airport_name as origin_airport_name ,( select top 1 airport_identifier_code from airport_identifier as ai where ai.airport_id = fl.arrival_airport_id and ai.airport_identifier_type_rcd = 'AC3' ) as destination_airport_code ,ad.airport_name as destination_airport_name ,fl.from_date_time ,fl.until_date_time ,fl.comment ,fl.flight_id from flight as fl inner join flight_segment as fs on fs.flight_id = fl.flight_id inner join booking_flight_segment as bfs on bfs.flight_segment_id = fs.flight_segment_id inner join airport as ao on ao.airport_id = fl.departure_airport_id inner join airport as ad on ad.airport_id = fl.arrival_airport_id --realwhere where bfs.booking_id = @booking_id "; using (var conn = new SqlConnection(Conn.ConnectionString)) { conn.Open(); conn.BeginTransaction(IsolationLevel.ReadUncommitted).Commit(); using (var command = new SqlCommand(sql, conn)) { command.Parameters.Add("@booking_id", SqlDbType.UniqueIdentifier).Value = bookingId; Logging log = Logging.PerformanceTimeStart("SolutionNorSolutionPort.DataAccessLayer.FlightSearch.FlightsOnBooking"); IDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult); log.PerformanceTimeStop(sql, command); var ordinals = new FlightsOnBookingDataOrdinals(reader); while (reader.Read()) { var data = new FlightsOnBookingData(); data.Populate(reader, ordinals); ret.Add(data); } reader.Close(); } return(ret); } }