private static List <string> ListFilters(AwardsQuery query) { var filters = new List <string>(); if (!string.IsNullOrWhiteSpace(query.Category)) { query.Category = query.Category.Trim(); filters.Add("Category = @Category"); } if (!string.IsNullOrWhiteSpace(query.PartNumber)) { query.PartNumber = query.PartNumber.Trim(); filters.Add("PartNumber = @PartNumber"); } if (!string.IsNullOrWhiteSpace(query.PartDescription)) { query.PartDescription = query.PartDescription.Trim(); filters.Add("PartDescription = @PartDescription"); } if (!string.IsNullOrWhiteSpace(query.Region)) { query.Region = query.Region.Trim(); filters.Add("Region = @Region"); } if (!string.IsNullOrWhiteSpace(query.Country)) { query.Country = query.Country.Trim(); filters.Add("Country = @Country"); } if (!string.IsNullOrWhiteSpace(query.Year)) { query.Year = query.Year.Trim(); filters.Add("Year = @Year"); } return(filters); }
// public async Task<SampleResults> GetAwards( // AwardsQuery query, int currentPage, int pageSize) // { // // await using var connection = new SqlConnection(connectionString); // await connection.OpenAsync(); // } public async Task <SampleResults> GetSamples(AwardsQuery query) { await using var connection = new SqlConnection(connectionString); await connection.OpenAsync(); var filters = ListFilters(query); var filter = filters.Any() ? $"WHERE {string.Join(" AND ", filters)}" : ""; var samples = await connection.QueryAsync <Sample>( $"SELECT * FROM samples {filter} ORDER BY Id ASC OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;", query); var count = await connection.QuerySingleAsync <int>( $"SELECT Count(*) FROM samples {filter}", query); return(new SampleResults { Count = count, Samples = samples, }); }
public Task <SampleResults> GetAwards(AwardsQuery query) { throw new System.NotImplementedException(); }