Пример #1
0
        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));
        }
Пример #2
0
        //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));
        }
Пример #3
0
        public async Task <PaginatedList <User> > Find(FilteredPageRequest filteredPageRequest)
        {
            if (!_user.IsAuthorisedAtLevel(UserRole.Admin))
            {
                throw new UnauthorizedAccessException();
            }

            return(await _userRepository.Find(filteredPageRequest));
        }
Пример #4
0
 public async Task <PaginatedList <Example> > Find(FilteredPageRequest pageRequest)
 {
     return(await _exampleRepository.Find(pageRequest));
 }