为 JqGrid 添加分页

Add Paging for JqGrid(为 JqGrid 添加分页)

问题描述

我正在尝试使用一些博客中提供的示例将 jQuery Grid 添加到我的应用程序(C# 和 Asp.net)中，能够使用 Webservice 发送的 Json 数据.现在尝试为Grid添加分页，结果被击中.脚本是这样的.

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>

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

UPDATED:如果你使用SqlDataReader从数据库中获取数据，你可以根据SqlCommand构造SQL语句(SqlCommand)您从服务器接收的 code>rows 和 page 参数.

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.

在大多数情况下，您需要查询具有唯一 ID 的数据.因此，您可以使用 SELECT TOP 和 LEFT OUTER JOIN 构造实现分页.让我们通过一个例子来解释它.例如，您需要使用 dbo.Products 表中的价格查询 Product23654" rel="nofollow noreferrer">Northwind 数据库.要获取第一页数据，您可以使用

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 您应该替换为 rows 参数的值.要获取由参数 page 定义的另一个页面，您需要跳过 (page-1)*rows 项并获取下一个顶部 page 项.使用 公用表表达式 (CTE) 语法，您可以编写不同的语句简单:

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

你应该把上面两个地方的 10 和 20 替换为 rows 和 (page-1)*rows.如果您有一些不支持公用表表达式 (CTE) 的数据库，您可以针对子查询重写相同的查询:

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