分页的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;
}