高效分页存储过程

 该存储过程直接复制执行即可

 1
 4 CREATE PROCEDURE SP_Page 
 5 @tblName VARCHAR(1255), -- 表名 
 6 @fldName VARCHAR(1255), -- 主键字段名 
 7 @PageSize INT = 10, -- 页尺寸 
 8 @PageIndex INT = 1, -- 页码 
 9 @IsReCount BIT = 0, -- 返回记录总数, 非 0 值则返回 
10 @OrderType BIT = 0, -- 设置排序类型, 非 0 值则降序 
11 @strWhere VARCHAR(MAX) = '', -- 查询条件 (注意: 不要加 where) 
12 @Recount BIGINT OUTPUT, -- 返回记录总数 
13 @PageCount BIGINT OUTPUT -- 返回总页数 
14 AS 
15 
16 DECLARE @strSQL VARCHAR(MAX) -- 主语句 
17 DECLARE @strTotal VARCHAR(2000) -- 获取记录数合计语句 
18 DECLARE @strTmp VARCHAR(MAX) -- 临时变量 
19 DECLARE @strOrder VARCHAR(400) -- 排序类型 
20 DECLARE @Sql1 NVARCHAR(MAX) -- 计算记录总数SQL 
21 
22 IF @OrderType != 0 
23 BEGIN 
24 SET @strTmp = '<(select min' 
25 SET @strOrder = ' order by [' + @fldName +'] desc' 
26 END 
27 ELSE 
28 BEGIN 
29 SET @strTmp = '>(select max' 
30 SET @strOrder = ' order by [' + @fldName +'] asc' 
31 END 
32 
33 SET @strSQL = 'select top ' + STR(@PageSize) + ' * from [' 
34 + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' 
35 + @fldName + ']) from (select top ' + STR((@PageIndex-1)*@PageSize) + ' [' 
36 + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)' 
37 + @strOrder 
38 
39 IF @strWhere != '' 
40 SET @strSQL = 'select top ' + STR(@PageSize) + ' * from [' 
41 + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' 
42 + @fldName + ']) from (select top ' + STR((@PageIndex-1)*@PageSize) + ' [' 
43 + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder 
44 --当为第一页时 
45 IF @PageIndex = 1 
46 BEGIN 
47 SET @strTmp ='' 
48 IF @strWhere != '' 
49 SET @strTmp = ' where ' + @strWhere 
50 
51 SET @strSQL = 'select top ' + STR(@PageSize) + ' * from [' 
52 + @tblName + ']' + @strTmp + ' ' + @strOrder 
53 END 
54 
55 IF @strWhere != '' 
56 BEGIN 
57 SET @strTotal = 'select distinct * from [' + @tblName + ']'+' where ' + @strWhere 
58 END 
59 ELSE 
60 BEGIN 
61 SET @strTotal = 'select distinct * from [' + @tblName + ']' 
62 END 
63 EXEC (@strSQL) 
64 IF @IsReCount != 0 
65 BEGIN 
66 SELECT @Sql1 = 'Select @Count = count(ta.' + @fldName + ') From (' + @strTotal + ') ta ' 
67 EXEC sp_executesql @Sql1, N'@Count int output', @Recount OUTPUT 
68 
69 IF ((@Recount % @PageSize) != 0) --如果除不尽则加一页 
70 BEGIN 
71 SET @PageCount = @Recount / @PageSize 
72 SET @PageCount = @PageCount + 1 
73 END 
74 ELSE 
75 BEGIN 
76 SET @PageCount = @Recount / @PageSize 
77 END 
78 END 
79 GO
80 
81

Controllers调用:

[HttpPost]
public JsonResult GetList(string strWhere, int pageIndex = 1, int pageSize = 10, int type = 0)
{
int total;
int pageTotal;



 string[] whereList = strWhere.Split(',');
            StringBuilder sWhere = new StringBuilder();
            sWhere.Append(" 1=1 ");
            if (whereList[0] != "")
            {
                sWhere.Append(" and UserCode like '%" + whereList[0] + "%'");
            }
            if (whereList[1] != "")
            {
                sWhere.Append(" and UserName like '%" + whereList[1] + "%'");
            }
            if (type == 0)
            {
                if (whereList[2] != "")
                {
                    sWhere.Append(" and DeptName like '%" + whereList[2] + "%'");
                }
            }
            else
            {
                    sWhere.Append(" and (DeptID =" + whereList[2] + " or DeptID in(select id from GetChildIDs(" + whereList[2] + ")))");
            }
            if (whereList[3] != "-1")
            {
                sWhere.Append(" and UserState=" + whereList[3]);
            }




 IList<User> List = Dal层.GetList(pageIndex, pageSize, sWhere.ToString(), out total, out pageTotal);
            string pageBar = 工具类.newShowPageNavNew(pageIndex, total, pageSize);
            return Json(new { result = true, data = List, pageBar = pageBar });
        }


 




