asp.net aspnetpager分页统计时与实际不符的解决办法
程序员文章站
2023-01-23 23:12:45
基本函数如下:复制代码 代码如下:/// /// 需要分页时使用,根据参数和conditionexpress获取datatable ///...
基本函数如下:
/// <summary>
/// 需要分页时使用,根据参数和conditionexpress获取datatable
/// </summary>
/// <param name="_tablename">表名</param>
/// <param name="_fieldnames">字段名集合,用逗号分开</param>
/// <param name="_ordercolumn">排序字段,用于统计有多少条记录</param>
/// <param name="isdesc">是否倒序</param>
/// <param name="_indexcolumn">自增字段名</param>
/// <param name="_currentpage">当前页</param>
/// <param name="pagesize">页大小</param>
/// <param name="_rowscount">总记录数</param>
/// <returns>获取到的datatable</returns>
public static datatable getdatatable(string _tablename, string _fieldnames, string _ordercolumn, bool isdesc, string _indexcolumn, int _currentpage, int pagesize, string conditionexpress, ref int _rowscount)
{
using (sqlconnection conn = new sqlconnection(connectionstring))
{
string wherestr = " where 1=1 ";
string sort = isdesc ? " desc" : " asc";
string sqlstr = " from " + _tablename;
//排序字段
string orderstr = " order by " + _ordercolumn + sort;
if (_ordercolumn != _indexcolumn)
orderstr += "," + _indexcolumn + sort;
if (conditionexpress != string.empty)
{
wherestr += conditionexpress;
}
sqlstr += wherestr;
//取得符合条件的数据总数
sqlcommand cmd = new sqlcommand("select count(" + _ordercolumn + ") " + sqlstr, conn);
conn.open();
try
{
_rowscount = (int)cmd.executescalar();
}
catch (exception ex)
{
throw new exception(ex.message);
}
if (_currentpage > _rowscount) _currentpage = _rowscount;
if (_currentpage > 1)
{
if (isdesc)
sqlstr += " and " + _ordercolumn + " < (select min(" + _ordercolumn + ") from ";
else
sqlstr += " and " + _ordercolumn + " > (select max(" + _ordercolumn + ") from ";
sqlstr += "(select top " + (pagesize * (_currentpage - 1)) + " " + _ordercolumn + " from " + _tablename + wherestr + orderstr + ") as t)";
}
sqlstr = "select top " + pagesize + " " + _fieldnames + sqlstr + orderstr;
try
{
dataset ds = new dataset();
sqldataadapter da = new sqldataadapter(sqlstr, conn);
da.fill(ds);
return ds.tables[0];
}
catch (exception ex)
{
throw new exception(ex.message);
}
}
}
调用如下:
private void bind()
{
int rowcount = 1;
string wherestr = string.empty;
//设置分页
anpager.alwaysshow = true;
anpager.pagesize = 10;
this.rptdictionary.datasource = getdatatable(
"dictionary_toysgogo_",
"[id_dictionary_],[namecn_dictionary_],[nameen_dictionary_],[point_dictionary_]",
"[id_dictionary_]",
true,
"[id_dictionary_]",
this.anpager.currentpageindex,
anpager.pagesize,
wherestr,
ref rowcount
);
this.anpager.recordcount = rowcount;
this.rptdictionary.databind();
}
//分页切换
protected void anpager_pagechanging(object src, wuqi.webdiyer.pagechangingeventargs e)
{
this.anpager.currentpageindex = e.newpageindex;
this.tbxtype.text = this.tbxtype.text;
bind();
}
之前一直在页数方面直接用数字写进去,没有写成anpager.pagesize=10;的形式,在老汤的提醒下,做了修改,也解决了一直困扰我的问题。
复制代码 代码如下:
/// <summary>
/// 需要分页时使用,根据参数和conditionexpress获取datatable
/// </summary>
/// <param name="_tablename">表名</param>
/// <param name="_fieldnames">字段名集合,用逗号分开</param>
/// <param name="_ordercolumn">排序字段,用于统计有多少条记录</param>
/// <param name="isdesc">是否倒序</param>
/// <param name="_indexcolumn">自增字段名</param>
/// <param name="_currentpage">当前页</param>
/// <param name="pagesize">页大小</param>
/// <param name="_rowscount">总记录数</param>
/// <returns>获取到的datatable</returns>
public static datatable getdatatable(string _tablename, string _fieldnames, string _ordercolumn, bool isdesc, string _indexcolumn, int _currentpage, int pagesize, string conditionexpress, ref int _rowscount)
{
using (sqlconnection conn = new sqlconnection(connectionstring))
{
string wherestr = " where 1=1 ";
string sort = isdesc ? " desc" : " asc";
string sqlstr = " from " + _tablename;
//排序字段
string orderstr = " order by " + _ordercolumn + sort;
if (_ordercolumn != _indexcolumn)
orderstr += "," + _indexcolumn + sort;
if (conditionexpress != string.empty)
{
wherestr += conditionexpress;
}
sqlstr += wherestr;
//取得符合条件的数据总数
sqlcommand cmd = new sqlcommand("select count(" + _ordercolumn + ") " + sqlstr, conn);
conn.open();
try
{
_rowscount = (int)cmd.executescalar();
}
catch (exception ex)
{
throw new exception(ex.message);
}
if (_currentpage > _rowscount) _currentpage = _rowscount;
if (_currentpage > 1)
{
if (isdesc)
sqlstr += " and " + _ordercolumn + " < (select min(" + _ordercolumn + ") from ";
else
sqlstr += " and " + _ordercolumn + " > (select max(" + _ordercolumn + ") from ";
sqlstr += "(select top " + (pagesize * (_currentpage - 1)) + " " + _ordercolumn + " from " + _tablename + wherestr + orderstr + ") as t)";
}
sqlstr = "select top " + pagesize + " " + _fieldnames + sqlstr + orderstr;
try
{
dataset ds = new dataset();
sqldataadapter da = new sqldataadapter(sqlstr, conn);
da.fill(ds);
return ds.tables[0];
}
catch (exception ex)
{
throw new exception(ex.message);
}
}
}
调用如下:
复制代码 代码如下:
private void bind()
{
int rowcount = 1;
string wherestr = string.empty;
//设置分页
anpager.alwaysshow = true;
anpager.pagesize = 10;
this.rptdictionary.datasource = getdatatable(
"dictionary_toysgogo_",
"[id_dictionary_],[namecn_dictionary_],[nameen_dictionary_],[point_dictionary_]",
"[id_dictionary_]",
true,
"[id_dictionary_]",
this.anpager.currentpageindex,
anpager.pagesize,
wherestr,
ref rowcount
);
this.anpager.recordcount = rowcount;
this.rptdictionary.databind();
}
复制代码 代码如下:
//分页切换
protected void anpager_pagechanging(object src, wuqi.webdiyer.pagechangingeventargs e)
{
this.anpager.currentpageindex = e.newpageindex;
this.tbxtype.text = this.tbxtype.text;
bind();
}
之前一直在页数方面直接用数字写进去,没有写成anpager.pagesize=10;的形式,在老汤的提醒下,做了修改,也解决了一直困扰我的问题。
下一篇: asp.net 购物车实现详细代码