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

 ?1 @page ?2 @model ListModel ?3 @{ ?4 ????Layout = "~/Pages/Shared/_Index.cshtml"; ?5 } ?6 ??7 <script> ?8 ????$(function () { ?9 ????????gridList(); 10 ????}); 11 ?12 ????function gridList() { 13 ????????var $gridList = $("#gridList"); 14 ????????$gridList.dataGrid({ 15 ????????????url: "/Admin/User/List?handler=GridJson", 16 ????????????height: $(window).height() - 128, 17 ????????????colModel: [ 18 ????????????????{ label: ‘主键‘, name: ‘F_Id‘, hidden: true }, 19 ????????????????{ label: ‘账户‘, name: ‘F_Account‘, width: 80, align: ‘left‘ }, 20 ????????????????{ label: ‘姓名‘, name: ‘F_RealName‘, width: 80, align: ‘left‘ }, 21 ????????????????{ 22 ????????????????????label: ‘性别‘, name: ‘F_Gender‘, width: 60, align: ‘center‘, 23 ????????????????????formatter: function (cellvalue) { 24 ????????????????????????if (cellvalue === true) { 25 ????????????????????????????return ‘男‘; 26 ????????????????????????} else { 27 ????????????????????????????return ‘女‘; 28 ????????????????????????} 29 ????????????????????} 30 ????????????????}, 31 ????????????????{ label: ‘手机‘, name: ‘F_MobilePhone‘, width: 80, align: ‘left‘ }, 32 ????????????????{ 33 ????????????????????label: ‘公司‘, name: ‘F_OrganizeId‘, width: 150, align: ‘left‘, 34 ????????????????????formatter: function (cellvalue) { 35 ????????????????????????return top.clients.organize[cellvalue] == null ? "" : top.clients.organize[cellvalue].fullname; 36 ????????????????????} 37 ????????????????}, 38 ????????????????{ 39 ????????????????????label: ‘部门‘, name: ‘F_DepartmentId‘, width: 80, align: ‘left‘, 40 ????????????????????formatter: function (cellvalue) { 41 ????????????????????????return top.clients.organize[cellvalue] == null ? "" : top.clients.organize[cellvalue].fullname; 42 ????????????????????} 43 ????????????????}, 44 ????????????????{ 45 ????????????????????label: ‘岗位‘, name: ‘F_DutyId‘, width: 80, align: ‘left‘, 46 ????????????????????formatter: function (cellvalue) { 47 ????????????????????????return top.clients.duty[cellvalue] == null ? "" : top.clients.duty[cellvalue].fullname; 48 ????????????????????} 49 ????????????????}, 50 ????????????????{ 51 ????????????????????label: ‘创建时间‘, name: ‘F_CreatorTime‘, width: 80, align: ‘left‘, 52 ????????????????????formatter: "date", formatoptions: { srcformat: ‘Y-m-d‘, newformat: ‘Y-m-d‘ } 53 ????????????????}, 54 ????????????????{ 55 ????????????????????label: "允许登录", name: "F_EnabledMark", width: 60, align: "center", 56 ????????????????????formatter: function (cellvalue) { 57 ????????????????????????if (cellvalue) { 58 ????????????????????????????return ‘<span class=\"label label-success\">正常</span>‘; 59 ????????????????????????} else if (cellvalue === 0) { 60 ????????????????????????????return ‘<span class=\"label label-default\">禁用</span>‘; 61 ????????????????????????} 62 ????????????????????} 63 ????????????????}, 64 ????????????????{ label: ‘备注‘, name: ‘F_Description‘, width: 200, align: ‘left‘ } 65 ????????????], 66 ????????????pager: "#gridPager", 67 ????????????sortname: ‘F_DepartmentId asc, F_CreatorTime desc‘, 68 ????????????viewrecords: true 69 ????????}); 70 ?71 ????????$("#btn_search").click(function () { 72 ????????????$gridList.jqGrid(‘setGridParam‘, { 73 ????????????????postData: { keyword: $("#txt_keyword").val() } 74 ????????????}).trigger(‘reloadGrid‘); 75 ????????}); 76 ????} 77 ?78 ????function btn_add() { 79 ????????$.modalOpen({ 80 ????????????id: "Form", 81 ????????????title: "新增用户", 82 ????????????url: "/Admin/User/Form", 83 ????????????width: "700px", 84 ????????????height: "510px", 85 ????????????callBack: function (iframeId) { 86 ????????????????top.frames[iframeId].submitForm(); 87 ????????????} 88 ????????}); 89 ????} 90 ?91 ????function btn_edit() { 92 ????????var keyValue = $("#gridList").jqGridRowValue().F_Id; 93 ????????$.modalOpen({ 94 ????????????id: "Form", 95 ????????????title: "修改用户", 96 ????????????url: "/Admin/User/Form?keyValue=" + keyValue, 97 ????????????width: "700px", 98 ????????????height: "510px", 99 ????????????callBack: function (iframeId) {100 ????????????????top.frames[iframeId].submitForm();101 ????????????}102 ????????});103 ????}104 105 ????function btn_delete() {106 ????????$.deleteForm({107 ????????????url: "/Admin/User/List?handler=DeleteForm",108 ????????????param: { keyValue: $("#gridList").jqGridRowValue().F_Id },109 ????????????success: function () {110 ????????????????$.currentWindow().$("#gridList").trigger("reloadGrid");111 ????????????}112 ????????})113 ????}114 115 ????function btn_details() {116 ????????var keyValue = $("#gridList").jqGridRowValue().F_Id;117 ????????$.modalOpen({118 ????????????id: "Details",119 ????????????title: "查看用户",120 ????????????url: "/Admin/User/Details?keyValue=" + keyValue,121 ????????????width: "700px",122 ????????????height: "550px",123 ????????????btn: null,124 ????????});125 ????}126 127 ????function btn_revisepassword() {128 ????????var keyValue = $("#gridList").jqGridRowValue().F_Id;129 ????????var account = $("#gridList").jqGridRowValue().F_Account;130 ????????var realName = $("#gridList").jqGridRowValue().F_RealName;131 ????????$.modalOpen({132 ????????????id: "RevisePassword",133 ????????????title: ‘重置密码‘,134 ????????????url: ‘/Admin/User/RevisePassword?keyValue=‘ + keyValue + "&account=" + escape(account) + ‘&realName=‘ + escape(realName),135 ????????????width: "450px",136 ????????????height: "260px",137 ????????????callBack: function (iframeId) {138 ????????????????top.frames[iframeId].submitForm();139 ????????????}140 ????????});141 ????}142 143 ????function btn_disabled() {144 ????????var keyValue = $("#gridList").jqGridRowValue().F_Id;145 ????????$.modalConfirm("注：您确定要【禁用】该项账户吗？", function (r) {146 ????????????if (r) {147 ????????????????$.submitForm({148 ????????????????????url: "/Admin/User/List?handler=DisabledAccount",149 ????????????????????param: { keyValue: keyValue },150 ????????????????????success: function () {151 ????????????????????????$.currentWindow().$("#gridList").trigger("reloadGrid");152 ????????????????????}153 ????????????????})154 ????????????}155 ????????});156 ????}157 158 ????function btn_enabled() {159 ????????var keyValue = $("#gridList").jqGridRowValue().F_Id;160 ????????$.modalConfirm("注：您确定要【启用】该项账户吗？", function (r) {161 ????????????if (r) {162 ????????????????$.submitForm({163 ????????????????????url: "/Admin/User/List?handler=EnabledAccount",164 ????????????????????param: { keyValue: keyValue },165 ????????????????????success: function () {166 ????????????????????????$.currentWindow().$("#gridList").trigger("reloadGrid");167 ????????????????????}168 ????????????????})169 ????????????}170 ????????});171 ????}172 </script>173 174 <div class="topPanel">175 ????<div class="toolbar">176 ????????<div class="btn-group">177 ????????????<a class="btn btn-primary" onclick="$.reload()"><span class="glyphicon glyphicon-refresh"></span></a>178 ????????</div>179 ????????<div class="btn-group">180 ????????????<a id="NF-add" authorize="yes" class="btn btn-primary dropdown-text" onclick="btn_add()"><i class="fa fa-plus"></i>新建用户</a>181 ????????</div>182 ????????<div class="operate">183 ????????????<ul class="nav nav-pills">184 ????????????????<li class="first">已选中<span>1</span>项</li>185 ????????????????<li><a id="NF-edit" authorize="yes" onclick="btn_edit()"><i class="fa fa-pencil-square-o"></i>修改用户</a></li>186 ????????????????<li><a id="NF-delete" authorize="yes" onclick="btn_delete()"><i class="fa fa-trash-o"></i>删除用户</a></li>187 ????????????????<li><a id="NF-Details" authorize="yes" onclick="btn_details()"><i class="fa fa-search-plus"></i>查看用户</a></li>188 ????????????????<li class="split"></li>189 ????????????????<li><a id="NF-revisepassword" authorize="yes" onclick="btn_revisepassword()"><i class="fa fa-key"></i>密码重置</a></li>190 ????????????????<li class="split"></li>191 ????????????????<li><a id="NF-disabled" authorize="yes" onclick="btn_disabled()"><i class="fa fa-stop-circle"></i>禁用</a></li>192 ????????????????<li><a id="NF-enabled" authorize="yes" onclick="btn_enabled()"><i class="fa fa-play-circle"></i>启用</a></li>193 ????????????</ul>194 ????????????<a href="javascript:;" class="close"></a>195 ????????</div>196 ????????<script>$(‘.toolbar‘).authorizeButton()</script>197 ????</div>198 ????<div class="search">199 ????????<table>200 ????????????<tr>201 ????????????????<td>202 ????????????????????<div class="input-group">203 ????????????????????????<input id="txt_keyword" type="text" class="form-control" placeholder="账户/姓名/手机" style="width: 200px;">204 ????????????????????????<span class="input-group-btn">205 ????????????????????????????<button id="btn_search" type="button" class="btn ?btn-primary"><i class="fa fa-search"></i></button>206 ????????????????????????</span>207 ????????????????????</div>208 ????????????????</td>209 ????????????</tr>210 ????????</table>211 ????</div>212 </div>213 <div class="gridPanel">214 ????<table id="gridList"></table>215 ????<div id="gridPager"></div>216 </div>

{ ?????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" ?} ??

$.fn.dataGrid = function (options) { ???var defaults = { ???????datatype: "json", ???????autowidth: 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);};

 ???/// <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; ???????????} ???????} ???}

 ???????/// <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; ???????????} ???????}

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