分页SQL

最新推荐文章于 2024-05-11 08:00:00 发布

原创最新推荐文章于 2024-05-11 08:00:00 发布 · 163 阅读

0 ·

CC 4.0 BY-SA版权

其他专栏收录该内容

6 篇文章

订阅专栏

博客给出了一个分页的SQL方法，该方法只需请求一次。通过传入SQL语句、排序字段、排序方式、每页大小和页码等参数，利用StringBuilder构建分页查询的SQL语句，实现分页功能。

分页的SQL，这个方法就请求一次

public static StringBuilder SqlPageSql(string strSql, string orderField, bool isAsc, int pageSize, int pageIndex)
{
StringBuilder stringBuilder = new StringBuilder();
if (pageIndex <= 0)
pageIndex = 1;
string str = string.Empty;
if (string.IsNullOrEmpty(orderField) == false)
{
if (orderField.ToUpper().IndexOf("ASC") + orderField.ToUpper().IndexOf("DESC") > 0)
str = " order by " + orderField;
else
str = " order by " + orderField + " " + (isAsc ? "asc" : "desc");
}
else
str = "order by (select 0)";
stringBuilder.Append("select * From (select row_number() Over (" + str + ")");
stringBuilder.Append(string.Concat(new object[] { " as rowNum,count(1) over() as totalRecord, * from (", strSql, ") T ) as N where rowNum > ", ((pageIndex - 1) * pageSize), " and rowNum <= ", (pageIndex * pageSize) }));
return stringBuilder;
}