【APS.NET Core】- Razor Page 使用jqgrid实现分页功能

本文将使用jqgrid在Razor Page中实现分页功能。

前台

List.cshtml代码如下：

@page
@model ListModel
@{
    Layout = "~/Pages/Shared/_Index.cshtml";
}

<script>
    $(function () {
        gridList();
    });

    function gridList() {
        var $gridList = $("#gridList");
        $gridList.dataGrid({
            url: "/Admin/User/List?handler=GridJson",
            height: $(window).height() - 128,
            colModel: [
                { label: '主键', name: 'F_Id', hidden: true },
                { label: '账户', name: 'F_Account',  80, align: 'left' },
                { label: '姓名', name: 'F_RealName',  80, align: 'left' },
                {
                    label: '性别', name: 'F_Gender',  60, align: 'center',
                    formatter: function (cellvalue) {
                        if (cellvalue === true) {
                            return '男';
                        } else {
                            return '女';
                        }
                    }
                },
                { label: '手机', name: 'F_MobilePhone',  80, align: 'left' },
                {
                    label: '公司', name: 'F_OrganizeId',  150, align: 'left',
                    formatter: function (cellvalue) {
                        return top.clients.organize[cellvalue] == null ? "" : top.clients.organize[cellvalue].fullname;
                    }
                },
                {
                    label: '部门', name: 'F_DepartmentId',  80, align: 'left',
                    formatter: function (cellvalue) {
                        return top.clients.organize[cellvalue] == null ? "" : top.clients.organize[cellvalue].fullname;
                    }
                },
                {
                    label: '岗位', name: 'F_DutyId',  80, align: 'left',
                    formatter: function (cellvalue) {
                        return top.clients.duty[cellvalue] == null ? "" : top.clients.duty[cellvalue].fullname;
                    }
                },
                {
                    label: '创建时间', name: 'F_CreatorTime',  80, align: 'left',
                    formatter: "date", formatoptions: { srcformat: 'Y-m-d', newformat: 'Y-m-d' }
                },
                {
                    label: "允许登录", name: "F_EnabledMark",  60, align: "center",
                    formatter: function (cellvalue) {
                        if (cellvalue) {
                            return '<span class="label label-success">正常</span>';
                        } else if (cellvalue === 0) {
                            return '<span class="label label-default">禁用</span>';
                        }
                    }
                },
                { label: '备注', name: 'F_Description',  200, align: 'left' }
            ],
            pager: "#gridPager",
            sortname: 'F_DepartmentId asc, F_CreatorTime desc',
            viewrecords: true
        });

        $("#btn_search").click(function () {
            $gridList.jqGrid('setGridParam', {
                postData: { keyword: $("#txt_keyword").val() }
            }).trigger('reloadGrid');
        });
    }

    function btn_add() {
        $.modalOpen({
            id: "Form",
            title: "新增用户",
            url: "/Admin/User/Form",
             "700px",
            height: "510px",
            callBack: function (iframeId) {
                top.frames[iframeId].submitForm();
            }
        });
    }

    function btn_edit() {
        var keyValue = $("#gridList").jqGridRowValue().F_Id;
        $.modalOpen({
            id: "Form",
            title: "修改用户",
            url: "/Admin/User/Form?keyValue=" + keyValue,
             "700px",
            height: "510px",
            callBack: function (iframeId) {
                top.frames[iframeId].submitForm();
            }
        });
    }

    function btn_delete() {
        $.deleteForm({
            url: "/Admin/User/List?handler=DeleteForm",
            param: { keyValue: $("#gridList").jqGridRowValue().F_Id },
            success: function () {
                $.currentWindow().$("#gridList").trigger("reloadGrid");
            }
        })
    }

    function btn_details() {
        var keyValue = $("#gridList").jqGridRowValue().F_Id;
        $.modalOpen({
            id: "Details",
            title: "查看用户",
            url: "/Admin/User/Details?keyValue=" + keyValue,
             "700px",
            height: "550px",
            btn: null,
        });
    }

    function btn_revisepassword() {
        var keyValue = $("#gridList").jqGridRowValue().F_Id;
        var account = $("#gridList").jqGridRowValue().F_Account;
        var realName = $("#gridList").jqGridRowValue().F_RealName;
        $.modalOpen({
            id: "RevisePassword",
            title: '重置密码',
            url: '/Admin/User/RevisePassword?keyValue=' + keyValue + "&account=" + escape(account) + '&realName=' + escape(realName),
             "450px",
            height: "260px",
            callBack: function (iframeId) {
                top.frames[iframeId].submitForm();
            }
        });
    }

    function btn_disabled() {
        var keyValue = $("#gridList").jqGridRowValue().F_Id;
        $.modalConfirm("注：您确定要【禁用】该项账户吗？", function (r) {
            if (r) {
                $.submitForm({
                    url: "/Admin/User/List?handler=DisabledAccount",
                    param: { keyValue: keyValue },
                    success: function () {
                        $.currentWindow().$("#gridList").trigger("reloadGrid");
                    }
                })
            }
        });
    }

    function btn_enabled() {
        var keyValue = $("#gridList").jqGridRowValue().F_Id;
        $.modalConfirm("注：您确定要【启用】该项账户吗？", function (r) {
            if (r) {
                $.submitForm({
                    url: "/Admin/User/List?handler=EnabledAccount",
                    param: { keyValue: keyValue },
                    success: function () {
                        $.currentWindow().$("#gridList").trigger("reloadGrid");
                    }
                })
            }
        });
    }
