c# - Add Paging for JqGrid

Question

am trying to add jQuery Grid into my application(C# and Asp.net) using samples provided in some blogs, able to use Json data sent by Webservice. Now have tried to add pagination for the Grid and got strucked.Script is like this.

    <script type="text/javascript">
    $(function () {
        $("#table").jqGrid({
            datatype: function (pdata) { getData(pdata); },
            height: 250,
            colNames: ['ID', 'First Name', 'Last Name'],
            colModel: [
            { name: 'ID', width: 60, sortable: false },
            { name: 'FirstName', width: 200, sortable: false },
            { name: 'LastName', width: 200, sortable: false }
        ],

            imgpath: '<%= ResolveClientUrl("styles/redmon/images") %>',

            pager: jQuery('#pager'),
            rowNum: 2,
            rowList: [2, 5, 10, 50, 100, 200, 500, 1000],                
            height: "100%",
            viewrecords: true,
            scrollOffset: 0,
            caption: 'Sample'

        });
    });
    function getData(pData) {
        $.ajax({
            type: 'POST',
            contentType: "application/json; charset=utf-8",
            url: '<%= ResolveClientUrl("~/WebService.asmx/GetListOfPersons") %>',
            data: '{}',
            dataType: "json",
            success: function (data, textStatus) {
                if (textStatus == "success")
                    ReceivedClientData(JSON.parse(getMain(data)).rows);
            },
            error: function (data, textStatus) {
                alert('An error has occured retrieving data!');
            }
        });
    }
    function ReceivedClientData(data) {
        var thegrid = $("#table");
        thegrid.clearGridData();
        for (var i = 0; i < data.length; i++)
            thegrid.addRowData(i + 1, data[i]);
    }
    function getMain(dObj) {
        if (dObj.hasOwnProperty('d'))
            return dObj.d;
        else
            return dObj;
    }
</script>

...html block

     <table id="table" cellpadding="0" cellspacing="0">
</table>
<div id="pager" class="scroll" style="text-align:center;"></div> 

The Pager div is displayed and attached but isnt working am I missing something?

Thanks Samuel

Answer 1

You main problem is that you ignore the pData of the getData which can be forwarded to your ASMX web service.

You use very old template for your jqGrid. The current version of jqGrid now 4.3 and you use still imgpath which was already deprecated in the version 3.5 (see the documentation). Very old version of jqGrid had no good support for calling of Web services, but even at the time one could already use addJsonData and addXmlData methods to add the data more effectively as you do with respect of addRowData. It is documented here.

I recommend you better instead of modifying of getData function use datatype: 'json' instead of datatype as function. In the old demo for example you can find an example how to implement this exactly. In another answer you can find an example how to use loadonce: true parameter in case if you prefer don't implement data paging on the server and instead of that want send all the grid data to the client side and allow jqGrid do paging, sorting and filtering the data for you on the client side. It can work effective only with relatively small number of rows (some hundred rows for example).

UPDATED: If you use SqlDataReader to get the data from the database you can construct the SQL statement (SqlCommand) base on the rows and page parameters which you receive from the server.

In the most cases you need query the data which has unique ids. So you can implement paging using of SELECT TOP and LEFT OUTER JOIN construction. Let us I explain it on an example. For example you need to query Product with the price from the dbo.Products table of the Northwind database. To get first page of data you can use

SELECT TOP(10) ProductID, ProductName, UnitPrice FROM dbo.Products

where 10 you should replace to the value of the rows parameter. To get another page defined by parameter page you need skip (page-1)*rows items and get the next top page items. Using common table expression (CTE) syntax you can write the statement vary easy:

WITH GetAll (Id,ProductName,UnitPrice) AS (
    SELECT ProductID,ProductName,UnitPrice FROM dbo.Products
), GetTop (Id,ProductName,UnitPrice) AS (
    SELECT TOP(20) * FROM GetAll
), GetNext (Id,ProductName,UnitPrice) AS (
    SELECT TOP(10) a.* FROM GetAll AS a
        LEFT OUTER JOIN GetTop AS t ON t.Id = a.Id
    WHERE t.Id IS NULL
)
SELECT * FROM GetNext

You should just replace 10 and 20 on two places above to rows and (page-1)*rows. If you has some database which not support common table expression (CTE) you can rewrite the same query with respect of subqueries:

SELECT TOP(10) a.* FROM (SELECT ProductID,ProductName,UnitPrice FROM dbo.Products)
        AS a LEFT OUTER JOIN
            (SELECT TOP(20) ProductID,ProductName,UnitPrice FROM dbo.Products) AS t
                ON t.ProductID = a.ProductID
WHERE t.ProductID IS NULL