主要的技术点不在这里一一阐述，相关存储也是引用别人的，主要技术点就是通过最优性能方式处理需求，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 = , int pageSize = )
{
orderNO = "";
#region 分页 int RecordCount = ;
int pageCount = ;
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 = ; #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, , 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 即可 效果展示 如同上