</script>

<div class="topPanel">
    <div class="toolbar">
        <div class="btn-group">
            <a class="btn btn-primary" onclick="$.reload()"><span class="glyphicon glyphicon-refresh"></span></a>
        </div>
        <div class="btn-group">
            <a id="NF-add" authorize="yes" class="btn btn-primary dropdown-text" onclick="btn_add()"><i class="fa fa-plus"></i>新建用户</a>
        </div>
        <div class="operate">
            <ul class="nav nav-pills">
                <li class="first">已选中<span>1</span>项</li>
                <li><a id="NF-edit" authorize="yes" onclick="btn_edit()"><i class="fa fa-pencil-square-o"></i>修改用户</a></li>
                <li><a id="NF-delete" authorize="yes" onclick="btn_delete()"><i class="fa fa-trash-o"></i>删除用户</a></li>
                <li><a id="NF-Details" authorize="yes" onclick="btn_details()"><i class="fa fa-search-plus"></i>查看用户</a></li>
                <li class="split"></li>
                <li><a id="NF-revisepassword" authorize="yes" onclick="btn_revisepassword()"><i class="fa fa-key"></i>密码重置</a></li>
                <li class="split"></li>
                <li><a id="NF-disabled" authorize="yes" onclick="btn_disabled()"><i class="fa fa-stop-circle"></i>禁用</a></li>
                <li><a id="NF-enabled" authorize="yes" onclick="btn_enabled()"><i class="fa fa-play-circle"></i>启用</a></li>
            </ul>
            <a href="javascript:;" class="close"></a>
        </div>
        <script>$('.toolbar').authorizeButton()</script>
    </div>
    <div class="search">
        <table>
            <tr>
                <td>
                    <div class="input-group">
                        <input id="txt_keyword" type="text" class="form-control" placeholder="账户/姓名/手机" style=" 200px;">
                        <span class="input-group-btn">
                            <button id="btn_search" type="button" class="btn  btn-primary"><i class="fa fa-search"></i></button>
                        </span>
                    </div>
                </td>
            </tr>
        </table>
    </div>
</div>
<div class="gridPanel">
    <table id="gridList"></table>
    <div id="gridPager"></div>
</div>

$gridList.dataGrid的定义见下一节。

jqgrid

jqGrid异步请求的默认请求参数属性包含：

{  
    page:"page",  //当前页码
    rows:"rows",  //每页数量  
    sort:"sidx",  //排序字段 
    order:"sord", //排序类型  
    search:"_search",   
    nd:"nd",   
    id:"id",   
    oper:"oper",   
    editoper:"edit",   
    addoper:"add",   
    deloper:"del",   
    subgridid:"id",   
    npage:null,   
    totalrows:"totalrows"  
}   

可以看到参数中包含名为page的参数，但是在Razor Page中名为page的参数已经被占用了，如图所示：

可以看到page参数是一个字符串类型，代表的是当前Page的地址。因此，不能使用再使用page作为当前页的名称，否则将会导致无法正常翻页。所以我们需要重新定义jqgrid的请求参数名称。可喜的是jqGrid提供了prmNames这个属性用来修改请求参数属性，因此我们对jqgrid做二级封装，代码如下：

$.fn.dataGrid = function (options) {
    var defaults = {
        datatype: "json",
        auto true,
        rownumbers: true,
        shrinkToFit: false,
        gridview: true,
        rowNum: 15,
        rowList: [10, 15, 30, 50, 100],
        prmNames: {
            'page': 'PageIndex',
            'rows': 'PageSize',
            'order': 'SortType',
            'sort': 'SortColumn'
        }
    };
    var options = $.extend(defaults, options);
    var $element = $(this);
    options["onSelectRow"] = function (rowid) {//选中某一行之后
        var length = $(this).jqGrid("getGridParam", "selrow").length;
        var $operate = $(".operate");
        if (length > 0) {
            $operate.animate({ "left": 0 }, 200);
        } else {
            $operate.animate({ "left": '-100.1%' }, 200);
        }

        $operate.find('.close').click(function () {
            $operate.animate({ "left": '-100.1%' }, 200);
        });
    };
    $element.jqGrid(options);
};

