本文介绍了一种基于SQL Server的高效分页方案，包括存储过程的编写与调用方法，并展示了具体的实现步骤与代码示例。
主要的技术点不在这里一一阐述，相关存储也是引用别人的，主要技术点就是通过最优性能方式处理需求，PagedList.包需要在线安装就可以
直接上干货
1、存储代码之第一种： 参数相对多点
/**//*
@strTable --要显示的表或多个表的连接
@strField --要查询出的字段列表,*表示全部字段
@pageSize --每页显示的记录个数
@pageIndex --要显示那一页的记录
@strWhere --查询条件,不需where
@strSortKey --用于排序的主键
@strSortField --用于排序，如：id desc (多个id desc,dt asc)
@strOrderBy --排序,0-顺序,1-倒序
@UsedTime --耗时测试时间差
@RecordCount --总记录数
@pageCount --总页数
*/
ALTER PROCEDURE [dbo].[fyPager]
@strTable varchar(1000) = '[dbo].[ttable]',
@strField varchar(1000) = '*',
@pageSize int = 10,
@pageIndex int = 1,
@strWhere varchar(1000) = '1=1',
@strSortKey varchar(1000) = 'id',
@strSortField varchar(500) = 'id DESC',
@strOrderBy bit = 1,
@RecordCount int output, --总记录数
@pageCount int output
--@UsedTime int OUTPUT
AS
SET NOCOUNT ON
Declare @sqlcount INT
Declare @timediff DATETIME
select @timediff=getdate()
Begin Tran
DECLARE @sql nvarchar(1000),@where1 varchar(200),@where2 varchar(200)
IF @strWhere is null or rtrim(@strWhere)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@strWhere+') AND ' --本来有条件再加上此条件
SET @where2=' WHERE ('+@strWhere+') ' --原本没有条件而加上此条件
END
--SET @sql='SELECT @intResult=COUNT(*) FROM '+@strTable+@where2
BEGIN
SET @sql='SELECT @sqlcount=COUNT(*) FROM (select '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab'
END
--print @sql
EXEC sp_executesql @sql,N'@sqlcount int OUTPUT',@sqlcount OUTPUT --计算总记录数
SELECT @RecordCount = @sqlcount --设置总记录数
IF @pageIndex=1 --第一页
BEGIN
SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(200))+' '+@strField+' FROM '+@strTable+@where2+'ORDER BY '+ @strSortField
END
Else
BEGIN
IF @strOrderBy=0
SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(200))+' '+@strField+ ' FROM '+
@strTable+@where1+@strSortKey+'>(SELECT MAX('+case when charindex('.',@strSortKey)>0 then right(@strSortKey,len(@strSortKey)-charindex('.',@strSortKey)) else @strSortKey end+') '+ ' FROM (SELECT TOP '+
CAST(@pageSize*(@pageIndex-1) AS varchar(200))+' '+@strSortKey+' FROM '+@strTable+@where2+
'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField
ELSE
SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(200))+' '+@strField+' FROM '+@strTable+@where1+
@strSortKey+'<(SELECT MIN('+case when charindex('.',@strSortKey)>0 then right(@strSortKey,len(@strSortKey)-charindex('.',@strSortKey)) else @strSortKey end+') '+ ' FROM (SELECT TOP '+CAST(@pageSize*(@pageIndex-1) AS varchar(200))+' '+
@strSortKey+' FROM '+@strTable+@where2+'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField+''
END
print @sql
EXEC(@sql)
print @sql
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit TRAN
--set @UsedTime = datediff(ms,@timediff,getdate())
--select datediff(ms,@timediff,getdate()) as 耗时
Return @sqlcount
End
执行测试：
USE [test]
GO
DECLARE @return_value int,
@RecordCount int,
@pageCount int
EXEC @return_value = [dbo].[fyPager]
@strTable = N'TRA_BargainOrder_Test A join UserInfo_test B ON A.UserID = B.Id',
@strField = N'*',
@pageSize = 10,
@pageIndex = 9,
@strWhere = N'',
@strSortKey = N'TRA_BargainOrder_TestID',
@strSortField = N'TRA_BargainOrder_TestID desc',
@strOrderBy = 1,
@RecordCount = @RecordCount OUTPUT,
@pageCount = @pageCount OUTPUT
SELECT @RecordCount as N'@RecordCount',
@pageCount as N'@pageCount'
SELECT 'Return Value' = @return_value
GO
2、VIEW 页面层 GxFy_PageList2.cshtml
<link href="~/Content/bootstrap.css" rel="stylesheet" /> <link href="~/Content/PagedList.css" rel="stylesheet" /> @model PagedList.StaticPagedList<Models.TRA_BargainOrder_Test> @using PagedList.Mvc @using PagedList @using (Html.BeginForm("Index", "Home", FormMethod.Get)) { <div class="well"> <table class="table"> @* <thead> <tr> <th> <input id="UserName" name="UserName" type="text" placeholder="请输入用户名" /> </th> <th> <input id="OrderNum" name="OrderNum" type="text" placeholder="请输入订单号" /> </th> <th> <input id="Submit1" type="submit" value="submit" /> </th> </tr> </thead>*@ <tr> <th>用户名</th> <th>地址</th> <th>订单编号</th> <th>城市代号</th> <th>时间</th> <th>订单状态</th> </tr> <tbody> @* @foreach (var item in ViewBag.List) *@ @*用model展示数据*@ @foreach (var item in Model) { <tr> <td>@item.UserName </td> <td>@item.LocalAddress </td> <td>@item.BargainOrderCode </td> <td>@item.CityCode </td> <td>@item.UpdateTime </td> <td>@item.OrderStatus </td> </tr> } </tbody> <tfoot> <tr> <td colspan="5"> <div class=""> @if (Model != null) { <span style="height: 20px; line-height: 20px;">共 @Model.TotalItemCount.ToString() 条记录，当前第 @Model.PageNumber 页/共 @Model.PageCount 页 </span> @Html.PagedListPager(Model, pageindex => Url.Action("GxFy_PageList2", new { pageindex }), new PagedListRenderOptions() { LinkToFirstPageFormat = "首页", LinkToNextPageFormat = "下一页", LinkToPreviousPageFormat = "上一页", LinkToLastPageFormat = "末页", DisplayItemSliceAndTotal = false, MaximumPageNumbersToDisplay = 3 }) } </div> </td> </tr> </tfoot> </table> </div> }
3、Controller 控制器代码：
public ActionResult GxFy_PageList2(string orderNO, int pageindex = 1, int pageSize = 10)
{
orderNO = "20185555555443342";
#region 分页
int RecordCount = 0;
int pageCount = 0;
string strField = "*";
int strOrderBy = 1;
string strSortField = "CreateTime desc";
string strSortKey = "TRA_BargainOrder_TestID";
//string strTable = "TRA_BargainOrder_Test";//单表
string strTable = "TRA_BargainOrder_Test A join UserInfo_test B ON A.UserID = B.Id";// 多表联合查询
string strWhere = "1=1";
int UsedTime = 0;
#endregion
#region 参数处理
//if (!string.IsNullOrEmpty(orderNO))
// {
// pageinationInfo.strWhere += " and BargainOrderCode like '%" + orderNO.Trim() + "%'";
// }
//if (!string.IsNullOrEmpty(orderNO))
//{
// strWhere += "and BargainOrderCode =" + orderNO.Trim() + "";
//}
#endregion
PageinationInfoService Service = new PageinationInfoService();
IList<TRA_BargainOrder_Test> List = Service.Get_PageinationInfoList<TRA_BargainOrder_Test>(strTable, strField, pageSize, pageindex, strWhere, strSortKey, strSortField, strOrderBy, out RecordCount,out pageCount);
#region 传值
//pageNumber，pageSize 我们已经传递到View，可以通过相关插件展现分页效果 在这里进行pagedlist分页
//pagedList还提供了另外一种方法：StaticPagedList 方法
//StaticPagedList 方法需要提供四个参数，分别为：数据源 当前页码 每页条数 以及总记录数
var PageList = new StaticPagedList<TRA_BargainOrder_Test>(List, pageindex, 10, RecordCount);
#region 页面用 ViewBag.List 显示列表信息时可以使用一下方式
//页面用 ViewBag.List 显示列表信息时可以使用一下方式
//// ViewBag.List = List;
//ViewBag.List = PageList;
//ViewBag.pageNumber = pageindex;
//ViewBag.pageSize = pageSize;
//ViewBag.RecordCount = RecordCount;
//ViewBag.BargainOrderCode = orderNO;
//return View();
#endregion
#region 页面用PagedList.StaticPagedList medel 实体展示分页数据
return View(PageList);
#endregion
#endregion
}
4、调用封装的存储通用类 PageinationInfoService
public class PageinationInfoService
{
/// <summary>
/// 获取分页列表 一种写法
/// 传入的参数是实体和PageinationInfo，实体主要是用于接收数据并封装到实体中
/// </summary>
/// <param name="pageinationInfo"></param>
/// <returns></returns>
public IList<Entity> GetPageinationInfoList<Entity>(PageinationInfo pageinationInfo) where Entity : class
{
dynamic result = null;
using (DefaultDbContext db = new DefaultDbContext())
{
#region SqlParameter参数
SqlParameter[] paras = new SqlParameter[9];
paras[0] = new SqlParameter("strTable", DbType.String);
paras[0].Value = pageinationInfo.strTable;
paras[1] = new SqlParameter("strField", DbType.String);
paras[1].Value = pageinationInfo.strField;
paras[2] = new SqlParameter("pageSize", DbType.Int32);
paras[2].Value = pageinationInfo.pageSize;
paras[3] = new SqlParameter("pageIndex", DbType.Int32);
paras[3].Value = pageinationInfo.pageIndex;
paras[4] = new SqlParameter("strWhere", DbType.String);
paras[4].Value = pageinationInfo.strWhere;
paras[5] = new SqlParameter("strSortKey", DbType.String);
paras[5].Value = pageinationInfo.strSortKey;
paras[6] = new SqlParameter("strSortField", DbType.String);
paras[6].Value = pageinationInfo.strSortField;
paras[7] = new SqlParameter("strOrderBy", DbType.Boolean);
paras[7].Value = pageinationInfo.strOrderBy;
paras[8] = new SqlParameter("RecordCount", DbType.Int32);
paras[8].Value = pageinationInfo.RecordCount;
paras[8].Direction = ParameterDirection.Output;
paras[9] = new SqlParameter("pageCount", DbType.Int32);
paras[9].Value = pageinationInfo.RecordCount;
paras[9].Direction = ParameterDirection.Output;
// paras[9] = new SqlParameter("UsedTime", DbType.Int32);
// paras[9].Value = pageinationInfo.UsedTime;
//paras[9].Direction = ParameterDirection.Output;
#endregion
try
{
result = db.Database.SqlQuery<Entity>("exec SP_Procedure_PageGX @strTable,@strField,@pageSize,@pageIndex,@strWhere,@strSortKey,@strSortField,@strOrderBy,@RecordCount output,@pageCount output", paras).ToList(); //,@UsedTime output
pageinationInfo.RecordCount = (int)paras[8].Value;
pageinationInfo.PageCount = (int)paras[9].Value;
// pageinationInfo.UsedTime = (int)paras[9].Value;
}
catch (Exception ex)
{
throw (ex);
}
}
return result;
}
/// <summary>
/// 第二种 封装直接传参写法 传入的参数是实体
/// </summary>
/// <typeparam name="Entity"></typeparam>
/// <returns></returns>
public IList<Entity> Get_PageinationInfoList<Entity>(string strTable, string strField, int pageSize, int pageIndex, string strWhere, string strSortKey, string strSortField, int strOrderBy, out int RecordCount, out int PageCount) where Entity : class
{
dynamic result = null;
using (DefaultDbContext db = new DefaultDbContext())
{
SqlParameter[] parameters =
{
new SqlParameter("@strTable", SqlDbType.VarChar),
new SqlParameter("@strField", SqlDbType.VarChar),
new SqlParameter("@pageSize", SqlDbType.Int),
new SqlParameter("@pageIndex", SqlDbType.Int),
new SqlParameter("@strWhere", SqlDbType.VarChar),
new SqlParameter("@strSortKey", SqlDbType.VarChar),
new SqlParameter("@strSortField", SqlDbType.VarChar),
new SqlParameter("@strOrderBy", SqlDbType.Int),
new SqlParameter("@RecordCount", SqlDbType.Int),
new SqlParameter("@pageCount", SqlDbType.Int)
};
parameters[0].Value = strTable;
parameters[1].Value = strField;
parameters[2].Value = pageSize;
parameters[3].Value = pageIndex;
parameters[4].Value = strWhere;
parameters[5].Value = strSortKey;
parameters[6].Value = strSortField;
parameters[7].Value = strOrderBy;
parameters[8].Direction = ParameterDirection.Output;
parameters[9].Direction = ParameterDirection.Output;
var data = db.Database.SqlQuery<Entity>("exec fyPager @strTable,@strField,@pageSize,@pageIndex,@strWhere,@strSortKey,@strSortField,@strOrderBy,@RecordCount output,@pageCount output", parameters).ToList();
int count = data.Count;
//
string Rcount = parameters[8].Value.ToString();
string Pagecount = parameters[9].Value.ToString();
//
PageCount = !string.IsNullOrEmpty(Pagecount) ? int.Parse(Pagecount) : 0;
RecordCount = !string.IsNullOrEmpty(Rcount) ? int.Parse(Rcount) : 0;
return data;
}
}
/// <summary>
/// 第三种 封装直接传参写法
/// </summary>
/// <typeparam name="Entity"></typeparam>
/// <param name="pageinationInfo"></param>
/// <returns></returns>
public IList<Entity> Get_PageinationInfo_List<Entity>(string strTable, string strField, string strSortField, string strWhere, int pageSize, int pageIndex, out int PageCount, out int RecordCount ) where Entity : class
{
dynamic result = null;
using (DefaultDbContext db = new DefaultDbContext())
{
SqlParameter[] parameters =
{
new SqlParameter("@TableName", SqlDbType.VarChar),
new SqlParameter("@Fields", SqlDbType.VarChar),
new SqlParameter("@OrderField", SqlDbType.VarChar),
new SqlParameter("@sqlWhere", SqlDbType.VarChar),
new SqlParameter("@pageSize", SqlDbType.Int),
new SqlParameter("@pageIndex", SqlDbType.Int),
new SqlParameter("@TotalPage", SqlDbType.Int),
new SqlParameter("@RecordCount", SqlDbType.Int)
};
parameters[0].Value = strTable;
parameters[1].Value = strField;
parameters[2].Value = strSortField;
parameters[3].Value = strWhere;
parameters[4].Value = pageSize;
parameters[5].Value = pageIndex;
parameters[6].Direction = ParameterDirection.Output;
parameters[7].Direction = ParameterDirection.Output;
var data = db.Database.SqlQuery<Entity>("exec SP_Procedure_PageGX @TableName,@Fields,@OrderField,@sqlWhere,@pageSize,@pageIndex,@TotalPage output,@RecordCount output", parameters).ToList();
int count = data.Count;
//
string Pagecount = parameters[6].Value.ToString();
string Rcount = parameters[7].Value.ToString();
//
PageCount = !string.IsNullOrEmpty(Pagecount) ? int.Parse(Pagecount) : 0;
RecordCount = !string.IsNullOrEmpty(Rcount) ? int.Parse(Rcount) : 0;
return data;
}
}
}
5、相关Model实体类
public class TRA_BargainOrder_Test
{
public int ischeck
{
get; set;
}
public long TRA_BargainOrder_TestID
{
get; set;
}
/// <summary>
/// 订单编号
/// </summary>
public string BargainOrderCode
{
get; set;
}
/// <summary>
/// 城市代号
/// </summary>
public string CityCode
{
get; set;
}
public string ParkUserId
{
get; set;
}
public int FlowStatus
{
get; set;
}
public DateTime UpdateTime
{
get; set;
}
public int OrderStatus
{
get; set;
}
public int PayStatus
{
get; set;
}
public DateTime CreateTime
{
get; set;
}
public string ExpressCode { get; set; }
/// <summary>
/// 用户ID
/// </summary>
public int UserID { get; set; }
#region 涉及到用户信息表数据
/// <summary>
/// 用户名
/// </summary>
public string UserName
{
get; set;
}
/// <summary>
/// 地址
/// </summary>
public string LocalAddress
{
get; set;
}
#endregion
}
6、页面展示效果图 在这里说明下 通过单表或者多表 都可以实现数据分页效果
7、存储之第二种方式 参数性对少点 效果性能 也是不错 自行选择
ALTER PROCEDURE [dbo].[SP_Procedure_PageGX] ( @TableName varchar (5000), --要显示的表或多个表的连接 @Fields varchar(3000)='*', --表中的字段，可以使用*代替 @OrderField varchar(500), --要排序的字段 @sqlWhere varchar(500)=NULL, --WHERE子句 @pageSize int, --分页的大小 @pageIndex int, --要显示的页的索引 @TotalPage int output, --页的总数 @RecordCount int output --总记录数 ) as begin Begin Tran Declare @sql nvarchar(4000); Declare @totalRecord int; --记录总数 if (@sqlWhere IS NULL or @sqlWhere = '') set @sql = 'select @totalRecord = count(*) from ' + @TableName else set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere --执行sql语句得到记录总数 EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize) select @RecordCount=CEILING(@totalRecord) --根据特定的排序字段为为行分配唯一ROW_NUMBER的顺序 if (@sqlWhere IS NULL or @sqlWhere = '') set @sql = 'select * from (select ROW_NUMBER() over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName else set @sql = 'select * from (select ROW_NUMBER() over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere --确保当前页的索引在合理的范围之内 if @PageIndex<=0 Set @pageIndex = 1 if @pageIndex>@TotalPage Set @pageIndex = @TotalPage --得到当前页在整个结果集中准确的ROW_NUMBER值 Declare @StartRecord int Declare @EndRecord int set @StartRecord = (@pageIndex-1)*@PageSize + 1 set @EndRecord = @StartRecord + @pageSize - 1 --输出当前页中的数据 set @Sql = @Sql + ') as t' + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord) Exec(@Sql) If @@Error <> 0 Begin RollBack Tran Return -1 End Else Begin Commit Tran Return @totalRecord End End
执行测试：
USE [test] GO DECLARE @return_value int, @TotalPage int, @RecordCount int EXEC @return_value = [dbo].[SP_Procedure_PageGX] @TableName = N'TRA_BargainOrder_Test A join UserInfo_test B ON A.UserID = B.Id', @Fields = N'*', @OrderField = N'CreateTime DESC', @sqlWhere = N'1=1', @pageSize = 10, @pageIndex = 8, @TotalPage = @TotalPage OUTPUT, @RecordCount = @RecordCount OUTPUT SELECT @TotalPage as N'@TotalPage', @RecordCount as N'@RecordCount' SELECT 'Return Value' = @return_value GO
8、view层代码
GxFy_PageList3
<link href="~/Content/bootstrap.css" rel="stylesheet" /> <link href="~/Content/PagedList.css" rel="stylesheet" /> @model PagedList.StaticPagedList<EFAutofacMVC.Models.TRA_BargainOrder_Test> @using PagedList.Mvc @using PagedList @using (Html.BeginForm("Index", "Home", FormMethod.Get)) { <div class="well"> <table class="table"> @* <thead> <tr> <th> <input id="UserName" name="UserName" type="text" placeholder="请输入用户名" /> </th> <th> <input id="OrderNum" name="OrderNum" type="text" placeholder="请输入订单号" /> </th> <th> <input id="Submit1" type="submit" value="submit" /> </th> </tr> </thead>*@ <tr> <th>用户名</th> <th>地址</th> <th>订单编号</th> <th>城市代号</th> <th>时间</th> <th>订单状态</th> </tr> <tbody> @* @foreach (var item in ViewBag.List) *@ @*用model展示数据*@ @foreach (var item in Model) { <tr> <td>@item.UserName </td> <td>@item.LocalAddress </td> <td>@item.BargainOrderCode </td> <td>@item.CityCode </td> <td>@item.UpdateTime </td> <td>@item.OrderStatus </td> </tr> } </tbody> <tfoot> <tr> <td colspan="5"> <div class=""> @if (Model != null) { <span style="height: 20px; line-height: 20px;">共 @Model.TotalItemCount.ToString() 条记录，当前第 @Model.PageNumber 页/共 @Model.PageCount 页 </span> @Html.PagedListPager(Model, pageindex => Url.Action("GxFy_PageList3", new { pageindex }), new PagedListRenderOptions() { LinkToFirstPageFormat = "首页", LinkToNextPageFormat = "下一页", LinkToPreviousPageFormat = "上一页", LinkToLastPageFormat = "末页", DisplayItemSliceAndTotal = false, MaximumPageNumbersToDisplay = 3 }) } </div> </td> </tr> </tfoot> </table> </div> }
9、控制器代码
/// <summary> /// 第三种 /// </summary> /// <param name="orderNO"></param> /// <param name="pageindex"></param> /// <param name="pageSize"></param> /// <returns></returns> public ActionResult GxFy_PageList3(string orderNO, int pageindex = 1, int pageSize = 10) { orderNO = "20185555555443342"; #region 分页 int RecordCount = 0; int pageCount = 0; string strField = "*"; string strSortField = "CreateTime desc"; //string strTable = "TRA_BargainOrder_Test";//单表 string strTable = "TRA_BargainOrder_Test A join UserInfo_test B ON A.UserID = B.Id";// 多表联合查询 string strWhere = "1=1"; int UsedTime = 0; #endregion #region 参数处理 //if (!string.IsNullOrEmpty(orderNO)) // { // pageinationInfo.strWhere += " and BargainOrderCode like '%" + orderNO.Trim() + "%'"; // } //if (!string.IsNullOrEmpty(orderNO)) //{ // strWhere += "and BargainOrderCode =" + orderNO.Trim() + ""; //} #endregion PageinationInfoService Service = new PageinationInfoService(); IList<TRA_BargainOrder_Test> List = Service.Get_PageinationInfo_List<TRA_BargainOrder_Test>(strTable, strField, strSortField, strWhere, pageSize, pageindex, out pageCount, out RecordCount); #region 传值 //pageNumber，pageSize 我们已经传递到View，可以通过相关插件展现分页效果 在这里进行pagedlist分页 //pagedList还提供了另外一种方法：StaticPagedList 方法 //StaticPagedList 方法需要提供四个参数，分别为：数据源 当前页码 每页条数 以及总记录数 var PageList = new StaticPagedList<TRA_BargainOrder_Test>(List, pageindex, 10, RecordCount); #region 页面用 ViewBag.List 显示列表信息时可以使用一下方式 //页面用 ViewBag.List 显示列表信息时可以使用一下方式 //// ViewBag.List = List; //ViewBag.List = PageList; //ViewBag.pageNumber = pageindex; //ViewBag.pageSize = pageSize; //ViewBag.RecordCount = RecordCount; //ViewBag.BargainOrderCode = orderNO; //return View(); #endregion #region 页面用PagedList.StaticPagedList medel 实体展示分页数据 return View(PageList); #endregion #endregion }
10、封装层存储调用类中方法 Get_PageinationInfo_List 即可 效果展示 如同上
扫一扫
1227
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
