// 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, VoucherSearchWithRemainingAmountDataOrdinals ordinals) { if (!reader.IsDBNull(ordinals.FinancialVoucherId)) { FinancialVoucherId = reader.GetGuid(ordinals.FinancialVoucherId); } if (!reader.IsDBNull(ordinals.FinancialCurrencyId)) { FinancialCurrencyId = reader.GetGuid(ordinals.FinancialCurrencyId); } if (!reader.IsDBNull(ordinals.VoucherNumber)) { VoucherNumber = reader.GetInt32(ordinals.VoucherNumber); } if (!reader.IsDBNull(ordinals.VoucherDescription)) { VoucherDescription = reader.GetString(ordinals.VoucherDescription); } if (!reader.IsDBNull(ordinals.RemainingAmount)) { RemainingAmount = reader.GetDecimal(ordinals.RemainingAmount); } if (!reader.IsDBNull(ordinals.FinancialCurrencyTypeCode)) { FinancialCurrencyTypeCode = reader.GetString(ordinals.FinancialCurrencyTypeCode); } if (!reader.IsDBNull(ordinals.FinancialCurrencyTypeName)) { FinancialCurrencyTypeName = reader.GetString(ordinals.FinancialCurrencyTypeName); } if (!reader.IsDBNull(ordinals.ValidFromDateTime)) { ValidFromDateTime = reader.GetDateTime(ordinals.ValidFromDateTime); } if (!reader.IsDBNull(ordinals.ValidUntilDateTime)) { ValidUntilDateTime = reader.GetDateTime(ordinals.ValidUntilDateTime); } }
/// <summary>voucher search with remaining amount</summary> /// <cardinality>Many</cardinality> public List <VoucherSearchWithRemainingAmountData> VoucherSearchWithRemainingAmount( DateTime voucherValidOnDateTime ) { var ret = new List <VoucherSearchWithRemainingAmountData>(); string sql = @" select top 50 fv.financial_voucher_id ,fv.financial_currency_id ,fv.voucher_number ,fv.voucher_description ,fv.value_amount - coalesce( ( select sum(fpv.amount) from financial_payment_voucher as fpv where fpv.financial_voucher_id = fv.financial_voucher_id) , 0) as remaining_amount ,fc.financial_currency_type_code ,fc.financial_currency_type_name ,fv.valid_from_date_time ,fv.valid_until_date_time from financial_voucher as fv inner join financial_currency as fc on fc.financial_currency_id = fv.financial_currency_id --realwhere "; if (voucherValidOnDateTime != DateTime.MinValue) { sql += " where @voucher_valid_on_date_time between fv.valid_from_date_time and fv.valid_until_date_time"; } sql += " order by fv.voucher_number"; using (var conn = new SqlConnection(Conn.ConnectionString)) { conn.Open(); conn.BeginTransaction(IsolationLevel.ReadUncommitted).Commit(); using (var command = new SqlCommand(sql, conn)) { if (voucherValidOnDateTime != DateTime.MinValue) { command.Parameters.Add("@voucher_valid_on_date_time", SqlDbType.DateTime).Value = voucherValidOnDateTime; } Logging log = Logging.PerformanceTimeStart("SolutionNorSolutionPort.DataAccessLayer.FinancialSearch.VoucherSearchWithRemainingAmount"); IDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult); log.PerformanceTimeStop(sql, command); var ordinals = new VoucherSearchWithRemainingAmountDataOrdinals(reader); while (reader.Read()) { var data = new VoucherSearchWithRemainingAmountData(); data.Populate(reader, ordinals); ret.Add(data); } reader.Close(); } return(ret); } }