SqlServer分页方法
时间：2021-07-01 10:21:17
帮助过：3人阅读
<summary>
/// 使用虚拟表进行分页查询,不适用明确知道列名的查询
/// </summary>
/// <param name="sql">sql 如"select * from name where 1=1"</param>
/// <param name="pageIndex">页码 如"1"</param>
/// <param name="pageSize">条数 如"100"</param>
/// <returns>DataTable</returns>
public static DataTable SelectPaging(String sql,
int pageIndex,
int pageSize)
{
//定义虚拟表名称
string temporaryTable =
"#" +
randString();
//创建您虚拟表
string sqlPaging =
"select identity(int) as ZZZZZ, * into " + temporaryTable +
" from (select TOP 100 Percent * from ( " + sql +
") a) a ";
//使用虚拟表进行分页查询
sqlPaging +=
"select * from " + temporaryTable +
" a WHERE 1=1 "
+
"AND a.ZZZZZ >= " + pageIndex * pageSize +
" AND a.ZZZZZ <= " + ((pageIndex +
1) * pageSize -
1) +
" ";
//删除虚拟表
sqlPaging +=
"DROP TABLE " +
temporaryTable;
//执行Sql语句 SelectCustomSql为执行sql的方法需自己定义
DataTable dt =
SelectCustomSql(sqlPaging);
//判断返回结果是否为空
if (dt !=
null)
{
//移除标识列
dt.Columns.Remove(
"ZZZZZ");
}
//返回结果
return dt;
}
其他分页方法百度上比较多。
SqlServer分页方法
标签：
