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));
}
