public Page <Product> GetPaged(int page, int pageSize, ProductFilter filter, string sortColumn = "Id", string sortOrder = "asc") { var countSql = new Sql().Select("count(*)"); countSql = ApplyFiltering(countSql, filter); var totalCount = db.ExecuteScalar <long>(countSql); var sql = new Sql().Select("Products.*, Categories.CategoryName"); sql = ApplyFiltering(sql, filter); RepositoryHelpers.ApplyOrdering(sql, sortColumn, sortOrder); var items = db.Fetch <Product, Category, Product>((p, c) => { p.Category = c; return(p); }, sql); return(new Page <Product>() { CurrentPage = page, Items = items, ItemsPerPage = pageSize, TotalItems = totalCount, TotalPages = (long)Math.Ceiling(totalCount / (double)pageSize) }); }
//public List<Category> GetPaged(int page, int pageSize) //{ // var sql = new Sql().Select("*") // .From<Category>(ctx.SqlSyntax) // .OrderBy<Category>(c => c.Id, ctx.SqlSyntax); // return db.Fetch<Category>(page, pageSize, sql); //} //public Page<Category> GetPaged(int page, int pageSize) //{ // var sql = new Sql().Select("*") // .From<Category>(ctx.SqlSyntax) // .OrderBy<Category>(c => c.Id, ctx.SqlSyntax); // return db.Page<Category>(page, pageSize, sql); //} public Page <Category> GetPaged(int page, int pageSize, CategoryFilter filter, string sortColumn = "Id", string sortOrder = "asc") { var sql = new Sql().Select("*") .From <Category>(ctx.SqlSyntax); if (!string.IsNullOrEmpty(filter.Name)) { sql = sql.Where <Category>( c => c.Name.ToLower().Contains(filter.Name.ToLower()), ctx.SqlSyntax); } RepositoryHelpers.ApplyOrdering(sql, sortColumn, sortOrder); return(db.Page <Category>(page, pageSize, sql)); }