public async Task <Pagination <ProductListResponseModel> > GetAllProducts(ProductListRequestModel requestModel)
{
var parameters = new DynamicParameters();
parameters.Add("@PageSize", requestModel.PageSize);
parameters.Add("@Offset", requestModel.Page);
await using var connection = _dbConnection.CreateDbConnection();
var products = await connection.QueryMultipleAsync(ProductQuery(requestModel), parameters);
var productListResponseModel = new ProductListResponseModel();
var productResult = products.Read <ProductServiceResponseModel, CategoryServiceResponseModel, ProductListResponseModel>
((product, category) => {
product.CategoryServiceResponseModel = category;
productListResponseModel.ProductServiceResponseModel = product;
return(productListResponseModel);
}
, splitOn: "CategoryId");
var totalItemCount = products.Read <int>().FirstOrDefault();
var productResponse = PaginationService.GetPagination(
productResult.AsQueryable(),
requestModel.Page.Value,
string.Empty,
true,
requestModel.PageSize.Value);
return(productResponse);
}
private static string ProductQuery(ProductListRequestModel requestModel)
{
var sortingString = new StringBuilder();
var sortType = ProductConstants.Asc;
if (requestModel.IsOrderByDesc)
{
sortType = ProductConstants.Desc;
}
;
if (!string.IsNullOrEmpty(requestModel.OrderByField))
{
sortingString = new StringBuilder($" ORDER BY A.{requestModel.OrderByField} {sortType}");
}
var query = new StringBuilder();
query.Append($@"
SELECT * FROM
(select
P.Id AS ProductId,
p.ProductName,
p.StockQuantity,
C.Id as CategoryId,
C.CategoryName
from Product (nolock) as P
inner join Category (NOLOCK) as C
on P.CategoryId = C.Id
ORDER BY P.Id
OFFSET (@Offset - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY
) A
{sortingString}
;
");
query.Append(@" SELECT COUNT(ProductId) AS [TotalItemCount]
FROM
(
SELECT P.Id AS ProductId
FROM Product (NOLOCK) P
INNER JOIN Category (NOLOCK) C ON C.Id = P.CategoryId
) A;
");
return(query.ToString());
}
public async Task <IActionResult> Get([FromQuery] ProductListRequestModel requestModel)
{
var order = await _productService.GetAllProducts(requestModel);
return(Ok(order));
}
