高效分页存储过程

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

CREATE PROCEDURE SP_Page 
@tblName VARCHAR(1255), -- 表名 
@fldName VARCHAR(1255), -- 主键字段名 
@PageSize INT = 10, -- 页尺寸 
@PageIndex INT = 1, -- 页码 
@IsReCount BIT = 0, -- 返回记录总数, 非 0 值则返回 
@OrderType BIT = 0, -- 设置排序类型, 非 0 值则降序 
@strWhere VARCHAR(MAX) = '', -- 查询条件 (注意: 不要加 where) 
@Recount BIGINT OUTPUT, -- 返回记录总数 
@PageCount BIGINT OUTPUT -- 返回总页数 
AS 

DECLARE @strSQL VARCHAR(MAX) -- 主语句 
DECLARE @strTotal VARCHAR(2000) -- 获取记录数合计语句 
DECLARE @strTmp VARCHAR(MAX) -- 临时变量 
DECLARE @strOrder VARCHAR(400) -- 排序类型 
DECLARE @Sql1 NVARCHAR(MAX) -- 计算记录总数SQL 

IF @OrderType != 0 
BEGIN 
SET @strTmp = '<(select min' 
SET @strOrder = ' order by [' + @fldName +'] desc' 
END 
ELSE 
BEGIN 
SET @strTmp = '>(select max' 
SET @strOrder = ' order by [' + @fldName +'] asc' 
END 

SET @strSQL = 'select top ' + STR(@PageSize) + ' * from [' 
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '([' 
+ @fldName + ']) from (select top ' + STR((@PageIndex-1)*@PageSize) + ' [' 
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)' 
+ @strOrder 

IF @strWhere != '' 
SET @strSQL = 'select top ' + STR(@PageSize) + ' * from [' 
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '([' 
+ @fldName + ']) from (select top ' + STR((@PageIndex-1)*@PageSize) + ' [' 
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder 
--当为第一页时 
IF @PageIndex = 1 
BEGIN 
SET @strTmp ='' 
IF @strWhere != '' 
SET @strTmp = ' where ' + @strWhere 

SET @strSQL = 'select top ' + STR(@PageSize) + ' * from [' 
+ @tblName + ']' + @strTmp + ' ' + @strOrder 
END 

IF @strWhere != '' 
BEGIN 
SET @strTotal = 'select distinct * from [' + @tblName + ']'+' where ' + @strWhere 
END 
ELSE 
BEGIN 
SET @strTotal = 'select distinct * from [' + @tblName + ']' 
END 
EXEC (@strSQL) 
IF @IsReCount != 0 
BEGIN 
SELECT @Sql1 = 'Select @Count = count(ta.' + @fldName + ') From (' + @strTotal + ') ta ' 
EXEC sp_executesql @Sql1, N'@Count int output', @Recount OUTPUT 

IF ((@Recount % @PageSize) != 0) --如果除不尽则加一页 
BEGIN 
SET @PageCount = @Recount / @PageSize 
SET @PageCount = @PageCount + 1 
END 
ELSE 
BEGIN 
SET @PageCount = @Recount / @PageSize 
END 
END 
GO

 

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;
}
});
}