// 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, FlightSegmentsScheduleDataOrdinals ordinals) { if (!reader.IsDBNull(ordinals.FlightScheduleId)) { FlightScheduleId = reader.GetGuid(ordinals.FlightScheduleId); } if (!reader.IsDBNull(ordinals.FlightScheduleSegmentId)) { FlightScheduleSegmentId = reader.GetGuid(ordinals.FlightScheduleSegmentId); } 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.LogicalSegmentNumber)) { LogicalSegmentNumber = reader.GetInt32(ordinals.LogicalSegmentNumber); } if (!reader.IsDBNull(ordinals.PhysicalSegmentNumber)) { PhysicalSegmentNumber = reader.GetInt32(ordinals.PhysicalSegmentNumber); } if (!reader.IsDBNull(ordinals.DepartureTime)) { DepartureTime = (TimeSpan)reader[ordinals.DepartureTime]; } if (!reader.IsDBNull(ordinals.ArrivalTime)) { ArrivalTime = (TimeSpan)reader[ordinals.ArrivalTime]; } if (!reader.IsDBNull(ordinals.DepartureGate)) { DepartureGate = reader.GetString(ordinals.DepartureGate); } if (!reader.IsDBNull(ordinals.ArrivalGate)) { ArrivalGate = reader.GetString(ordinals.ArrivalGate); } }
/// <summary>Get segments for flight schedule</summary> /// <cardinality>Many</cardinality> public List <FlightSegmentsScheduleData> FlightSegmentsSchedule( Guid flightScheduleId ) { var ret = new List <FlightSegmentsScheduleData>(); string sql = @" select fss.flight_schedule_id ,fss.flight_schedule_segment_id ,ai.airline_identifier_code ,fsi.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 ,fss.logical_segment_number ,fss.physical_segment_number ,fss.departure_time ,fss.arrival_time ,fss.departure_gate ,fss.arrival_gate from flight_schedule_segment as fss inner join flight_schedule as fs on fs.flight_schedule_id = fss.flight_schedule_id inner join airline_identifier as ai on ai.airline_id = fs.airline_id and ai.airline_identifier_type_rcd = dbo.ref('airline_identifier_type_ref.AC3') inner join airport as da on da.airport_id = fss.departure_airport_id inner join airport as aa on aa.airport_id = fss.arrival_airport_id inner join airport_identifier as dai on dai.airport_id = fss.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 = fss.arrival_airport_id and aai.airport_identifier_type_rcd = dbo.ref('airport_identifier_type_ref.AC3') left join flight_schedule_identifier as fsi on fsi.flight_schedule_id = fss.flight_schedule_id and fsi.flight_identifier_type_rcd = dbo.ref('flight_identifier_type_ref.FN3') --realwhere where fss.flight_schedule_id = @flight_schedule_id order by fss.physical_segment_number "; using (var conn = new SqlConnection(Conn.ConnectionString)) { conn.Open(); conn.BeginTransaction(IsolationLevel.ReadUncommitted).Commit(); using (var command = new SqlCommand(sql, conn)) { command.Parameters.Add("@flight_schedule_id", SqlDbType.UniqueIdentifier).Value = flightScheduleId; Logging log = Logging.PerformanceTimeStart("SolutionNorSolutionPort.DataAccessLayer.PassengerSearch.FlightSegmentsSchedule"); IDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult); log.PerformanceTimeStop(sql, command); var ordinals = new FlightSegmentsScheduleDataOrdinals(reader); while (reader.Read()) { var data = new FlightSegmentsScheduleData(); data.Populate(reader, ordinals); ret.Add(data); } reader.Close(); } return(ret); } }