DAL层方法:




/// <summary>
/// 分页查询信息信息列表
/// </summary>
/// <returns></returns>
public static IList<Sys_User> GetList(int pageIndex, int pageSize, string where, out int total, out int pageTotal)
{
string tableName = "V_Sys_User";   //表明
string primaryKey = "ID";   //主键
DataTable dt =GetPage(tableName, primaryKey, pageIndex, pageSize, 1, 1, where, out total, out pageTotal);   //下面工具类里面的方法

return GetListByTable(dt);  //将DataTable转换成list集合。在这也可以用循环遍历DataTable得到list。
}




工具类:




 /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="primaryKey">主键字段名</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页尺寸</param>
        /// <param name="isReCount">返回记录总数, 非 0 值则返回</param>
        /// <param name="orderType">设置排序类型, 非 0 值则降序</param>
        /// <param name="where">查询条件 (注意: 不要加 where)</param>
        /// <param name="recount">返回记录总数</param>
        /// <param name="pageCount">返回总页数</param>
        /// <returns></returns>
        public static DataTable GetPage(string tableName, string primaryKey, int pageIndex, int pageSize, int isReCount, int orderType, string where, out int recount, out int pageCount)
        {
            string procName = "SP_Page";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@Recount",SqlDbType.Int),
                new SqlParameter("@PageCount",SqlDbType.Int),
                new SqlParameter("@tblName",tableName),
                new SqlParameter("@fldName",primaryKey),
                new SqlParameter("@PageSize",pageSize),
                new SqlParameter("@PageIndex",pageIndex),
                new SqlParameter("@IsReCount",isReCount),
                new SqlParameter("@OrderType",orderType),
                new SqlParameter("@strWhere",where)
            };

            paras[0].Direction = ParameterDirection.Output;
            paras[1].Direction = ParameterDirection.Output;

            DataTable dt = SqlHelper.FillDataTable(procName, paras);

            recount = int.Parse(paras[0].Value.ToString());
            pageCount = int.Parse(paras[1].Value.ToString());

            return dt;
        }







 public static string newShowPageNavNew(int pageCurrent, int totalCount, int pageSize = 5, string methodName = "getlist")
        {
            var totalPage = Math.Max((totalCount + pageSize - 1) / pageSize, 1);
            //if (totalPage >= 1)
            //{
                //要输出的超链接字符串
                var pageNav = new StringBuilder();
                //左边代码
            //无数据
            if(totalCount==0)
            {
                pageNav.AppendFormat("<div class='col-sm-4'><div class='dataTables_info' id='editable_info' role='alert' aria-live='polite' aria-relevant='all'>显示 0 项，共 0 项</div></div>");
            }
            else
            {
                //最后一页文件数量
                if (pageCurrent == totalPage)
                {
                    pageNav.AppendFormat("<div class='col-sm-4'><div class='dataTables_info' id='editable_info' role='alert' aria-live='polite' aria-relevant='all'>显示 " + ((pageCurrent - 1) * pageSize + 1) + " 到 " + totalCount + " 项，共 " + totalCount + " 项</div></div>");
                }
                else
                {
                    pageNav.AppendFormat("<div class='col-sm-4'><div class='dataTables_info' id='editable_info' role='alert' aria-live='polite' aria-relevant='all'>显示 " + ((pageCurrent - 1) * pageSize + 1) + " 到 " + (pageCurrent * pageSize) + " 项，共 " + totalCount + " 项</div></div>");
                }
            }
              
                //右边代码
                pageNav.AppendFormat("<div class='col-sm-8'><div class='dataTables_paginate paging_simple_numbers' id='editable_paginate'><ul class='pagination'>");




 //如果当前是第一页，则“上一页”不可点
                if (pageCurrent == 1)
                {
                    pageNav.AppendFormat("<li class='paginate_button previous disabled' aria-controls='editable' tabindex='0' id='editable_previous'><a href='javascript:void(0)'>上一页</a></li>");
                }
                else
                {
                    pageNav.AppendFormat("<li class='paginate_button previous' aria-controls='editable' tabindex='0' id='editable_previous'><a href='javascript:void(0)' onclick='" + methodName + "({0},{1})'>{2}</a></li>", pageCurrent - 1, pageSize, "上一页");
                }




 //中间页码 
                if (pageCurrent <= 3)
                {
                    for (int i = 1; i < 8; i++)
                    {
                        if (i <= totalPage)
                        {
                            if (pageCurrent == i) //当前页处理
                            {
                                pageNav.AppendFormat("<li class='paginate_button active' aria-controls='editable' tabindex='0'><a href='javascript:void(0)'>{0}</a></li>", pageCurrent);
                            }
                            else //一般页处理
                            {
                                pageNav.AppendFormat("<li class='paginate_button previous' aria-controls='editable' tabindex='0' id='editable_previous'><a href='javascript:void(0)' onclick='" + methodName + "({0},{1})'>{2}</a></li>", i, pageSize, i);
                            }
                        }
                    }
                }
                else if (pageCurrent > 3 && pageCurrent < totalPage - 3)
                {
                    int current = 4;
                    for (int i = 1; i < 8; i++)
                    {
                        if ((pageCurrent + i - current) >= 1 && (pageCurrent + i - current) <= totalPage)
                        {
                            if (current == i) //当前页处理
                            {
                                pageNav.AppendFormat("<li class='paginate_button active' aria-controls='editable' tabindex='0'><a href='javascript:void(0)'>{0}</a></li>", pageCurrent);
                            }
                            else //一般页处理
                            {
                                pageNav.AppendFormat("<li class='paginate_button previous' aria-controls='editable' tabindex='0' id='editable_previous'><a href='javascript:void(0)' onclick='" + methodName + "({0},{1})'>{2}</a></li>", pageCurrent + i - current, pageSize, pageCurrent + i - current);
                            }
                        }
                    }




 }
                else
                {
                    for (int i = totalPage - 6; i <= totalPage; i++)
                    {
                        if (i <= totalPage && i>0)
                        {
                            if (pageCurrent == i) //当前页处理
                            {
                                pageNav.AppendFormat("<li class='paginate_button active' aria-controls='editable' tabindex='0'><a href='javascript:void(0)'>{0}</a></li>", pageCurrent);
                            }
                            else //一般页处理
                            {
                                pageNav.AppendFormat("<li class='paginate_button previous' aria-controls='editable' tabindex='0' id='editable_previous'><a href='javascript:void(0)' onclick='" + methodName + "({0},{1})'>{2}</a></li>", i, pageSize, i);
                            }
                        }
                    }
                }




 //如果当前是最后一页，则“下一页”不可点
                if (pageCurrent == totalPage)
                {
                    pageNav.AppendFormat("<li class='paginate_button next disabled' aria-controls='editable' tabindex='0' id='editable_next'><a href='javascript:void(0)'>下一页</a></li>");
                }
                else
                {
                    pageNav.AppendFormat("<li class='paginate_button next' aria-controls='editable' tabindex='0' id='editable_next'><a href='javascript:void(0)' onclick='" + methodName + "({0},{1})'>{2}</a></li>", pageCurrent + 1, pageSize, "下一页");
                }
                pageNav.AppendFormat("</ul></div></div>");
                return pageNav.ToString();
            //}
            //else
            //{
            //    return string.Empty;
            //}
        }


 




