当数据量过大,有几万甚至十几万条数据时,每次都从数据库中取出所有数据就会降低查询效率,系统运行慢,还有可能卡死,这时假分页就会显得很不人性化,因此有了真分页的必要性。
正如上篇博文总结归纳,“真”相对于“假”存在,即不藕断丝连,从根部彻底断开,在此处表现为根据查询条件,只从数据库中提取出需要的部分,适合于大数据。而真分页的实现要借助于第三方控件AspNetPager。
首先需要下载AspNetPager控件,下载DLL文件:http://www.webdiyer.com/downloads
在VS中引用AspNetPager控件,欢迎参考博文《VS添加Ajax》中添加选择项部分(有图有真相),此处不再赘述:http://blog.csdn.net/u010773667/article/details/38518461
首先在web窗体中拖放一个gridview控件用来显示数据,选中AspNetPager控件拖拽到web窗体相应位置用来进行分页设置。选中spNetPager控件,在右下角将会显现一个小按钮,单击打开,对导航按钮显示文本进行设置。
运行后效果:
对页索引文本或下拉框进行设置
改进效果见下图:
如果还想显示的更加具体,可进行自定义信息区显示方式及内容设置
上述对控件进行的所有设置将在VS中自动生成相应代码,我们也可以通过手动输入代码进行设置,此处不介绍。
好了,设置好了前台,接下来就要进行数据绑定了(注意:在方法anpCa_PageChanged()中绑定了caid=6,此处需要重新获得类别ID,我没有解决。。。希望会的朋友友情提示一下)
- protected void Page_Load(object sender, EventArgs e)
- {
- if (!Page .IsPostBack )
- {
- string caid = Request.QueryString["caid"];
- DataTable dt = new NewsManager().SelectAllNewsByCaId(caid);
- anpCa.AlwaysShow = true;
- anpCa.PageSize = 5;
- anpCa.RecordCount = dt.Rows.Count;
- int startIndex = anpCa.PageSize * 0;
- int endIndex = anpCa.PageSize * 1;
- gvDataBind(caid, startIndex, endIndex);
- }
- }
- private void gvDataBind(string caid,int startIndex,int endIndex)
- {
- DataTable dt = new NewsManager().SelectPartNewsByCaId(caid, startIndex, endIndex);
- if (dt.Rows.Count != 0)
- {
- lblCategory.Text = dt.Rows[0]["name"].ToString(); //使类别标题显示相应的类别名称
- }
- gvNew.DataSource = dt;
- gvNew.DataBind();
- }
- protected void anpCa_PageChanged(object sender, EventArgs e)
- {
- string caid = "6";
- int startIndex = anpCa.PageSize * (anpCa.CurrentPageIndex - 1)+1;
- int endIndex = anpCa.PageSize * (anpCa.CurrentPageIndex);
- gvDataBind(caid, startIndex, endIndex);
- }
- }
protected void Page_Load(object sender, EventArgs e)
{
if (!Page .IsPostBack )
{
string caid = Request.QueryString["caid"];
DataTable dt = new NewsManager().SelectAllNewsByCaId(caid);
anpCa.AlwaysShow = true;
anpCa.PageSize = 5;
anpCa.RecordCount = dt.Rows.Count;
int startIndex = anpCa.PageSize * 0;
int endIndex = anpCa.PageSize * 1;
gvDataBind(caid, startIndex, endIndex);
}
}
private void gvDataBind(string caid,int startIndex,int endIndex)
{
DataTable dt = new NewsManager().SelectPartNewsByCaId(caid, startIndex, endIndex);
if (dt.Rows.Count != 0)
{
lblCategory.Text = dt.Rows[0]["name"].ToString(); //使类别标题显示相应的类别名称
}
gvNew.DataSource = dt;
gvNew.DataBind();
}
protected void anpCa_PageChanged(object sender, EventArgs e)
{
string caid = "6";
int startIndex = anpCa.PageSize * (anpCa.CurrentPageIndex - 1)+1;
int endIndex = anpCa.PageSize * (anpCa.CurrentPageIndex);
gvDataBind(caid, startIndex, endIndex);
}
}
在D层数据查询的部分代码展示
- </pre></p><p></p><pre class="html" name="code">
</pre></p><p></p><pre class="html" name="code">
- </pre><span style="font-family:华文楷体;font-size: 14pt;"></span><pre class="html" name="code">#region 根据类别ID取出该类别下的所有新闻的分页显示
- /// <summary>
- /// 根据类别ID取出该类别下的所有新闻
- /// </summary>
- /// <param name="caId">类别ID</param>
- /// <returns></returns>
- public DataTable SelectPartNewsByCaId(string caId,int startIndex, int endIndex)
- {
- DataTable dt = new DataTable();
- SqlParameter[] paras = new SqlParameter[]
- {
- new SqlParameter ("@caId",caId ),
- new SqlParameter ("@startIndex",startIndex ),
- new SqlParameter ("@endIndex",endIndex )
- };
- dt = sqlhelper.ExecuteQuery("dbo.category_showpage", paras, CommandType.StoredProcedure);
- return dt;
- }
- #endregion
</pre><span style="font-family:华文楷体;font-size: 14pt;"></span><pre class="html" name="code">#region 根据类别ID取出该类别下的所有新闻的分页显示
/// <summary>
/// 根据类别ID取出该类别下的所有新闻
/// </summary>
/// <param name="caId">类别ID</param>
/// <returns></returns>
public DataTable SelectPartNewsByCaId(string caId,int startIndex, int endIndex)
{
DataTable dt = new DataTable();
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter ("@caId",caId ),
new SqlParameter ("@startIndex",startIndex ),
new SqlParameter ("@endIndex",endIndex )
};
dt = sqlhelper.ExecuteQuery("dbo.category_showpage", paras, CommandType.StoredProcedure);
return dt;
}
#endregion
存储过程(很重要)
- -- =============================================
- -- Author: 王英群
- -- Create date: 2014-8-10
- -- Description: 跟据类别ID取出该类别下的所有新闻的分页显示
- -- =============================================
- ALTER PROCEDURE [dbo].[category_showpage]
- @caid int,
- @startIndex int,
- @endIndex int
- AS
- BEGIN
- with temptable as (
- select ROW_NUMBER() over (order by id desc) as 行号, * from
- (
- select n.id,n.titile,n.createTime,c.[name],n.caId from news n
- inner join category c on n.caId =c.id and n.caId =@caid
- ) as aa
- )
- select * from temptable where 行号 between @startIndex and @endIndex
- END
-- =============================================
-- Author: 王英群
-- Create date: 2014-8-10
-- Description: 跟据类别ID取出该类别下的所有新闻的分页显示
-- =============================================
ALTER PROCEDURE [dbo].[category_showpage]
@caid int,
@startIndex int,
@endIndex int
AS
BEGIN
with temptable as (
select ROW_NUMBER() over (order by id desc) as 行号, * from
(
select n.id,n.titile,n.createTime,c.[name],n.caId from news n
inner join category c on n.caId =c.id and n.caId =@caid
) as aa
)
select * from temptable where 行号 between @startIndex and @endIndex
END
运行后效果见下图:
注意:我的程序中多了一个参数(类别ID),在页索引动态变化的过程中需要一直重新获得,这一点我没有实现,希望小伙伴们可以帮助我,谢谢!
结合上篇博文,假分页适合于数据量相对较小的情况下,而真分页适合于数据量大的情况下。真假分页的使用,为我们的阅读减负。