存储过程分页

 

//数据库存储过程

--存储过程
create PROCEDURE commonPagination
@columns varchar(500), --要显示的列名，用逗号隔开
@tableName varchar(100), --要查询的表名
@orderColumnName varchar(100), --排序的列名
@order varchar(50), --排序的方式，升序为asc,降序为 desc
@where varchar(100), --where 条件，如果不带查询条件，请用 1=1
@pageIndex int, --当前页索引
@pageSize int, --页大小(每页显示的记录条数)
@pageCount int out --总页数，输出参数
as
begin
declare @sqlRecordCount nvarchar(1000) --得到总记录条数的语句
declare @sqlSelect nvarchar(1000) --查询语句
set @sqlRecordCount=N'select @recordCount=count(*) from ' +@tableName + ' where '+ @where

declare @recordCount int --保存总记录条数的变量
exec sp_executesql @sqlRecordCount,N'@recordCount int output',@recordCount output

--动态 sql 传参
if( @recordCount % @pageSize = 0) --如果总记录条数可以被页大小整除
set @pageCount = @recordCount / @pageSize --总页数就等于总记录条数除以页大小
else --如果总记录条数不能被页大小整除
set @pageCount = @recordCount / @pageSize + 1 --总页数就等于总记录条数除以页大小加1

set @sqlSelect =
N'select '+@columns+' from (
select row_number() over (order by '
+@orderColumnName+' '+@order
+') as tempid,* from '
+@tableName+' where '+ @where
+') as tempTableName where tempid between '
+str((@pageIndex - 1)*@pageSize + 1 )
+' and '+str( @pageIndex * @pageSize)
exec (@sqlSelect) --执行动态Sql
end

//实体

public class PageList
{
/// <summary>
/// 表名
/// </summary>
public string TableName { get; set; }
/// <summary>
/// 需要返回的列
/// </summary>
public string Columns { get; set; }
/// <summary>
/// 每页几条
/// </summary>
public int PageSize { get; set; }
/// <summary>
/// 当前页
/// </summary>
public int PageIndex { get; set; }
/// <summary>
///查询条件
/// </summary>
public string Where { get; set; }
/// <summary>
/// 排序字段名 最好为唯一主键
/// </summary>
public string OrderColumnName { get; set; }
/// <summary>
/// 排序类型 1:降序 其它为升序
/// </summary>
public string Order { get; set; }
/// <summary>
/// 总页数
/// </summary>
public int PageCount { get; set; }
/// <summary>
/// 数据
/// </summary>
public object Data { get; set; }
}

//数据访问

public PageList PageSet(PageList pl)
{
using (Model1 db = new Model1())
{
var parm = new[]
{
new SqlParameter("@columns",pl.Columns),
new SqlParameter("@tableName", pl.TableName),
new SqlParameter("@orderColumnName", pl.OrderColumnName),
new SqlParameter("@order", pl.Order),
new SqlParameter("@where", pl.Where),
new SqlParameter("@pageIndex", pl.PageIndex),
new SqlParameter("@pageSize", pl.PageSize),
new SqlParameter("@pageCount", pl.PageCount),
};
parm[7].Direction = ParameterDirection.Output;
var Result = db.Database.SqlQuery<GetTwoTable>("exec commonPagination @columns,@tableName,@orderColumnName,@order,@where,@pageIndex,@pageSize,@pageCount output", parm).ToList();
pl.Data = Result;
pl.PageCount = Convert.ToInt32(parm[7].Value);
return pl;
}

}

//api

[HttpGet]
public PageList Query(int pageIndex=1)
{
var where = "1=1";
PageList pl = new PageList();
pl.Columns = "*";
pl.TableName = "GetTwoTable";
pl.OrderColumnName = "ID";
pl.Order = "asc";
pl.PageIndex = pageIndex;
pl.PageSize = 4;
pl.Where = where;
var Result = bll.PageSet(pl);
return Result;

}

//前台

var pageIndex= 1;
var pagecount=0;

function Show()
{
$.ajax({
url: "/api/WebApi/Query",
data: { "pageindex": pageIndex},
type:"get",
success:function(data){
pageindex = data.pageindex;
pageCount = data.pageCount;

var tr = "";
$("#Table1 tr").not("#tr1").remove();
for (var i = 0; i < data.Data.length; i++) {
tr= '<tr>'
+ '<td>' + data.Data[i].ID+ '</td>'
+ '<td>' + data.Data[i].StudentName + '</td>'
+ '<td>' + data.Data[i].StudentAge + '</td>'
+ '<td>' + data.Data[i].TypeName + '</td>'
+ '<td>' + data.Data[i].StudentHobby + '</td>'
+ '<td>' +(data.Data[i].TimeInto).substring(0,10)+ '</td>'
+ '<td>' + data.Data[i].StudentImg + '</td>'
+ '<td>' + data.Data[i].StudentRemark + '</td>'
+ '<td><input id="Button1" type="button" value="查看成绩" onclick="Redict(' + data.Data[i].ID + ')" /></td>'
+ '</tr>'
$("#Table1").append(tr);
}
}
})
}

<div style="clear:both">
<input type="button" value="首页" onclick="FirstPage()" />&nbsp;
<input type="button" value="上一页" onclick="PrivePage()" />&nbsp;
<input type="button" value="下一页" onclick="NextPage()" />&nbsp;
<input type="button" value="尾页" onclick="LastPage()" />&nbsp;
</div>