/// <summary>Get available currencies</summary> /// <cardinality>Many</cardinality> public List <GetAvailableCurrenciesData> GetAvailableCurrencies( Guid financialCurrencyId, DateTime currencyDateTime ) { var ret = new List <GetAvailableCurrenciesData>(); string sql = @" select top 50 fc.financial_currency_against_financial_currency_type_rcd ,fctr.financial_currency_type_name ,fc.amount ,fc.equals_amount ,fc.decimal_count ,fc.financial_currency_id from financial_currency as fc inner join financial_currency_type_ref as fctr on fctr.financial_currency_type_rcd = financial_currency_against_financial_currency_type_rcd where fc.valid_from_date_time <= @currency_date_time and (fc.valid_until_date_time is null or @currency_date_time <= fc.valid_until_date_time) "; if (financialCurrencyId != Guid.Empty) { sql += @" and fc.financial_currency_type_rcd = ( select fc2.financial_currency_type_rcd from financial_currency as fc2 where fc2.financial_currency_id = @financial_currency_id )" ; } else { sql += @" and fc.amount = fc.equals_amount"; } using (var conn = new SqlConnection(Conn.ConnectionString)) { conn.Open(); conn.BeginTransaction(IsolationLevel.ReadUncommitted).Commit(); using (var command = new SqlCommand(sql, conn)) { command.Parameters.Add("@financial_currency_id", SqlDbType.UniqueIdentifier).Value = financialCurrencyId; command.Parameters.Add("@currency_date_time", SqlDbType.DateTime).Value = currencyDateTime; Logging log = Logging.PerformanceTimeStart("SolutionNorSolutionPort.DataAccessLayer.FinancialSearch.GetAvailableCurrencies"); IDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult); log.PerformanceTimeStop(sql, command); var ordinals = new GetAvailableCurrenciesDataOrdinals(reader); while (reader.Read()) { var data = new GetAvailableCurrenciesData(); data.Populate(reader, ordinals); ret.Add(data); } reader.Close(); } return(ret); } }
// 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, GetAvailableCurrenciesDataOrdinals ordinals) { if (!reader.IsDBNull(ordinals.FinancialCurrencyAgainstFinancialCurrencyTypeRcd)) { FinancialCurrencyAgainstFinancialCurrencyTypeRcd = reader.GetString(ordinals.FinancialCurrencyAgainstFinancialCurrencyTypeRcd); } if (!reader.IsDBNull(ordinals.FinancialCurrencyTypeName)) { FinancialCurrencyTypeName = reader.GetString(ordinals.FinancialCurrencyTypeName); } if (!reader.IsDBNull(ordinals.Amount)) { Amount = reader.GetDecimal(ordinals.Amount); } if (!reader.IsDBNull(ordinals.EqualsAmount)) { EqualsAmount = reader.GetDecimal(ordinals.EqualsAmount); } if (!reader.IsDBNull(ordinals.DecimalCount)) { DecimalCount = reader.GetInt32(ordinals.DecimalCount); } if (!reader.IsDBNull(ordinals.FinancialCurrencyId)) { FinancialCurrencyId = reader.GetGuid(ordinals.FinancialCurrencyId); } }