在Asp.Net MVC环境下利用存储过程进行分页的实现
概述
我们在做项目的时候,大多数情况都会遇到产品、资讯、人员、订单的列表显示,有列表显示自然就会涉及到分页技术。Asp.Net MVC没有控件,不像WebForm中有GridView控件可以自动帮我们实现分页。因此在MVC中就要我们自己手动实现分页。这里共涉及到三个方面:存储过程、列表分页显示、页码导航条,下面分别叙述实现的具体过程。
一、存储过程
我们有视图为什么还要用存储过程?假设,符合条件的查询结果总共有10万条记录,但是我们只需要在当前页面呈现出第101至120条记录,那么结果集中其余99.98%的数据不是我们需要的。如果我们将10万条记录全部取出并放入内存,然后将其中20条记录输出给前端页面,这样势必造成内存的极大浪费和执行效率的降低。使用存储过程后,只需要传入参数,存储过程便会查询并输出我们真正需要的那第101至120条记录,其余的10万条记录不会输出。
具体的存储过程代码如下:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Procedure_Name]
@PageSize int , -- 页尺寸
@PageIndex int, -- 页码
@TableName nvarchar(100), --表名
@ShowFiled varchar(500), --显示字段
@Orderby nvarchar(100), --排序字段, 反序请加上 Desc
@strwhere varchar(500)--条件
AS
declare @sqlstr varchar(5000)
BEGIN
set @sqlstr='select '+@ShowFiled+' from (SELECT '+@ShowFiled+' ,row_number() over(order by '+@Orderby+') as idrank from '+@TableName+' where 1 = 1 and '+@strwhere+ ' )as t where idrank >'+str((@PageIndex-1)*@pagesize)+' and idrank<='+str((@PageIndex-1)*@pagesize+@pagesize)+' ORDER BY '+@Orderby
exec (@sqlstr)
END
参数说明:
@PageSize:每页面显示的记录条数
@PageIndex:当前要显示的页码(即第几页)
@TableName:表名或者是视图名称
@ShowFiled:需要显示的字段(没有必要把所有的字段都显示出来)
@Orderby:排序字段, 反序请加上 Desc
@strwhere:查询条件
这个存储过程里面有一个 row_number() over 函数,是该存储过程的核心。
从字面上看,row_number() 就是“行号”。这个函数的作用是:从1开始,根据over(Order by ) 的排序条件,对排序后的结果集的每一条记录进行连续编号。
这个存储过程先将查询结果进行排序,用 row_number() over函数 新增一个行号字段命名为 idrank,并命名为表t。我们把这部分SQL语句提取出来看一下:
SELECT '+@ShowFiled+' ,row_number() over(order by '+@Orderby+') as idrank from '+@TableName+' where 1 = 1 and '+@strwhere+ '
--带入参数后如下
SELECT [ProductID],[ProductName],row_number() over(order by [ProductID]) as idrank from [dbo].[View_Product] where 1 = 1
查询出的结果如下:
我们可以看到 idrank 是从1开始排序的。
然后,根据@PageSize 和 @PageIndex 的计算结果,筛选出符合idrank范围的记录就可以了。
二、列表分页显示
1.利用SqlHelper获取数据
在数据访问层新建一个方法,用于向存储过程传入参数,并接收返回的结果集。具体代码如下:
/// <summary>
/// 存储过程分页
/// </summary>
/// <param name="Tablename">表名</param>
/// <param name="ShowFiled">显示字段</param>
/// <param name="StrWhere">Where语句(不带Where)</param>
/// <param name="Orderby">Orderby语句 反序请加Desc</param>
/// <param name="PageSize">每页记录数</param>
/// <param name="PageIndex">页码</param>
/// <returns></returns>
public static SqlDataReader QueryByProcedurePage(string Tablename, string ShowFiled, string StrWhere, string Orderby, int PageSize, int PageIndex)
{
SqlConnection Con = new SqlConnection(ConnStr);
Con.Open();
SqlCommand cmd = new SqlCommand("上面刚写的那个存储过程名", Con);
try
{
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter P1, P2, P3, P4, P5, P6;
P1 = new SqlParameter("@PageSize", SqlDbType.Int, 4);
P2 = new SqlParameter("@PageIndex", SqlDbType.Int, 4);
P3 = new SqlParameter("@TableName", SqlDbType.NVarChar, 100);
P4 = new SqlParameter("@ShowFiled", SqlDbType.NVarChar, 500);
P5 = new SqlParameter("@Orderby", SqlDbType.NVarChar, 500);
P6 = new SqlParameter("@strwhere", SqlDbType.NVarChar, 500);
P1.SqlValue = PageSize;
P2.SqlValue = PageIndex;
P3.SqlValue = Tablename.ToString();
P4.SqlValue = ShowFiled.ToString();
P5.SqlValue = Orderby.ToString();
P6.SqlValue = StrWhere.ToString();
cmd.Parameters.Add(P1);
cmd.Parameters.Add(P2);
cmd.Parameters.Add(P3);
cmd.Parameters.Add(P4);
cmd.Parameters.Add(P5);
cmd.Parameters.Add(P6);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception)
{
Con.Close();
throw;
}
ConnStr 是连接字符串,根据实际情况自己写就可以了。
2.显示列表
首先,从页面URL接收表示当前页码数的参数(PageIndex)。 接收进来的参数要先进行合法性判断:非负值、非数值都要进行检查。
其次,计算总页数。 根据SQL语句计算出满足查询条件的记录数(RecordCount),根据自己设定好的每页显示的记录条数(PageSize),计算出页总数(PageCount)。注意:PageCount = RecordCount ÷ PageSize,当除不尽有余数时,要给总页数+1。
if (RecordCount % PageSize != 0)
{
PageCount = RecordCount / PageSize + 1;
}
else
{
PageCount = RecordCount / PageSize;
}
计算出总页数后,还要对传入的当前页码数的合理性进行判断,既不能小于1,也不能大于页总数。
if (Request.QueryString["Page"] != null)
{
if (DAL.General.IsNumeric(Request.QueryString["Page"].ToString()))
{
CurrentPage = int.Parse(Request.QueryString["Page"].ToString());
if (CurrentPage < 1)
{
CurrentPage = 1;
}
//计算CurrentPage(当前页)是否超出最大页码范围
if (CurrentPage > PageCount)
{
CurrentPage = PageCount;
}
}
}
然后,获取当前要显示的数据。 我这里是利用泛型List<>获取的数据:
List<Models.Product> ProductList = new BLL.Product().GetProductList(Str, "OnTop DESC, ProductID Desc ", PageSize, CurrentPage);
最后,将这些参数传给ViewBag。
三、页码导航条
导航条有两种:简易版和美观版。简易版适合后台使用,功能简洁,算法简单容易实现。美观版适合前台使用,用户体验较好,效果美观,但算法较为复杂。
1、简易版
简易版的效果如下:
为了方便各页面引用,可以将导航条部分提取出来,做成分部页。分部页的视图代码如下:
<div class="form-inline" style="text-align: center; margin-top:35px;">
当前第 <span style="color:red">@ViewBag.CurrentPage</span> 页 / 共 <span style="color:red">@ViewBag.PageCount</span> 页
<a href="@ViewBag.Url_Fist" class="pager">首页</a><a href="@ViewBag.Url_Prev" class="pager">上页</a><a href="@ViewBag.Url_Next" class="pager">下页</a><a href="@ViewBag.Url_End" class="pager">尾页</a>
</div>
分部页的动作方法代码如下:
public ActionResult Index(string UrlString, int CurrentPage, int PageCount)
{
if (PageCount ==0)
{
return Content("");
}
if (CurrentPage > PageCount)
{
CurrentPage = PageCount;
}
ViewBag.CurrentPage = CurrentPage;
ViewBag.PageCount = PageCount;
ViewBag.Url_Fist = UrlString + "Page=1";
ViewBag.Url_End = UrlString + "Page=" + PageCount;
if (CurrentPage > 1)
{
ViewBag.Url_Prev = UrlString + "Page=" + (CurrentPage - 1).ToString();
}
else
{
ViewBag.Url_Prev = UrlString + "Page=1";
}
if (CurrentPage == PageCount)
{
if (CurrentPage >1)
{
ViewBag.Url_Next = UrlString + "Page=" + PageCount;
}
else
{
ViewBag.Url_Next =UrlString + "Page=1";
}
}
else
{
ViewBag.Url_Next = UrlString + "Page=" + (CurrentPage + 1).ToString();
}
return PartialView("Partial_Pager");
}
在分部页的动作方法中需要传入3个参数:UrlString、CurrentPage 和 PageCount。UrlString:用于传入除页码参数外的完整的链接地址。
动作方法需要输出的参数有:
ViewBag.CurrentPage:当前页
ViewBag.PageCount :总页数
ViewBag.Url_Fist :首页链接
ViewBag.Url_End:尾页链接
ViewBag.Url_Prev:上一页链接
ViewBag.Url_Next:下一页链接
算法逻辑比较简单,不再赘述。
2、美观版
美观版主要用于在前台呈现分页导航,效果美观用户体验较好,但算法略复杂。
该分页导航可以实现:
1、当前页高亮显示
2、非首页和尾页时,有 “《 ” 和 “》”出现
3、首页时 隐藏 “《 ”,尾页时隐藏“》”
4、当前页与首页或尾页的距离足够大时有“…”出现
由于分页导航可能会有多种呈现效果,无法在视图页面设计好模板进行呈现,所以只能在控制器动作方法中拼凑Html代码,然后通过ViewBag.Pager输出给视图页,在视图页以@Html.Raw(ViewBag.Pager)
方法解析成Html代码,呈现给前端。
控制器方法的难点和重点在于:当前页与首页和尾页的位置关系判断。
先设置4个字符串变量:
StrPagerStart:拼接导航的起始部分
StrMid:拼接导航的中间部分
StrPagerEnd:拼接导航的结尾部分
设3个位置节点:首页、尾页、当前页,这3个节点的位置关系有四种情况:
第一种情况:三点重合。 首页、当前页、尾页重合,也就是列表只有一页,这种情况最为简单。
代码如下:
//1、三点重合:即只有1页
if (CurrentPage == 1 && CurrentPage == PageCount)
{
StrPagerStart = "<li class=\"active\"><a>1</a></li>";
}
第二种情况:首页与当前页重合。 这种情况当前页为第一页,且当前页数不等于总页数(即页总数大于等于2)。这里又分成两种情况:
1、总页数小于等于4,则生成2、3、4页、next页
2、总页数大于4,则生成2、3页、…页、最后一页、next页
第二种情况代码如下:
//2、第一页和当前页重合
if (CurrentPage == 1 && CurrentPage != PageCount)
{
//生成第1页
StrPagerStart = "<li class=\"active\"><a>1</a></li>";
//总页数小于等于4,则生成2、3、4页、next页
if (PageCount <= 4)
{
StrMid = "";
for (int i = 2; i <= PageCount; i++)
{
StrMid += "<li><a href=\"" + BaseUrlString + i.ToString() + "\">" + i.ToString() + "</a></li>";
}
StrPagerEnd = "<li><a href=\"" + BaseUrlString + (CurrentPage + 1).ToString() + "\" aria-label=\"Previous\"><span aria-hidden=\"true\">»</span></a></li>";
}
else
{
//总页数大于4,则生成2、3页、...页、最后一页、next页
StrMid = "";
StrMid += "<li><a href=\"" + BaseUrlString + "2" + "\">2</a></li>";
StrMid += "<li><a href=\"" + BaseUrlString + "3" + "\">3</a></li>";
StrMid += "<li><a>...</a></li>";
StrPagerEnd = "<li><a href=\"" + BaseUrlString + PageCount.ToString() + "\">" + PageCount.ToString() + "</a></li>";
StrPagerEnd += "<li><a href=\"" + BaseUrlString + (CurrentPage + 1).ToString() + "\" aria-label=\"Previous\"><span aria-hidden=\"true\">»</span></a></li>";
}
}
第三种情况:当前页在首页和最后一页中间(与两端不重合)。
因为第三种情况下,当前页在中间,所以pre页、第1页、最后页、next页都会出现。这样就先把 StrPagerStart 和 StrPagerEnd 的部分生成。
//生成 Pre页、第1页
StrPagerStart = "<li><a href=\"" + BaseUrlString + (CurrentPage - 1).ToString() + "\" aria-label=\"Previous\"><span aria-hidden=\"true\">«</span></a></li>";
StrPagerStart += "<li><a href=\"" + BaseUrlString + "1\">1</a></li>";
//生成 最后一页、next页
StrPagerEnd = "<li><a href=\"" + BaseUrlString + PageCount.ToString() + "\">" + PageCount.ToString() + "</a></li>";
StrPagerEnd += "<li><a href=\"" + BaseUrlString + (CurrentPage + 1).ToString() + "\" aria-label=\"Previous\"><span aria-hidden=\"true\">»</span></a></li>";
第三种情况分成两大类,每类里面又分别有两种情况。
1、当前页距首页小于等于3,先生成第2页至当前页
1.1、距尾页小于等于3,则生成 当前+1页 至倒数第2页
1.2、距尾页大于4,则生成 当前页+1页、…页
2、当前页距首页大于4,则生成…页、当前-1页、当前页
2.1、距尾页小于等于2,则生成 当前+1 页 至 倒数第2页
2.2、距尾页大于等于3,则生成CurrentPage+1页、…页
第三种情况全部代码如下:
//3、当前页在第1页和最后一页中间(与两端不重合)
if (CurrentPage > 1 && CurrentPage < PageCount)
{
//生成 Pre页、第1页
StrPagerStart = "<li><a href=\"" + BaseUrlString + (CurrentPage - 1).ToString() + "\" aria-label=\"Previous\"><span aria-hidden=\"true\">«</span></a></li>";
StrPagerStart += "<li><a href=\"" + BaseUrlString + "1\">1</a></li>";
//生成 最后一页、next页
StrPagerEnd = "<li><a href=\"" + BaseUrlString + PageCount.ToString() + "\">" + PageCount.ToString() + "</a></li>";
StrPagerEnd += "<li><a href=\"" + BaseUrlString + (CurrentPage + 1).ToString() + "\" aria-label=\"Previous\"><span aria-hidden=\"true\">»</span></a></li>";
StrMid = "";
if (CurrentPage <= 3) //距前(第1页)小于等于3
{
//生成第2页至当前页
for (int i = 2; i < CurrentPage + 1; i++)
{
if (i == CurrentPage)
{
StrMid += "<li class=\"active\"><a>" + i.ToString() + "</a></li>"; //当前页加class="active",去掉链接
}
else
{
StrMid += "<li><a href=\"" + BaseUrlString + i.ToString() + "\">" + i.ToString() + "</a></li>";
}
}
if (PageCount - CurrentPage <= 3) //距后小于等于3
{
//生成CurrentPage+1页至倒数第2页
for (int i = CurrentPage + 1; i < PageCount; i++)
{
StrMid += "<li><a href=\"" + BaseUrlString + i.ToString() + "\">" + i.ToString() + "</a></li>";
}
}
else
{
//距后大于等于4
//生成CurrentPage+1页、...页
StrMid += "<li><a href=\"" + BaseUrlString + (CurrentPage + 1).ToString() + "\">" + (CurrentPage + 1).ToString() + "</a></li>";
StrMid += "<li><a>...</a></li>";
}
}
else
{
//距前(第1页)大于4
//生成...页、CurrentPage-1页、CurrentPage页
StrMid = "<li><a>...</a></li>";
StrMid += "<li><a href=\"" + BaseUrlString + (CurrentPage - 1).ToString() + "\">" + (CurrentPage - 1).ToString() + "</a></li>";
StrMid += "<li class=\"active\"><a>" + CurrentPage.ToString() + "</a></li>";
if (PageCount - CurrentPage <= 2) //距后小于等于2
{
//生成CurrentPage+1页至倒数第2页
for (int i = CurrentPage + 1; i < PageCount; i++)
{
StrMid += "<li><a href=\"" + BaseUrlString + i.ToString() + "\">" + i.ToString() + "</a></li>";
}
}
else
{
//距后大于等于3
//生成CurrentPage+1页、...页
StrMid += "<li><a href=\"" + BaseUrlString + (CurrentPage + 1).ToString() + "\">" + (CurrentPage + 1).ToString() + "</a></li>";
StrMid += "<li><a>...</a></li>";
}
}
}
第四种情况:当前页与最后一页重合。 这种情况,当前页为最后一页,且当前页数不等于1(即页总数大于等于2)。
先生成尾页和Pre页:
//生成尾页
StrPagerEnd = "<li class=\"active\"><a>" + CurrentPage.ToString() + "</a></li>";
//生成Pre页
StrPagerStart = "<li><a href=\"" + BaseUrlString + (CurrentPage - 1).ToString() + "\" aria-label=\"Previous\"><span aria-hidden=\"true\">«</span></a></li>";
这里分成两种情况:
1、总页数小于等于4,则生成第1页至 当前-1 页
2、总页数大于4,则生成第1页、…页、PageCount-2页、PageCount-1页、最后一页
第四种情况全部代码如下:
//4、当前页和最后一页重合
if (CurrentPage == PageCount && CurrentPage != 1)
{
//生成最后一页
StrPagerEnd = "<li class=\"active\"><a>" + CurrentPage.ToString() + "</a></li>";
//生成Pre页
StrPagerStart = "<li><a href=\"" + BaseUrlString + (CurrentPage - 1).ToString() + "\" aria-label=\"Previous\"><span aria-hidden=\"true\">«</span></a></li>";
//总页数小于等于4,则生成第1页至CurrentPage-1页
if (PageCount <= 4)
{
StrMid = "";
for (int i = 1; i < PageCount; i++)
{
if (i == CurrentPage)
{
StrMid += "<li class=\"active\"><a>" + i.ToString() + "</a></li>"; //当前页加class="active",去掉链接
}
else
{
StrMid += "<li><a href=\"" + BaseUrlString + i.ToString() + "\">" + i.ToString() + "</a></li>";
}
}
}
else
{
//总页数大于4,则生成第1页、...页、PageCount-2页、PageCount-1页、最后一页
StrMid = "<li><a href=\"" + BaseUrlString + "1\">1</a></li>";
StrMid += "<li><a>...</a></li>";
for (int i = PageCount-2; i <= PageCount-1; i++)
{
if (i == CurrentPage)
{
StrMid += "<li class=\"active\"><a>" + i.ToString() + "</a></li>" ; //当前页加class="active",去掉链接
}
else
{
StrMid += "<li><a href=\"" + BaseUrlString + i.ToString() + "\">" + i.ToString() + "</a></li>";
}
}
}
}
至此,分页和分页导航功能全部实现。