/// <summary>
/// 获得分页操作sql语句(对于排序的字段必须建立索引)
/// </summary>
/// <param name="tblname">操作表名</param>
/// <param name="fldname">操作索引字段名称</param>
/// <param name="pageindex">当前页</param>
/// <param name="pagesize">每页显示记录数</param>
/// <param name="rtnfields">返回字段集合,中间用逗号格开。返回全部用“*”</param>
/// <param name="ordertype">排序方式,0升序,1为降序</param>
/// <param name="strwhere">检索的条件语句,不需要再加where要害字</param>
/// <returns></returns>
public static string constructsplitsql(string tblname,
string fldname,
int pageindex,
int pagesize,
string rtnfields,
int ordertype,
string strwhere)
{
string strsql = "";
string stroldwhere = "";
// 构造检索条件语句字符串
if( strwhere != "" )
{
stroldwhere = " and " + strwhere + " ";
strwhere = " where " + strwhere + " ";
// 去除不合法的字符,防止sql注入式攻击
strwhere = strwhere.replace("'", "''");
strwhere = strwhere.replace("--", "");
strwhere = strwhere.replace(";", "");
}
// 升序操作
if( ordertype == 0 )
{
if( pageindex == 1 )
{
strsql += "select top " + pagesize + " " + rtnfields + " from " + tblname + " ";
strsql += strwhere + "order by " + fldname + " asc";
}
else
{
strsql += "select top " + pagesize + " " + rtnfields + " from " + tblname + " ";
strsql += "where (" + fldname + " > ( select max(" + fldname + ") from (select top " + ((pageindex - 1)*pagesize) + " " + fldname + " from " + tblname + strwhere + " order by " + fldname + " asc ) as t )) ";
strsql += stroldwhere + "order by " + fldname + " asc";
}
}
// 降序操作
else if( ordertype == 1 )
{
if( pageindex == 1 )
{
strsql += "select top " + pagesize + " " + rtnfields + " from " + tblname + " ";
strsql += strwhere + "order by " + fldname + " desc";
}
else
{
strsql += "select top " + pagesize + " " + rtnfields + " from " + tblname + " ";
strsql += "where (" + fldname + " < ( select min(" + fldname + ") from (select top " + ((pageindex - 1)*pagesize) + " " + fldname + " from " + tblname + strwhere + " order by " + fldname + " desc ) as t )) ";
strsql += stroldwhere + "order by " + fldname + " desc";
}
}
else // 异常处理
{
throw new dataexception("未指定任何排序类型。0升序,1为降序");
}
return strsql;
}
调用代码:cs文件的,参考。。
dataprovider dp = null;
user.datatype = "sqlclient";
user.connectionstring = "server=(local); uid=sa; pwd=1016; database=skyboard";
dp = user.instancedataprovider();
string strcmd = "select count(id) from [address]";
int totalrecord = 1198954;
int pagesize = 20;
int pageindex = (request.querystring["page"] == null) ? 1 : int.parse(request.querystring["page"]);
int pagenum = 0;
if(totalrecord % pagesize == 0)
{
pagenum = totalrecord/pagesize;
}
else
{
pagenum = (totalrecord/pagesize) + 1;
}
//strcmd = string.format(seaskyer.fso.fobject.readfile(@"e://a.txt"), pagesize * (pageindex - 1) + 1);
strcmd = seaskyer.strings.function.constructsplitsql("address", "id", pageindex, pagesize, "*", 1, "");
response.write(strcmd);
datatable dt = dp.datatablesql(strcmd);
response.write("共有" + totalrecord + ", " + user.splitpages("test.aspx?", pageindex, pagenum, totalrecord ));
datagrid1.datasource = dt.defaultview;
datagrid1.databind();
dt.clear();
dt.dispose();
不用存储实现的分页,效率和存储过程一样
最新推荐文章于 2010-12-16 11:54:00 发布