为了符合C#的属性命名规范，我们将几个需要接受的参数都使用匈牙利命名法。

后台

为了接收前台jqgird传递的参数，我们定义一个Class，内容如下：

    /// <summary>
    /// 分页信息
    /// </summary>
    public class PagerInfo
    {
        /// <summary>
        /// 表名称
        /// </summary>
        public string TableName { get; set; }

        /// <summary>
        /// 主键名称
        /// </summary>
        public string PKField { get; set; } = "id";

        /// <summary>
        /// 返回列 逗号分隔
        /// </summary>
        public string ReturnField { get; set; } = "*";

        /// <summary>
        /// 查询条件
        /// </summary>
        public string QueryString { get; set; }

        /// <summary>
        /// 排序列
        /// </summary>
        public string SortColumn { get; set; }

        /// <summary>
        /// 排序类型
        /// </summary>
        public string SortType { get; set; }

        /// <summary>
        /// 当前页
        /// </summary>
        public int PageIndex { get; set; }

        /// <summary>
        /// 每页条数
        /// </summary>
        public int PageSize { get; set; }

        /// <summary>
        /// 总记录数
        /// </summary>
        public int TotalCount { get; set; }

        /// <summary>
        /// 总页数
        /// </summary>
        public int PageCount
        {
            get
            {
                if (this.TotalCount <= 0)
                {
                    return 0;
                }

                return TotalCount % this.PageSize == 0 ? TotalCount / this.PageSize : TotalCount / this.PageSize + 1;
            }
        }
    }

代码中的红色部分就是与我们自定义的jqgrid请求参数对应。分页获取数据的方法：

        /// <summary>
        /// 分页获取对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pagerInfo"></param>
        /// <returns></returns>
        public static List<T> GetPageList<T>(PagerInfo pagerInfo)
        {
            using (var conn = ConnectionFactory.GetInstance())
            {
                var param = new DynamicParameters();
                param.Add("@tbName", pagerInfo.TableName);
                param.Add("@pkField", pagerInfo.PKField);
                param.Add("@pageIndex", pagerInfo.PageIndex);
                param.Add("@pageSize", pagerInfo.PageSize);
                param.Add("@returnField", pagerInfo.ReturnField);
                param.Add("@orderField", pagerInfo.SortColumn);
                param.Add("@queryStr", pagerInfo.QueryString);
                param.Add("@itemCount", 0, DbType.Int32, ParameterDirection.Output);
                param.Add("@pageCount", 0, DbType.Int32, ParameterDirection.Output);

                var list = conn.Query<T>("SP_Pagination", param, commandType: CommandType.StoredProcedure).ToList();
                pagerInfo.TotalCount = param.Get<int>("@itemCount");

                return list;
            }
        }

可以看到代码中调用了Mysql数据库中名为：SP_Pagination 的存储过程，该存储过程定义见下一节。

数据库

 SP_Pagination定义如下：

CREATE DEFINER=`root`@`%` PROCEDURE `SP_Pagination`(in tbName varchar(20),#表名  
    in returnField varchar(100),#要显示的列名  
    in queryStr varchar(500),#where条件(只需要写where后面的语句)  
    in orderField varchar(500),#排序条件（只需要写order by后面的语句）  
    in pageSize int,#每一页显示的记录数  
    in pageIndex int,#当前页  
    out itemCount int,
    out pageCount int)
BEGIN  
    if (pageSize < 1)then  
      set pageSize = 20;  
    end if;  
      
    if (pageIndex < 1)then  
      set pageIndex = 1;  
    end if;  
      
    if(LENGTH(queryStr)>0)then  
        set queryStr=CONCAT(' where ',queryStr);  
    end if;  
      
    if(orderField is not null and LENGTH(orderField)>0)then  
        set orderField = CONCAT(' order by ',orderField);  
    end if;  
      
    set @strsql = CONCAT('select ', returnField, ' from ', tbName, ' ', queryStr, ' ', orderField, ' limit ', pageIndex * pageSize - pageSize, ',', pageSize);  
      
    prepare stmtsql from @strsql;  
    execute stmtsql;  
    deallocate prepare stmtsql;  
      
    set @strsqlcount=concat('select count(1) as count into @itemCount from ',tbName,'',queryStr);  
    prepare stmtsqlcount from @strsqlcount;  
    execute stmtsqlcount;  
    deallocate prepare stmtsqlcount;  
    set itemCount=@itemCount;  
    set pageCount = (itemCount + pageIndex - 1) / pageSize;
END

至此，Razor Page分页功能实现完毕。需要特别注意的是jqgrid的page参数与Razor Page的page参数冲突的问题。