public override async Task <IReadOnlyCollection <Bill> > GetMultipleAsync(BillCriteria billCriteria = null) { try { var bills = new List <Bill>(); var rawQuery = $"select * from [{nameof(Bill)}]"; var wherePart = this.GetWhereConditions(billCriteria); var sql = rawQuery + (wherePart.sql is not null && wherePart.sql != string.Empty ? " where " + wherePart.sql : string.Empty) + " " + this.GetSortingSQL(); var cmd = new CommandDefinition( commandText: sql, parameters: wherePart.parameters, // clauses.Values.SelectMany(x => (k => k.Item1, k => k.Item2)).ToDictionary(x => x, transaction: this.GetTransactionIfAvailable()); //var resultsGrid = SqlMapper.QueryMultipleAsync(this._connection, cmd); //var x = await resultsGrid.Result.ReadAsync(); await using (var reader = await SqlMapper.ExecuteReaderAsync(cnn: this._connection, cmd).ConfigureAwait(false)) { while (await reader.ReadAsync().ConfigureAwait(false)) { // TODO: cache column names composition bills.Add( new Bill( id: await reader.GetSafeAsync <long>(nameof(Bill.Id)).ConfigureAwait(false), supplierId: await reader.GetSafeAsync <long>(nameof(Bill.SupplierId)).ConfigureAwait(false), releaseDate: await reader.GetSafeAsync <DateTime>(nameof(Bill.ReleaseDate)).ConfigureAwait(false), dueDate: await reader.GetSafeAsync <DateTime>(nameof(Bill.DueDate)).ConfigureAwait(false), paymentDate: await reader.GetSafeAsync <DateTime?>(nameof(Bill.PaymentDate)).ConfigureAwait(false), registrationDate: await reader.GetSafeAsync <DateTime>(nameof(Bill.RegistrationDate)).ConfigureAwait(false), amount: await reader.GetSafeAsync <double>(nameof(Bill.Amount)).ConfigureAwait(false), agio: await reader.GetSafeAsync <double>(nameof(Bill.Agio)).ConfigureAwait(false), additionalCosts: await reader.GetSafeAsync <double>(nameof(Bill.AdditionalCosts)).ConfigureAwait(false), code: await reader.GetSafeAsync <string>(nameof(Bill.Code)).ConfigureAwait(false), notes: await reader.GetSafeAsync <string>(nameof(Bill.Notes)).ConfigureAwait(false))); } } return(bills.AsReadOnly()); } catch (InvalidCastException ex) { Debug.WriteLine(ex); return(Array.Empty <Bill>()); } catch (Exception ex) { Debug.WriteLine(ex); return(Array.Empty <Bill>()); } }
public async Task<IReadOnlyCollection<BillDto>> GetAsync(BillCriteria billCriteria) { IReadOnlyCollection<Bill> bills = null; using (var uow = await this._billingUnitOfWorkFactory.CreateAsync().ConfigureAwait(false)) { bills = await uow.Bills.GetMultipleAsync(billCriteria).ConfigureAwait(false); } var billDTOs = bills.Select(s => BillDto.From(s)).ToList().AsReadOnly(); return billDTOs; }
public Task <DetailedBillDto> GetDetailedBillsAsync(BillCriteria criteria = null) { throw new NotImplementedException(); }
private (string sql, IDictionary <string, object> parameters) GetWhereConditions(BillCriteria billCriteria) { if (billCriteria == null) { return(null, null); } //IDictionary<string, IEnumerable<Tuple<string, object>>> clauses = new Dictionary<string, IEnumerable<Tuple<string, object>>>(); IList <string> whereSqlClauses = new List <string>(); var whereParamsDict = new Dictionary <string, object>(); if (billCriteria.SupplierId.HasValue) { const string paramKey = "@" /*+ nameof(billCriteria) + "." */ + nameof(billCriteria.SupplierId); whereSqlClauses.Add(nameof(Bill.SupplierId) + " = " + paramKey); whereParamsDict.Add(paramKey, billCriteria.SupplierId); } if (billCriteria.IsPaid.HasValue) { whereSqlClauses.Add(nameof(Bill.PaymentDate) + (billCriteria.IsPaid.Value ? " is not " : " is ") + "NULL"); } if (billCriteria.ReleaseDateRange != null) { if (billCriteria.ReleaseDateRange.Start.HasValue && billCriteria.ReleaseDateRange.End.HasValue) { const string startParamKey = "@" + nameof(billCriteria.ReleaseDateRange) + nameof(billCriteria.ReleaseDateRange.Start); const string endParamKey = "@" + nameof(billCriteria.ReleaseDateRange) + nameof(billCriteria.ReleaseDateRange.End); if (billCriteria.ReleaseDateRange.Start.Value == billCriteria.ReleaseDateRange.End.Value) { whereSqlClauses.Add(nameof(Bill.ReleaseDate) + " = " + startParamKey); } else { whereSqlClauses.Add(nameof(Bill.ReleaseDate) + " between " + startParamKey + " and " + endParamKey); } whereParamsDict.Add(startParamKey, billCriteria.ReleaseDateRange.Start); whereParamsDict.Add(endParamKey, billCriteria.ReleaseDateRange.End); } else if (billCriteria.ReleaseDateRange.Start.HasValue) { const string startParamKey = "@" + nameof(billCriteria.ReleaseDateRange) + nameof(billCriteria.ReleaseDateRange.Start); whereSqlClauses.Add(nameof(Bill.ReleaseDate) + " >= " + startParamKey); whereParamsDict.Add(startParamKey, billCriteria.ReleaseDateRange.Start); } else { const string endParamKey = "@" + nameof(billCriteria.ReleaseDateRange) + nameof(billCriteria.ReleaseDateRange.End); whereSqlClauses.Add(nameof(Bill.ReleaseDate) + " <= " + endParamKey); whereParamsDict.Add(endParamKey, billCriteria.ReleaseDateRange.End); } } // TODO: use stringbuilder return(string.Join(" and ", whereSqlClauses), whereParamsDict); }
public override Task <Bill> GetSingleAsync(BillCriteria billCriteria) { throw new NotImplementedException(); }
public abstract Task <IReadOnlyCollection <Bill> > GetMultipleAsync(BillCriteria billCriteria = null);
public abstract Task <Bill> GetSingleAsync(BillCriteria billCriteria);