Exemple #1
0
        private string SqlClauseForQueryingProducts(GetProductCollectionRequest request)
        {
            var fields = new Dictionary <string, string>
            {
                { nameof(GetProductCollectionResult.ProductCollectionItem.Id), $"{nameof(Product)}.Id" },
                { nameof(GetProductCollectionResult.ProductCollectionItem.DisplayName), $"{nameof(Product)}.{nameof(Product.Name)}" }
            };

            var groupByFields  = string.Join(",", fields.Select(x => x.Value));
            var selectedFields = string.Join(", ", fields.Select(x => $"{x.Key}={x.Value}"));

            var sqlClauseBuilder = new StringBuilder($"SELECT {selectedFields}")
                                   .Append($" FROM {nameof(Product)} AS {nameof(Product)}");

            if (!string.IsNullOrWhiteSpace(request.SearchTerm))
            {
                sqlClauseBuilder = sqlClauseBuilder
                                   .Append($" WHERE {nameof(Product)}.{nameof(Product.Name)} LIKE @SearchTerm");
            }

            sqlClauseBuilder = sqlClauseBuilder
                               .Append($" GROUP BY {groupByFields}")
                               .Append($" ORDER BY {nameof(Product)}.{nameof(Product.Name)} ")
                               .Append(" OFFSET @Offset ROWS ")
                               .Append(" FETCH NEXT @PageSize ROWS ONLY; ");

            return(sqlClauseBuilder.ToString());
        }
        public async Task <GetProductCollectionResult> Handle(GetProductCollectionRequest request, CancellationToken cancellationToken)
        {
            await this._validator.ValidateAndThrowAsync(request, null, cancellationToken);

            var sqlClauses = new List <string>
            {
                this.SqlClauseForQueryingProducts(request),
                this.SqlClauseForCountProducts(request)
            };

            var combinedSqlClauses = string.Join("; ", sqlClauses);
            var parameters         = new
            {
                Offset     = (request.PageIndex - 1) * request.PageSize,
                PageSize   = request.PageSize,
                SearchTerm = $"%{request.SearchTerm}%"
            };

            using var connection = await this._connectionFactory.GetConnection(cancellationToken);

            var multiQueries = await connection.QueryMultipleAsync(combinedSqlClauses, parameters);

            var products = await multiQueries.ReadAsync <GetProductCollectionResult.ProductCollectionItem>();

            var totalProducts = await multiQueries.ReadFirstOrDefaultAsync <int>();

            var result = new GetProductCollectionResult
            {
                Products      = products ?? Enumerable.Empty <GetProductCollectionResult.ProductCollectionItem>(),
                TotalProducts = totalProducts
            };

            return(result);
        }
Exemple #3
0
        private string SqlClauseForCountProducts(GetProductCollectionRequest request)
        {
            var sqlClauseBuilder = new StringBuilder($"SELECT COUNT(*)")
                                   .Append($" FROM {nameof(Product)}");

            if (!string.IsNullOrWhiteSpace(request.SearchTerm))
            {
                sqlClauseBuilder = sqlClauseBuilder
                                   .Append($" WHERE {nameof(Product.Name)} LIKE @SearchTerm");
            }

            return(sqlClauseBuilder.ToString());
        }