kendo ui grid filter, sort and paging in the server

Question

I'm using kendo grid and want to perform filtering, sorting and paging in the server. I understand that I should add to the dataSource:

serverPaging: true,
serverSorting: true

But how do I tell the grid/dataSource which url it should use for the sortig, filtering etc. And what if I want to perform the sortig myself? I want to use the control kendo provides but to go to the server myself. Is there an event like "sortTriggered" where I can call "prevntDefault" or something like that... I don't know.

Solution 1

Take a look at this sample. It is using the MobileServices javascript api for Windows Azure, but shows you how to handle server paging and sorting on your own.

http://jsbin.com/efeKiwO/11/edit

On the transport function of your dataSource, each method (read,update,create,destroy) can be configured to a function (this is the read function, it handles any sorting and paging).

read: function(options) {
        // Get the table
        var table = client.getTable("Customer");

        // Build base query
        var query = table.includeTotalCount();

        // Add paging
        if(options.data.skip !== undefined && options.data.take !== undefined) {
          query = query.skip(options.data.skip).take(options.data.take);
        }

        // Add sorting
        if(typeof options.data.sort !== "undefined" && options.data.sort !== null) {
          for(var i = 0; i< options.data.sort.length; i++) {
            if(options.data.sort[i].dir === "desc") {
              query = query.orderByDescending(options.data.sort[i].field);
            }
            else {
              query = query.orderBy(options.data.sort[i].field);
              }
          }
        }

        var promise = query.read();

        promise.done(function(data) {
          options.success(data);
        });
      },

Inside that function you can do whatever you like. Instead of using a javascript library like this sample, you could make a $.getJSON call, or a $.ajax call, or whatever else you want to do. The parameter object to the function will contain everything you need for paging, sorting, and filtering. Once you have the data, just call options.success(dataSet); with your properly sorting/paged dataSet and your grid will bind to it.

Solution 2

Your configuration is almost there,

What's missing is the secret sauce to connect to MVC.

Lets assume your DataSource configuration is like this:

var myDataSource = new kendo.data.DataSource({
    transport: {
        read: {
            url: 'Users/Read',
            type: 'POST'
        }
    },
    serverSorting: true,
    serverFiltering: true,
    serverPaging: true
}

On your server side in UsersController.cs (example), you have to receive a [DataSourceRequest]

public DataSourceResult Read([DataSourceRequest] DataSourceRequest request)
{
    // Here you might actually get the items from your cache or database. 
    var List<User> myList = new List<User>();         

    // Here is when the kendo magic happens. 
    return myList.ToDataSourceResult(request);
}

Why [DataSourceRequest] is important?

Because it contains the paging, sorting, filtering parameters that your grid is asking to the server. So if you want to do the algorithm yourself, you must examin the request and process those paramenters. Just remember to return a DataSourceResult object instance.

If your objects live in a cache and your fields need no special treatment for filtering, grouping, sorting, etc, then just use the kendo C# extension ToDataSourceResult. It will process your items and apply the filtering, sorting, paging configuration, using dynamic LINQ statements.

Comments

[julius_am]

So I can't have any url I want? What if I have a special url I can't use it?

[The_Black_Smurf]

You mean that you have different URL based on the sort/page that you want?

[The_Black_Smurf]

Can you add some examples to your question?

[julius_am]

That's exactly what I was trying to do but for some reason when I add "serverSorting" to the data source the sorting doesn't work and the read function is not called.

[The_Black_Smurf]

You don't need the ServerSorting if you handle it on the client side.

[julius_am]

If I remove the "serverSorting" the read function definitly will not be called. Anyway, I fixed the problem. It's working now, thanks.

[James Reategui]

Assuming entity framework or other IQueryable result from server side, how would .ToDataSourceResult work... From my understanding it would do the paging/sorting etc in memory, instead of letting sql server do the heavy lifting...

[Adrian Salazar]

Well, that's more a question to be asked to the kendo guys. I've examined a bit of their C# code and yes they are using LINQ to make all the transformations needed, so it actually depends on what is backing up your IEnumerable. So it is possible that the DB will make all the heavy lifting in the end. Just don't materialise your query into in-memory objects before passing it to kendo. Disclaimer: we haven't bought a license to use the latest kendo version in my company though, as we don't depend on that framework anymore, so my knowledge on server-side kendo could be way outdated.