不用存储实现的分页,效率和存储过程一样
来源:互联网 发布:怎样才能在淘宝上开店 编辑:程序博客网 时间:2024/05/22 02:26
/// <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();
- 不用存储实现的分页,效率和存储过程一样
- 不用存储过程的分页实现
- 不用存储过程一样参数传递
- 实现分页的存储过程
- Repeater控件不用存储过程实现分页,DataList同理
- 存储过程实现分页
- 存储过程实现分页
- 存储过程实现分页
- 存储过程实现分页
- C#高效分页代码(不用存储过程)
- 改进的效率较高的sql2000分页存储过程
- 使用系统存储过程实现的通用分页存储过程
- 使用系统存储过程实现的通用分页存储过程
- 分页存储过程(对有主键的表效率极高)
- 一个效率比较高的分页存储过程(SQL)
- 关于sql 几种分页查询效率的存储过程
- 大数据量分页存储过程效率测试
- 大数据量分页存储过程效率测试
- 用select... for update注意事项
- Watir 语法(Web Application Testing in Ruby)
- JavaScript 判断浏览器类型及版本
- 从SQL语言的分类谈COMMIT和ROLLBACK的用法
- word2CHM,做软件产品必备的说明书工具
- 不用存储实现的分页,效率和存储过程一样
- IIS安全配置 - 用IIS建立高安全性Web服务器
- mysql 连接超时解决方法
- hibernate学习笔记1(javax.persistence.Cacheable)
- 异步操作(二)
- POJ_2531_dfs
- JavaScript层移动关键代码
- Apple Tree(动态规划)
- 在9530手机上面,如何控制程序只能纵向显示?如何判断当前的屏幕横纵方向呢?