//数据库中新建存储过程:
1 USE [SAAS0626] 2 GO 3 /****** Object: StoredProcedure [dbo].[Paging] Script Date: 2017/8/7 11:03:32 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 ALTER PROCEDURE [dbo].[Paging] 9 ( @TableName VARCHAR(1000), --表名,多表时使用 tA a inner join tB b On a.AID = b.AID 10 @Fields NVARCHAR(2000) = '*', --读取字段 11 @Condition NVARCHAR(3000) = '', --Where条件 12 @Sort NVARCHAR(200) = '', --排序字段,不能为空 13 @CurrentPage INT = 1, --开始页码 14 @PageSize INT = 10, --页大小 15 @GroupBy varchar(200), --分组语句 16 @RecordCount INT = 0 OUT 17 ) 18 AS 19 20 DECLARE @strWhere VARCHAR(2000) 21 DECLARE @strGroup VARCHAR(200) 22 DECLARE @strsql NVARCHAR(3900) 23 24 IF @Condition IS NOT NULL AND len(ltrim(rtrim(@Condition)))>0 25 BEGIN 26 SET @strWhere = ' WHERE ' + @Condition + ' ' 27 END 28 ELSE 29 BEGIN 30 SET @strWhere = '' 31 END 32 33 IF @GroupBy IS NOT NULL AND len(ltrim(rtrim(@GroupBy)))>0 34 BEGIN 35 SET @strsql = 'SELECT @RecordCount = Count(1) FROM (SELECT 1 AS total FROM ' + @TableName + @strWhere + ' group by ' + @GroupBy + ') as t' 36 SET @strGroup = ' GROUP BY ' + @GroupBy + ' ' 37 END 38 ELSE 39 BEGIN 40 SET @strsql = 'SELECT @RecordCount = Count(1) FROM ' + @TableName + @strWhere 41 SET @strGroup = '' 42 END 43 44 45 EXECUTE sp_executesql @strsql, N'@RecordCount INT output', @RecordCount OUTPUT 46 47 IF @CurrentPage = 1 --第一页提高性能 48 BEGIN 49 SET @strsql = 'SELECT TOP ' + str(@PageSize) + ' ' + @Fields + ' FROM ' + @TableName + ' ' + @strWhere + @strGroup + ' ORDER BY '+ @Sort 50 END 51 ELSE 52 BEGIN 53 DECLARE @startRecord NVARCHAR(50) 54 DECLARE @endRecord NVARCHAR(50) 55 SET @startRecord = CONVERT(NVARCHAR(50),(@CurrentPage - 1) * @PageSize + 1) 56 SET @endRecord = CONVERT(NVARCHAR(50),@CurrentPage * @PageSize) 57 SET @strsql = 'SELECT * FROM ( 58 SELECT '+ @Fields + ',ROW_NUMBER() OVER(ORDER BY ' + @Sort + ') AS rownum FROM '+ @TableName + @strWhere + @strGroup + ') AS XX 59 WHERE rownum BETWEEN '+ @startRecord + ' AND ' + @endRecord 60 END 61 EXEC(@strsql) 62 RETURN
建立Model:
public class PageData<T> { public PageData() { this._Items = new List<T>(); } private IList<T> _Items; public IList<T> Items { get { return _Items; } set { _Items = value; } } public int RecordCount { get; set; } public int CurrentPage { get; set; } public int PageCount { get; set; } } public class PageParams { /// <summary> /// 表名(多表使用tA a inner join tB b On a.AID = b.AID) /// </summary> public string TableName { get; set; } private string _Fileds = "*"; /// <summary> /// 查询字段 /// </summary> public string Fields { get { return _Fileds; } set { _Fileds = value; } } private string _Sort = "ID"; /// <summary> /// 排序字段(不能为空) /// </summary> public string Sort { get { return _Sort; } set { _Sort = value; } } /// <summary> /// 查询条件 /// </summary> public string Condition { get; set; } private int _CurrentPage = 1; /// <summary> /// 当前页数(从1开始) /// </summary> public int CurrentPage { get { return _CurrentPage; } set { _CurrentPage = value; } } private int _PageSize = 10; /// <summary> /// 每页条数 /// </summary> public int PageSize { get { return _PageSize; } set { _PageSize = value; } } public string GroupBy { get; set; } /// <summary> /// 总记录数 /// </summary> public int RecordCount { get; set; } }
Js代码(jquery.pagination.min.js):
1 (function(b){var a={init:function(d,c){return(function(){a.fillHtml(d,c);if(d[0]&&!b._data(d[0],"events")){a.bindEvent(d,c)}})()},fillHtml:function(d,c){return(function(){var l=c.current,h=c.pageCount,f=[],m,g,e=10;if(isNaN(h)){d.html("");return}if(l<=1){m=1;g=e;if(g>h){g=h}}else{if(l>=h){m=h-e+1;g=h;if(m<=0){m=1}}else{var k=Math.ceil(e/2);m=l-k;if(m<=0){m=1}g=m+e-1;if(g>h){g=h}if(m>h){g=h}}}if(l>1){f.push('<li><a href="javascript:;" class="first">首页</a></li>');f.push('<li><a href="javascript:;" class="prev">上一页</a></li>')}else{f.push('<li class="disabled"><span>首页</span></li>');f.push('<li class="disabled"><span>上一页</span></li>')}for(var j=m;j<=g;j++){if(j==l){f.push('<li class="disabled"><span class="current">'+j+"</span></li>")}else{f.push('<li><a href="javascript:;" class="number">'+j+"</a></li>")}}if(l<h){f.push('<li><a href="javascript:;" class="next">下一页</a></li>');f.push('<li><a href="javascript:;" class="last">末页</a></li>')}else{f.push('<li class="disabled"><span>下一页</span></li>');f.push('<li class="disabled"><span>末页</span></li>')}d.html(f.join(""))})()},bindEvent:function(d,c){return(function(){d.on("click","a",function(){var e;switch(this.className){case"first":e=1;break;case"prev":var f=parseInt(d.find("span.current").text(),10);e=f-1;break;case"number":e=parseInt(this.innerText);break;case"next":var f=parseInt(d.find("span.current").text(),10);e=f+1;break;case"last":e=c.pageCount;break}typeof c.callback=="function"&&c.callback(e)})})()}};b.fn.Paging=function(d){var e={current:1,pageCount:1,pageSize:10,callback:function(){}};var c=b.extend({},e,d);a.init(this,c)}})(jQuery);
View视图中添加分页样式:
1 <div class="text-left"> 2 <div class="pagesize"> 3 每页 4 <select id="positions-pagesize"> 5 <option value="10">10</option> 6 <option value="30">30</option> 7 <option value="50">50</option> 8 </select> 9 条 共 <span id="pCount"></span> 页 共 <span id="rCount"></span> 条记录 10 </div> 11 <ul class="pagination" id="positionPager"> 12 <li class="disabled"><span>上一页</span></li> 13 <li class="disabled"><span class="current">1</span></li> 14 <li class="disabled"><span>下一页</span></li> 15 </ul> 16 </div>
需要引用JS<script src="~/JS/Common/jquery.pagination.min.js"></script>
分布视图中需要添加隐藏button存储参数:
@using CommonUtility
@model PageData<Model>
1 <input id="pageCount" type="hidden" value="@(Model != null ? Model.PageCount : 0)" /> 2 <input id="reCount" type="hidden" value="@(Model != null? Model.RecordCount : 0)" />
Js代码:
1 //页码显示条数触发事件 2 $("#positions-pagesize").on("change", function () { LoadOrderReply(1, this.value); }) 3 4 //首页刷新 5 LoadOrderReply(); 6 function LoadOrderReply(page, size) { 7 size = $("#positions-pagesize").val(); 8 $("#orderTb").load("/OrderReply/OrderReplyPartial", { "page": page || 1, "size": size || 10 }, function () { 9 //显示第几页,共几条记录 10 if ($("#pageCount").val() != 0) { 11 $("#pCount").text($("#pageCount").val()); 12 $("#rCount").text($("#reCount").val()); 13 } 14 else { 15 $("#pCount").text(0); 16 $("#rCount").text(0); 17 } 18 19 //触发鼠标停留显示 20 $("[data-toggle='popover']").popover({ html: true }); 21 $("[data-toggle='popover']").popover(); 22 $("#positionPager").Paging({ 23 current: page, 24 pageCount: parseInt($("#pageCount").val(), 10), 25 callback: function (p) { LoadOrderReply(p, size); } 26 }); 27 }); 28 }
控制器Controller:
1 public PartialViewResult CContractMgrPartial() 2 { 3 int currentPage = 0; 4 int.TryParse(Request["page"] + "", out currentPage); 5 if (currentPage < 1) currentPage = 1; 6 7 int pageSize = 0; 8 int.TryParse(Request["size"] + "", out pageSize); 9 if (pageSize < 1) pageSize = UtilityHelp.PageSize; 10 11 PageParams p = new PageParams 12 { 13 TableName = "TableName", //数据库表名 14 Fields = @"*", //需要查询的字段 15 Condition = "", //WHERE条件 16 Sort = "ID", //升序字段 17 CurrentPage = currentPage, 18 PageSize = pageSize 19 }; 20 PageData<ExCContractMgr> list = PageDataBLL.GetPageData<ExCContractMgr>(p);; //<ExCContractMgr>里为Model 21 return PartialView(list); 22 }
BLL
1 using CommonDAL; 2 using CommonUtility; 3 4 namespace CommonBLL 5 { 6 public class PageDataBLL 7 { 8 public static PageData<T> GetPageData<T>(PageParams pageParams) 9 { 10 return PageDataDAL.GetPageData<T>(pageParams); 11 } 12 /// <summary> 13 /// 返回DataTable 14 /// </summary> 15 /// <typeparam name="T"></typeparam> 16 /// <param name="pageParams"></param> 17 /// <returns></returns> 18 public static PageData<T> GetPageDataTable<T>(PageParams pageParams) 19 { 20 return PageDataDAL.GetPageDataTable<T>(pageParams); 21 } 22 } 23 }
DAL
1 using CommonUtility; 2 using Dapper; 3 using System; 4 using System.Collections.Generic; 5 using System.Data; 6 using System.Data.SqlClient; 7 using System.Linq; 8 using System.Reflection; 9 using System.Text; 10 using System.Threading.Tasks; 11 12 namespace CommonDAL 13 { 14 public class PageDataDAL 15 { 16 public static IDbConnection GetConn() 17 { 18 return new SqlConnection(ConfigureHelp.Connection); 19 } 20 21 public static PageData<T> GetPageData<T>(PageParams pageParams) 22 { 23 if (pageParams == null) 24 { 25 return null; 26 } 27 28 var p = new DynamicParameters(); 29 p.Add("TableName", pageParams.TableName); 30 p.Add("Fields", pageParams.Fields); 31 p.Add("Condition", pageParams.Condition); 32 p.Add("Sort", pageParams.Sort); 33 p.Add("CurrentPage", pageParams.CurrentPage); 34 p.Add("PageSize", pageParams.PageSize); 35 p.Add("GroupBy", pageParams.GroupBy); 36 p.Add("RecordCount", dbType: DbType.Int32, direction: ParameterDirection.Output); 37 38 var conn = GetConn(); 39 try 40 { 41 conn.Open(); 42 43 var pageData = new PageData<T>(); 44 pageData.Items = conn.Query<T>("Paging", p, commandType: CommandType.StoredProcedure).ToList(); 45 pageData.RecordCount = p.Get<int>("RecordCount"); 46 pageData.PageCount = Convert.ToInt32(Math.Ceiling(pageData.RecordCount * 1.0 / pageParams.PageSize)); 47 pageData.CurrentPage = pageParams.CurrentPage > pageData.PageCount ? pageData.PageCount : pageParams.CurrentPage; 48 49 return pageData; 50 } 51 catch (Exception ex) 52 { 53 UtilityHelp.WriteLog(ex, ex.Message); 54 return null; 55 } 56 finally 57 { 58 conn.Close(); 59 } 60 61 } 62 63 64 /// <summary> 65 /// 根据存储过程获取Table 66 /// </summary> 67 /// <typeparam name="T"></typeparam> 68 /// <param name="pageParams"></param> 69 /// <returns></returns> 70 public static PageData<T> GetPageDataTable<T>(PageParams pageParams) 71 { 72 if (pageParams == null) 73 { 74 return null; 75 } 76 77 var p = new DynamicParameters(); 78 p.Add("TableName", pageParams.TableName); 79 p.Add("Fields", pageParams.Fields); 80 p.Add("Condition", pageParams.Condition); 81 p.Add("Sort", pageParams.Sort); 82 p.Add("CurrentPage", pageParams.CurrentPage); 83 p.Add("PageSize", pageParams.PageSize); 84 p.Add("GroupBy", pageParams.GroupBy); 85 p.Add("RecordCount", dbType: DbType.Int32, direction: ParameterDirection.Output); 86 87 var conn = GetConn(); 88 try 89 { 90 conn.Open(); 91 92 var pageData = new PageData<T>(); 93 DataTable dt = new DataTable(); 94 CommandDefinition cd = new CommandDefinition("Paging", p, commandType: CommandType.StoredProcedure); 95 dt.Load(conn.ExecuteReader(cd)); 96 pageData.dtItems = dt; 97 pageData.RecordCount = p.Get<int>("RecordCount"); 98 pageData.PageCount = Convert.ToInt32(Math.Ceiling(pageData.RecordCount * 1.0 / pageParams.PageSize)); 99 pageData.CurrentPage = pageParams.CurrentPage > pageData.PageCount ? pageData.PageCount : pageParams.CurrentPage; 100 101 return pageData; 102 } 103 catch (Exception ex) 104 { 105 UtilityHelp.WriteLog(ex, ex.Message); 106 return null; 107 } 108 finally 109 { 110 conn.Close(); 111 } 112 113 } 114 } 115 }
PageSize()
1 public static int PageSize { get { return Convert.ToInt32(ConfigurationManager.AppSettings["pagesize"].ToString()); } }