How one could use server side sorting and paging with Azure Mobile Services

I am using jqGrid (inlineNav) with data from azure service and interested in learning how one could use server side sorting and paging with Azure Mobile Services.

Please share thoughts around this.

Windows Azure Mobile Services provides REST API which can be used to get/insert/edit/delete data of the the tables which you configured for the corresponding access (see the documentation). Query records operation request uses HTTP GET verb. It supports Open Data Protocol (OData) URI options $orderby, $skip, $top and $inlinecount which could be used to fill jqGrid.

$("#list4").jqGrid({
    url : 'https://mohit.azure-mobile.net/tables/Schedules',
    datatype: "json",
    height: "auto",
    colModel: [
        { name: "RouteId", width: 50 },
        { name: "Area", width: 130 }
    ],
    cmTemplate: {editable: true, editrules: { required: true}},
    rowList: [10, 20, 30],
    rowNum: 10,
    prmNames: { search: null, nd: null },
    ajaxGridOptions: {
        contentType: "application/json",
        headers: {
            "X-ZUMO-APPLICATION": "myKey"
        }
    },
    serializeGridData: function (postData) {
        if (postData.sidx) {
            return {
                $top: postData.rows,
                $skip: (parseInt(postData.page, 10) - 1) * postData.rows,
                $orderby: postData.sidx + " " + postData.sord,
                $inlinecount: "allpages"
            };
        } else {
            return {
                $top: postData.rows,
                $skip: (parseInt(postData.page, 10) - 1) * postData.rows,
                $inlinecount: "allpages"
            };
        }
    },
    beforeProcessing: function (data, textStatus, jqXHR) {
        var rows = parseInt($(this).jqGrid("getGridParam", "rowNum"), 10);
        data.total = Math.ceil(data.count/rows);
    },
    jsonReader: {
        repeatitems: false,
        root: "results",
        records: "count"
    },
    loadError: function (jqXHR, textStatus, errorThrown) {
        alert('HTTP status code: ' + jqXHR.status + '\n' +
            'textStatus: ' + textStatus + '\n' +
            'errorThrown: ' + errorThrown);
        alert('HTTP message body (jqXHR.responseText): ' + '\n' + jqXHR.responseText);
    },
    pager: "#pager1",
    sortname: "Area",
    viewrecords: true,
    caption: "Schedule Data",
    gridview: true
});

Some comments to the above code:

• I removed sortable: false to allow sorting of grid by click on the column header
• with respect of prmNames option one can remove sending of unneeded parameters to the server or rename it. I used prmNames: { search: null, nd: null } to deny sending of _search and nd options. One could use sort: "$orderby", rows: "$top" to rename two other parameters, but because we need to calculate $skip and append sord after sidx we need to use serializeGridData. So the renaming of other parameters are not needed in the case.
• using serializeGridData we construct the list of options which will be send to the server.
• ajaxGridOptions will be used to set additional parameters of jQuery.ajax request which do jqGrid internally for access to the server. The options which I use in the example set Content-Type: application/json and X-ZUMO-APPLICATION: myKey in the HTTP headers
• the response from the server don't contains total (the total number of pages), so we use beforeProcessing callback to fill the property based on other information before the response will be processed.
• because we use $inlinecount=allpages options in the URL the response from the server will contains information about the total number of records and the page of data will be wrapped in the results part of the answer. So we use jsonReader: {repeatitems: false, root: "results", records: "count"} to read the response.
• we have to remove loadonce: true option because the server returns only the requested page of data instead of the whole set of data.