public async Task <ExecutorPage> GetExecutorsPerPage(int page, int pageSize, List <int> categories)
{
string sqlQuery = @"
SELECT COUNT(DISTINCT Executor.ExecutorId) AS Number FROM Executor
JOIN CategoryExecutor ON CategoryExecutor.ExecutorId = Executor.ExecutorId
WHERE CategoryExecutor.CategoryId IN @CategoryList;
SELECT DISTINCT Executor.* FROM Executor
JOIN CategoryExecutor ON CategoryExecutor.ExecutorId = Executor.ExecutorId
WHERE CategoryExecutor.CategoryId IN @CategoryList
ORDER BY Executor.ExecutorId DESC
OFFSET @Page*@PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;";
if (categories == null)
{
categories = new List <int>()
{
0, 1, 2, 3, 4, 5, 6
};
}
using (SqlConnection connection = new SqlConnection(_connectionString))
{
await connection.OpenAsync();
IEnumerable <Executor> executors = new List <Executor>();
int numberOfAllExecutors;
using (var multi = await connection.QueryMultipleAsync(sqlQuery, new
{
Page = page,
PageSize = pageSize,
CategoryList = categories.ToArray <int>()
}))
{
var numberOfExecutors = await multi.ReadAsync <int>();
numberOfAllExecutors = numberOfExecutors.Single();
executors = await multi.ReadAsync <Executor>();
}
ExecutorPage executorsSinglePageModel = new ExecutorPage()
{
ExecutorsOnPage = executors,
NumberOfAllExecutorsWithSomeCategory = numberOfAllExecutors
};
return(executorsSinglePageModel);
}
}
public async Task <ExecutorDTOPage> GetExecutorsPerPage(int page, int pageSize, List <Category> categories)
{
List <int> categoriesValues = null;
if (categories != null)
{
categoriesValues = new List <int>();
foreach (Category category in categories)
{
categoriesValues.Add((int)category);
}
}
ExecutorPage executorsSinglePageModel = await _database.Executors.GetExecutorsPerPage(page, pageSize, categoriesValues);
ExecutorDTOPage executorsDTOSinglePageModel = _mapper.Map <ExecutorDTOPage>(executorsSinglePageModel);
executorsDTOSinglePageModel.Page = ++page;
executorsDTOSinglePageModel.PageSize = pageSize;
return(executorsDTOSinglePageModel);
}