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();
}
