Пример #1
0
        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);
            }
        }
Пример #2
0
        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);
        }