// 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, FinancialFaresDataOrdinals ordinals) { if (!reader.IsDBNull(ordinals.FinancialFareTypeRcd)) { FinancialFareTypeRcd = reader.GetString(ordinals.FinancialFareTypeRcd); } 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.ValidFromFlightDateTime)) { ValidFromFlightDateTime = reader.GetDateTime(ordinals.ValidFromFlightDateTime); } if (!reader.IsDBNull(ordinals.ValidUntilFlightDateTime)) { ValidUntilFlightDateTime = reader.GetDateTime(ordinals.ValidUntilFlightDateTime); } if (!reader.IsDBNull(ordinals.Amount)) { Amount = reader.GetDecimal(ordinals.Amount); } if (!reader.IsDBNull(ordinals.FinancialCurrencyTypeRcd)) { FinancialCurrencyTypeRcd = reader.GetString(ordinals.FinancialCurrencyTypeRcd); } if (!reader.IsDBNull(ordinals.FinancialCurrencyTypeName)) { FinancialCurrencyTypeName = reader.GetString(ordinals.FinancialCurrencyTypeName); } if (!reader.IsDBNull(ordinals.DateTime)) { DateTime = reader.GetDateTime(ordinals.DateTime); } if (!reader.IsDBNull(ordinals.DefaultUserCode)) { DefaultUserCode = reader.GetString(ordinals.DefaultUserCode); } if (!reader.IsDBNull(ordinals.DefaultUserName)) { DefaultUserName = reader.GetString(ordinals.DefaultUserName); } if (!reader.IsDBNull(ordinals.FinancialFareId)) { FinancialFareId = reader.GetGuid(ordinals.FinancialFareId); } }
/// <summary>Get fares</summary> /// <cardinality>Many</cardinality> public List <FinancialFaresData> FinancialFares() { var ret = new List <FinancialFaresData>(); string sql = @" select top 50 fare.financial_fare_type_rcd ,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 ,fare.valid_from_flight_date_time ,fare.valid_until_flight_date_time ,fare.amount ,fc.financial_currency_type_rcd ,fctr.financial_currency_type_name ,fare.date_time ,du.default_user_code ,du.default_user_name ,fare.financial_fare_id from financial_fare as fare inner join airport as da on da.airport_id = fare.departure_airport_id inner join airport as aa on aa.airport_id = fare.arrival_airport_id inner join airport_identifier as dai on dai.airport_id = fare.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 = fare.arrival_airport_id and aai.airport_identifier_type_rcd = dbo.ref('airport_identifier_type_ref.AC3') inner join financial_currency as fc on fc.financial_currency_id = fare.financial_currency_id inner join financial_currency_type_ref as fctr on fctr.financial_currency_type_rcd = fc.financial_currency_type_rcd inner join default_user as du on du.default_user_id = fare.user_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.FinancialSearch.FinancialFares"); IDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult); log.PerformanceTimeStop(sql, command); var ordinals = new FinancialFaresDataOrdinals(reader); while (reader.Read()) { var data = new FinancialFaresData(); data.Populate(reader, ordinals); ret.Add(data); } reader.Close(); } return(ret); } }