Exemple #1
0
 // 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);
     }
 }
Exemple #2
0
        /// <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);
            }
        }