Paging with KendoUI and ServiceStack using remote data

Question

Is it possible to do server-side paging with ServiceStack and consume it via JSON/AJAX with a KendoUI grid? I have a large amount of data (30,000+ rows) that will need to be paged. I need the smallest payload over the wire. All examples for Kendo's grid shows the paging parameters set client-side but ServiceStack does not appear to use them. This makes me worried that everything will be sent over the wire. This is running in a ASP.NET MVC4 application.

Answer 1

You're right that most of the KendoUI examples use client-side paging, which does ship the entire dataset over the wire first. I ran into this issue a few months back and ultimately chose a different overall approach, but there is what I had.

You can take one of two basic approaches:

1. Configure the KendoUI DataSource using the transport setting to change the KendoUI query into one that is more copacetic with ServiceStack.
2. Configure "something" in ServiceStack to understand KendoUI paging.

I went with #2, using a global request filter. This is incomplete code, but the idea was to handle paging, sorting, and filtering. You might be fine just keeping the Page, PageSize, Skip and Take parameters, then manually applying paging.

First, the DTO. For any ServiceStack DTO, inherit from this base class as well. I know this flies in the face of Mythz's (very valid) opinion on DTO design, but this approach won't work well elsewise.

public class KendoQueryBase : IKendoFilter, IKendoSort, IKendoPaged
{
    public FilterTerm Filter { get; set; }
    public List<SortTerm> Sort { get; set; }

    public int? Page { get; set; }
    public int? PageSize { get; set; }
    public int? Skip { get; set; }
    public int? Take { get; set; }
}

Then use a global request filter to transform the request into a format that can be interpreted by ServiceStack. Again, if you're just doing paging, this probably isn't needed. It is also incomplete, but shows a decent starting point.

class KendoQueryPlugin : IPlugin
{
    public void Register(IAppHost appHost)
    {
        appHost.GlobalRequestFilters.Add((req, resp, dto) =>
        {
            if (dto is KendoQueryBase)
            {
                KendoQueryBase qb = dto as KendoQueryBase;
                if (qb.Sort == null) qb.Sort = new List<SortTerm>();
                Dictionary<string, string> qs = req.QueryString.ToDictionary();

                // Create the Sort Terms
                var i = 0;
                while (qs.ContainsKey("sort[{0}][field]".Fmt(i)))
                {
                    qb.Sort.Add(new SortTerm()
                    {
                        Field = qs["sort[{0}][field]".Fmt(i)],
                        Dir = qs["sort[{0}][dir]".Fmt(i)]
                    });
                    i++;
                }

                i = 0;
            }
        });
    }
}

EDIT

To further address paging specifically, I also wrote a helper extension that leverages the ServiceStack.OrmLite library and inserts paging into a SqlExpression:

public static class PagingExtensions
{
    public static SqlExpression<T> Page<T>(this SqlExpression<T> exp, int? page, int? pageSize)
    {
        if (!page.HasValue || !pageSize.HasValue)
            return exp;

        if (page <= 0) throw new ArgumentOutOfRangeException("page", "Page must be a number greater than 0.");
        if (pageSize <= 0) throw new ArgumentOutOfRangeException("pageSize", "PageSize must be a number greater than 0.");

        int skip = (page.Value - 1) * pageSize.Value;
        int take = pageSize.Value;

        return exp.Limit(skip, take);
    }
}

You can use it with the above DTO schema like this:

if (!request.Page.HasValue) request.Page = 1;
if (!request.PageSize.HasValue || request.PageSize < 0 || request.PageSize > 100)
    request.PageSize = 10;

var exp = Db.From<Your Database Object>
....
var results = Db.Select<YourDTO>(exp.Page(request.Page, request.PageSize)),