ASP.Net 一步一步学习分页(适合初学者)
1. 方式一,原始分页:
适应性比较好,但代码结构凌乱,分页链接的生成参见 <<通用手动分页方方法>> ,包含用到的GetPageLinks()函数
SQLHelper db = new SQLHelper(g_ConnStr);
db.Open();
DataSet dt = db.ExecuteDataSet("SELECT * FROM [DCMS_Article] where channel_id=16 order by art_date desc");
if (dt.Tables[0].Rows.Count != 0)
{
int nPageSize = 10;
int nShowNum = 5;
int nRecord = dt.Tables[0].Rows.Count;
int CurPage = Utils.ToInt(Request.QueryString["page"]);
PagedDataSource fenye = new PagedDataSource();
fenye.AllowPaging = true;
fenye.DataSource = dt.Tables[0].DefaultView;
fenye.PageSize = nPageSize;
int PageCount = fenye.PageCount;
CurPage = CurPage == 0 ? 1 : CurPage;
CurPage = CurPage > PageCount ? PageCount : CurPage;
fenye.CurrentPageIndex = CurPage - 1;
rpt_list_news.DataSource = fenye;
lblPageLinks.Text = PageCount > 0 ? Utils.GetPageLinks(nRecord, nPageSize, nShowNum) : "";
}
else
{
rpt_list_news.DataSource = dt;
}
rpt_list_news.DataBind();
db.Close();
2.方式二,换个方式简化调用:
稍微简化了调用方式,但使用有一定局限性
private void Bind()
{
SQLHelper db = new SQLHelper(g_ConnStr);
db.Open();
string strsql="SELECT * FROM [DCMS_Article] where channel_id=16 order by art_date desc" ;
int nPageSize = 2;
int nShowNum = 5;
int nRecord =0;
int CurPage = Utils.ToInt(Utils.Get( "page"));
DataSet dt = db.GetPagedDataSet(strsql, CurPage, nPageSize, out nRecord);
rpt_list_news.DataSource = dt.Tables[0];
lblPageLinks.Text = Utils.GetPageLinks(nRecord, nPageSize, nShowNum);
rpt_list_news.DataBind();
db.Close();
}
用到的GetPagedDataSet函数如下
public DataSet GetPagedDataSet(string SQLString, int CurPage, int PageSize, out int RecordCount)
{
DataSet ds = new DataSet();
using (SqlDataAdapter da = new SqlDataAdapter(SQLString, conn))
{
SQLString = System.Text.RegularExpressions.Regex.Replace(SQLString, "order by.*" , "");
RecordCount = (int)ExecuteScalar(string.Format("select count(*) from ({0}) as temp", SQLString));
//内部校正一下当前页的页码
PageSize = PageSize > 0 ? PageSize : 1;
int nPageCount = RecordCount % PageSize == 0 ? RecordCount / PageSize : (RecordCount / PageSize) + 1;
CurPage = CurPage > nPageCount ? nPageCount : CurPage;
CurPage = CurPage > 0 ? CurPage : 1;
int startRow = (CurPage - 1) * PageSize;
da.Fill(ds,startRow, PageSize, "table");
}
return ds;
}
3.初次封装简化参数:
尝试再次改进,方法二中代码看起来已经不那么乱了,但是需要提前设定多个分页参数,调用多了修改起来就比较麻烦,而且很明显的缺点适用性不好,如果我们将分页的具体操作封装就会简化许多,看封装后的调用
private void Bind()
{
SQLHelper db = new SQLHelper(g_ConnStr);
db.Open();
PageHelper pager = new PageHelper();
pager.CurrPage = Utils.ToInt(Utils .Get("page"));
int nRecord= 0;
string strsql = "SELECT * FROM [DCMS_Article] where channel_id=16 order by art_date desc" ;
DataSet dt = db.GetPagedDataSet(strsql, pager.CurrPage, pager.PageSize, out nRecord);
rpt_list_news.DataSource = dt.Tables[0];
rpt_list_news.DataBind();
pager.RecordCount = nRecord;
lblPageLinks.Text = pager.GetPageLinks();
db.Close();
}
清晰了不少,PageHelper类提供了分页的必须参数,参数设定了默认值,另外将 Utils.GetPageLinks() 函数封装在里面内部调用默认参数,也避免参数手动输入,PageHelper 每个人都有不同的实现,大抵都类似
4.
PageHelper返回
分页数据源:这个PageHelper根据查询结果重新分页,跟第一次的分页方式相同,PageHelper增加了GetPagedDataSource函数
private void Bind()
{
SQLHelper db = new SQLHelper(g_ConnStr);
db.Open();
string strsql = "SELECT * FROM [DCMS_Article] where channel_id=16 order by art_date desc" ;
DataSet dt = db.ExecuteDataSet(strsql);
PageHelper pager = new PageHelper();
pager.CurrPage = Utils.ToInt(Utils.Get("page"));
rpt_list_news.DataSource = pager.GetPagedDataSource(dt);
rpt_list_news.DataBind();
lblPageLinks.Text = pager.GetPageLinks();
db.Close();
}
public object GetPagedDataSource(DataSet dsSrc)
{
object retDataSource = new object();
if (dsSrc.Tables[0].Rows.Count != 0)
{
System.Web.UI.WebControls.PagedDataSource fenye = new System.Web.UI.WebControls.PagedDataSource();
fenye.AllowPaging = true;
fenye.DataSource = dsSrc.Tables[0].DefaultView;
fenye.PageSize = this.PageSize;
this._RecordCount = dsSrc.Tables[0].Rows.Count;
this._PageCount = fenye.PageCount;
_CurrPage = _CurrPage == 0 ? 1 : _CurrPage;
_CurrPage = _CurrPage > _PageCount ? _PageCount : _CurrPage;
fenye.CurrentPageIndex = _CurrPage - 1;
retDataSource = fenye;
}
return retDataSource;
}
这次倒是感觉跟平常的调用没啥区别了,就是多次查询让人很不爽,其次数据多了也增加开销,这里暂时先不尝试存储过程分页,再试试看能改进到什么程度
5.综合一下
数据源的获取放在PageHelper类中不是很好还是放在DBHeler中,其实我还是比较喜欢 SqlDataAdapter 来分页, PagedDataSource 分页则必须先查询一次再分页,奈何无论采取哪种方式获取记录总数总是个问题
private void Bind()
{
SQLHelper db = new SQLHelper(g_ConnStr);
db.Open();
string strsql = "SELECT * FROM [DCMS_Article] where channel_id=16 order by art_date desc" ;
PageHelper pager = new PageHelper();
pager.CurrPage = Utils.ToInt(Utils.Get("page"));
rpt_list_news.DataSource = db.GetPagedDataSource(strsql, ref pager);
rpt_list_news.DataBind();
lblPageLinks.Text = pager.GetPageLinks();
db.Close();
}
调用算是比较简单了,要是能解决查询记录总数的问题,那就好多了,下面是DBHelper中的函数实现
public object GetPagedDataSource(string strsql, int CurrPage, int PageSize, out int RecordCount)
{
object retDataSource = new object();
DataSet dsSrc = ExecuteDataSet(strsql);
RecordCount = 0;
int PageCount = 0;
if (dsSrc.Tables[0].Rows.Count != 0)
{
System.Web.UI.WebControls. PagedDataSource fenye = new System.Web.UI.WebControls.PagedDataSource ();
fenye.AllowPaging = true;
fenye.DataSource = dsSrc.Tables[0].DefaultView;
fenye.PageSize = PageSize;
RecordCount = dsSrc.Tables[0].Rows.Count;
PageCount = fenye.PageCount;
CurrPage = CurrPage == 0 ? 1 : CurrPage;
CurrPage = CurrPage > PageCount ? CurrPage : CurrPage;
fenye.CurrentPageIndex = CurrPage - 1;
retDataSource = fenye;
}
return retDataSource;
}
//这个简化了参数调用
public object GetPagedDataSource(string strsql, ref PageHelper pager )
{
int nRecord = 0;
object retobj= GetPagedDataSource(strsql, pager.CurrPage, pager.PageSize, out nRecord);
pager.RecordCount= nRecord;
return retobj;
}
