分布视图分页

//数据库中新建存储过程： 
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                 条 &nbsp;&nbsp;&nbsp;&nbsp;共&nbsp;&nbsp; <span id="pCount"></span> &nbsp;&nbsp;页 &nbsp;&nbsp; &nbsp;&nbsp; 共 &nbsp;&nbsp;<span id="rCount"></span> &nbsp;&nbsp;条记录&nbsp;&nbsp;&nbsp;&nbsp;
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()); } }