public async Task <IndentDTOPage> GetIndentsPerPage(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);
}
}
IndentPage indentsSinglePageModel = await _database.Indents.GetIndentsPerPage(page, pageSize, categoriesValues);
IndentDTOPage indentsDTOSinglePageModel = _mapper.Map <IndentDTOPage>(indentsSinglePageModel);
indentsDTOSinglePageModel.Page = ++page;
indentsDTOSinglePageModel.PageSize = pageSize;
return(indentsDTOSinglePageModel);
}
public async Task <IndentPage> GetIndentsPerPage(int page, int pageSize, List <int> categories)
{
string sqlQuery = @"
DECLARE @NumberOfIndents INT;
SET @NumberOfIndents = (
SELECT Count(Indent.IndentId) FROM Indent
LEFT JOIN Executor ON Executor.ExecutorId = Indent.ExecutorId
WHERE CategoryId IN @CategoryList
)
SELECT @NumberOfIndents AS Number;
SELECT * FROM Indent
LEFT JOIN Executor ON Executor.ExecutorId = Indent.ExecutorId
WHERE CategoryId IN @CategoryList
ORDER BY IndentId 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 <Indent> indents = new List <Indent>();
int numberOfAllIndents;
using (var multi = await connection.QueryMultipleAsync(sqlQuery, new
{
Page = page,
PageSize = pageSize,
CategoryList = categories.ToArray <int>()
}))
{
var numberOfIndents = await multi.ReadAsync <int>();
indents = multi.Read <Indent, Executor, Indent>((Indent, Executor) =>
{
Indent.Executor = new Executor();
Indent.Executor = Executor;
return(Indent);
},
splitOn: "IndentId, ExecutorId");
numberOfAllIndents = numberOfIndents.Single();
}
IndentPage indentsSinglePageModel = new IndentPage()
{
IndentsOnPage = indents,
NumberOfAllIndentsWithSomeCategory = numberOfAllIndents
};
return(indentsSinglePageModel);
}
}