public async Task <PaginatedList <Example> > Find(FilteredPageRequest pageRequest)
{
const string sqlTemplate = @"SELECT * FROM users OFFSET :offset LIMIT :pageSize;
SELECT COUNT(*) FROM users;";
// var sqlTemplate = @"
// SELECT *
// FROM example e
// WHERE e.name ILIKE :searchTerm
// {0}
// OFFSET :offset
// LIMIT :pageSize;
//
// SELECT COUNT(*)
// FROM example e
// WHERE e.name ILIKE :searchTerm;
// ";
var sanitisedSql = new SanitisedSql <Example>(sqlTemplate, pageRequest.OrderBy, pageRequest.OrderByAsc, "p");
var sql = sanitisedSql.ToSql();
var result = await _con.Db.QueryMultipleAsync(sql, new
{
pageRequest.SearchTerm,
offset = (int)pageRequest.Offset,
pageSize = (int)pageRequest.PageSize
});
var examples = await result.ReadAsync <Example>();
var count = await result.ReadSingleAsync <long>();
return(new PaginatedList <Example>(examples.ToList(), count, pageRequest));
}
//CRUD
public async Task <PaginatedList <User> > Find(FilteredPageRequest filteredPageRequest)
{
const string sqlTemplate = @"
SELECT *
FROM users u
WHERE u.name ILIKE :searchTerm
OFFSET :offset
LIMIT :pageSize;
SELECT COUNT(*)
FROM users u
WHERE u.name ILIKE :searchTerm
OFFSET :offset
LIMIT :pageSize;
";
var sanitisedSql = new SanitisedSql <User>(sqlTemplate, filteredPageRequest.OrderBy, filteredPageRequest.OrderByAsc, "u");
var sql = sanitisedSql.ToSql();
var result = await _con.Db.QueryMultipleAsync(sql, new
{
filteredPageRequest.SearchTerm,
offset = (int)filteredPageRequest.Offset,
pageSize = (int)filteredPageRequest.PageSize
});
var users = await result.ReadAsync <User>();
var count = await result.ReadSingleAsync <long>();
return(new PaginatedList <User>(users.ToList(), count, filteredPageRequest));
}