public IEntityCollectionInfo <Product> GetEntityCollectionInfo(int pageNumber, int pageSize) { var collectionInfo = new ProductCollectionInfo(); collectionInfo.Entities = _commonDb.Query <Product>($"select * from products order by id offset {pageNumber * pageSize - pageSize} rows fetch next {pageSize} rows only"); _commonDb.GetPageCount(pageSize, "select count(1) from products", collectionInfo); return(collectionInfo); }
public ProductCollectionInfo Get(int pageNumber, int pageSize) { try { var products = _commonDb.Query <ProductWithDescription>($"select * from ({SubQuery}) subQuery order by code offset {pageNumber * pageSize - pageSize} rows fetch next {pageSize} rows only"); var collectionInfo = new ProductCollectionInfo(); _commonDb.GetPageCount(pageSize, "select count(1) from products", collectionInfo); collectionInfo.Entities = products; return(collectionInfo); } catch (Exception e) { throw new ProductOperationException($"Products get error!", e); } }
public ProductCollectionInfo GetByFilterProducts(int pageNumber, int pageSize, Guid?type, Guid?unit, IEnumerable <Guid> categories) { var queryBuilder = new StringBuilder($"select p.* from ({SubQuery}) p").AppendLine(); var codes = categories as Guid[] ?? categories?.ToArray(); if (codes != null && codes.Any()) { var joinCodes = codes.Aggregate("", (c, n) => c + $"'{n}',"); var categoryFilterString = $"inner join (select * from productcategories where codeCategory in ({joinCodes.Remove(joinCodes.Length - 1)})) c on p.code = c.codeProduct"; queryBuilder.AppendLine(categoryFilterString); } var isWhere = false; if (type.HasValue) { isWhere = true; queryBuilder.AppendLine("where"); queryBuilder.AppendLine($"codeType = '{type.Value}'"); } if (unit.HasValue) { queryBuilder.AppendLine($"{(isWhere ? "and" : "where")} codeUnit = '{unit.Value}'"); } queryBuilder.AppendLine("group by p.code, p.description, p.price, p.isAvailable, p.deliveryDate, p.codeType, p.codeUnit"); queryBuilder.AppendLine($"order by code offset {pageNumber * pageSize - pageSize} rows fetch next {pageSize} rows only"); try { var query = queryBuilder.ToString(); var products = _commonDb.Query <ProductWithDescription>(query); var collectionInfo = new ProductCollectionInfo(); _commonDb.GetPageCount(pageSize, $"select count(1) from ({query}) countSet", collectionInfo); collectionInfo.Entities = products; return(collectionInfo); } catch (Exception e) { throw new ProductOperationException($"Products get error!", e); } }