JQGrid二种方法加载Json数据-优快云博客

方法一：一次加载所有数据到前台页面再分页

前台页面：

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="pagegrid.aspx.vb" Inherits="WebExt.pagegrid" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title></title>
    <link id="uiThemes" rel="stylesheet" type="text/css" media="screen" href="../jqgrid/css/ui-lightness/jquery-ui-1.8.13.custom.css" />
    <link rel="stylesheet" type="text/css" media="screen" href="../jqgrid/css/ui.jqgrid.css" />
    <script type="text/javascript" src="../jqgrid/js/jquery-1.5.2.min.js"></script>
    <script src="../jqgrid/js/i18n/grid.locale-cn.js" type="text/javascript"></script>
    <script src="../jqgrid/js/jquery.jqGrid.min.js" type="text/javascript"></script>
    <script src="../pub/json2.js" type="text/javascript"></script>
    <script type="text/javascript">
        $(function () {
            jQuery("#list2").jqGrid({
                url: 'JQGridService.asmx/GetJQGridData2',
                data: {},
                datatype: 'json',
                mtype: 'POST',
                loadonce: true,
                ajaxGridOptions: { contentType: 'application/json; charset=utf-8' },
                serializeGridData: function (postData) {
                    return JSON.stringify(postData);
                },
                jsonReader: {
                    root: function (obj) {
                        var data = eval("(" + obj.d + ")");
                        return data.rows;
                    },
                    page: function (obj) {
                        var data = eval("(" + obj.d + ")");
                        return data.page;
                    },
                    total: function (obj) {
                        var data = eval("(" + obj.d + ")");
                        return data.total;
                    },
                    records: function (obj) {
                        var data = eval("(" + obj.d + ")");
                        return data.records;
                    },
                    repeatitems: false 
                },
                colNames: ['Inv No', 'name', 'sex', 'email','age'],
                colModel: [
   		            { name: 'ID', index: 'ID', width: 55 },
   		            { name: 'Names', index: 'Names', width: 90 },
   		            { name: 'Sex', index: 'Sex', width: 100 },
   		            { name: 'Age', index: 'Age', width: 80, align: "right" },
   		            { name: 'Remark', index: 'Remark', width: 80, align: "right" }
   	            ],
   		        rowNum: 10,
                width:600,
                rowList: [10, 20, 30],
                pager: '#pager2',
                sortname: 'name',
                viewrecords: true,
                sortorder: "asc",
                caption: "JSON Example"
            });
            jQuery("#list2").jqGrid('navGrid', '#pager2', { edit: false, add: false, del: false });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table id="list2" style="width:600px;"></table> <div id="pager2"></div>
    </div>
    </form>
</body>
</html>

重点在loadonce设置为true,jsonReader的repeatitems设置true,表示用JqGrid标准格式，否则不用标准。

后台代码：

    <WebMethod(EnableSession:=True)> _
    Public Function GetJQGridData2() As String
        Dim jsonData, strSql As String
        Dim strConn As String
        Dim conn As SqlConnection
        Dim da As SqlDataAdapter
        Dim ds As New DataSet

        strSql = "select * from employe"

        strConn = System.Configuration.ConfigurationManager.AppSettings("dbConnection")
        conn = New SqlConnection(strConn)
        da = New SqlDataAdapter(strSql, conn)
        conn.Open()
        da.Fill(ds)

        jsonData = JsonConvert.SerializeObject(ds.Tables(0), New DataTableConverter())
        jsonData = "{ ""page"": 1, ""total"": 2, ""records"": 15, ""rows"": " + jsonData + "}"

        Return jsonData
    End Function

方法二：加载请求的页面数据

前台代码：修改loadonce: false,

后台代码：

<WebMethod(EnableSession:=True)> _
    Public Function GetJQGridDBData(ByVal rows As Integer, ByVal page As Integer, ByVal sidx As String, ByVal sord As String _
                                    ) As String
        '参数
        'rows是当前要显示多少条数据给GRID的行数
        'page是当前页数
        'sidx是排序的列名
        'sord是排序的顺序
        'ByVal searchField As String, ByVal searchString As String
        Dim searchField, searchString As String

        Dim strConn As String
        Dim conn As SqlConnection
        Dim da As SqlDataAdapter
        Dim ds As New DataSet
        Dim jsonData As String
        Dim pagesize As Integer = rows
        Dim pages As Integer
        Dim strSql As String


        'pages 计算前pages 条数据
        'pagesize(每页显示多少条数据)
        'pageNumber 页数 从客户端传来
        pages = pagesize * (page - 1) + 1


        strSql = "select top " + rows.ToString() + " * from employe where 1=1 "
        If Not String.IsNullOrEmpty(searchField) And Not String.IsNullOrEmpty(searchString) Then
            strSql += " and " + searchField + "='" + searchString + "' "
        End If
        strSql += " and id>=( select max(id) "
        strSql += " from (select top " + pages.ToString() + " id from employe"
        strSql += " order by " + sidx + " " + sord + ")t );select * from employe"

        strConn = System.Configuration.ConfigurationManager.AppSettings("dbConnection")
        conn = New SqlConnection(strConn)
        da = New SqlDataAdapter(strSql, conn)
        conn.Open()
        da.Fill(ds)


        Dim records As String
        Dim total As String
        records = ds.Tables(1).Rows.Count
        total = Int(ds.Tables(1).Rows.Count / rows) + 1

        jsonData = "{ ""page"": " + page.ToString() + ", ""total"": " + total.ToString() + ", ""records"": " + records + ", ""rows"": ["
        'jsonData += "{""id"":1,""cell"":[1,""001"",""中国"",""aa"",""12""]},"
        For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
            jsonData += "{""id"":" + (i + 1).ToString() + ",""cell"":[" + ds.Tables(0).Rows(i)(0).ToString() + ",""" + ds.Tables(0).Rows(i)(1).ToString() + """,""" + ds.Tables(0).Rows(i)(2).ToString() + """,""" + ds.Tables(0).Rows(i)(3).ToString() + """,""" + ds.Tables(0).Rows(i)(4).ToString() + """]}"
            If i <> ds.Tables(0).Rows.Count - 1 Then
                jsonData += ","
            End If
        Next

        jsonData += "]}"

        conn.Close()

        '数据中
        'page当前页数
        'total总页数
        'records记录总数
        'rows列内容
        Return jsonData
    End Function

注意：

JQGrid标准数据格式：

{ “page“: 1, “total“: 2, “records“: 15, “rows“: [
{"id“:1,“cell“:[1,“001“,“中国“,“aa“,“12“]},
{"id“:2,“cell“:[2,“001“,“上海“,“aa“,“12“]},
{"id“:3,“cell“:[3,“001“,“天津“,“aa“,“12“]}
]}

非标准：

{ “page“: 1, “total“: 2, “records“: 15, “rows“: [
{“id“:“1“,“sex“:“001“,“name“:“中国1“,“email“:“aa“,“age“:“12“},
{“id“:“2“,“sex“:“001“,“name“:“中国2“,“email“:“aa“,“age“:“12“},
{“id“:“3“,“sex“:“001“,“name“:“中国3“,“email“:“aa“,“age“:“12“}
]}