前台:




 <table class="table table-bordered table-hover tablesorter" id="tableinfo">
                            <thead>
                                <tr>
                                    <th>编号</th>
                                    <th>姓名</th>
                                    <th>性别</th>
                                    <th>部门</th>
                                    <th>状态</th>
                                </tr>
                            </thead>
                            <tbody id="tableBody"></tbody>
                        </table>
                        <div class="row" id="pageBar"></div>


 



 //人员信息
        function getlist(pageindex, pagesize) {
            var where =$.trim($("#UserName").val()) + ',' + $.trim($("#dept_SupID").val()) + ',' + $.trim($("#UserState").val());  //拼接where条件
            $.ajax({
                type: "post",
                url: "@Url.Action("GetList", "User")",
                data: { pageIndex: pageindex, pageSize: pagesize, strWhere: where, type: 1 },
                datatype: "json",
                success: function (data) {
                    if (!data.result) {
                        layer.alert('服务器异常！', {
                            skin: 'layui-layer-molv', //样式类名
                            icon: 2
                        });
                        return;
                    }
                    var str = "";
                    if (data.data.length <= 0) {
                        layer.msg("暂无数据", { icon: 2, time: 1000 });
                    }
                    for (var i = 0; i < data.data.length; i++) {
                        str += "<tr>" +
                            "<td>" + data.data[i].UserCode + "</td>" +
                            "<td>" + data.data[i].UserName + "</td>" +
                            "<td>" + (data.data[i].Sex == "0" ? "男" : "女") + "</td>" +
                            "<td>" + data.data[i].DeptName + "</td>" +
                            "<td>" + (data.data[i].UserState == "1" ? "<lable class='label label-danger'>禁用</lable>" : " <lable class='label label-success'>正常</lable>") + "</td>" +
                            "</tr>"
                    }
                    $("#tableBody").html(str);
                    $("#pageBar").html(data.pageBar);
                    return;
                }
            });
        }







        

	      

	    

	  

	  

	    
      
        【推广】
        免费学中医，健康全家人
      
	  

    

    

          原文地址：https://www.cnblogs.com/bin521/p/8392600.html