[笔记]几个常用的分页查询

方法①，在DataTable内分页（不推荐）:

（优点：实现方法简单；缺点：浪费内存...）

/// <summary>
/// DataTable分页查询
/// </summary>
/// <param name="data">DataTable</param>
/// <param name="allPage">一共有几页</param>
/// <param name="PageSize">每页几行数据</param>
/// <param name="PageIndex">第几页</param>
/// <returns></returns>
public static DataTable GetDataTablePage(DataTable data, out int allPage, 
    int PageSize = 500, int PageIndex = 1)
{
    allPage = data.Rows.Count / PageSize;
    allPage += data.Rows.Count % PageSize == 0 ? 0 : 1;

    DataTable dt = data.Clone();
    int startIndex = PageIndex * PageSize;
    int endIndex = startIndex + PageSize > data.Rows.Count ? data.Rows.Count : startIndex + PageSize;

    if (startIndex < endIndex)
    {
        for (int i = startIndex; i < endIndex; i++)
        {
            dt.ImportRow(data.Rows[i]);
        }
    }

    return dt;
}

简单调用:

DataTable dt = bll.GetData();//先从数据库查询出所有的数据(未分页)

int allPage = 1;//分页数
int PageSize = 500;//每页500条数据
int PageIndex = 1;//当前第1页

DataTable dtPage = GetDataTablePage(dt,out allPage,PageSize,PageIndex);
return dtPage;//分页后的(500条)数据

说明：先取出所有数据的DataTable，然后在这个DataTable里面挑选出你需要的那几行数据...

数据量小的话还可以用用...

---

方法②，在SQL里面分页（新老版本SQL均适用）:

（优点：实现思路简单；缺点：总数据多的话，查询慢...）

/// <summary>
/// SQL分页查询
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="fldName">显示字段</param>
/// <param name="strWhere">where条件</param>
/// <param name="orderBy">排序</param>
/// <param name="PageSize">每页多少数据</param>
/// <param name="PageIndex">当前第几页</param>
/// <returns></returns>
public static DataTable PAGING(string TableName, string fldName, string strWhere, string orderBy, 
    int PageSize = 500, int PageIndex = 1)
{
    StringBuilder strSql = new StringBuilder();
    strSql.AppendLine("DECLARE @PageSize INT;");
    strSql.AppendLine("DECLARE @PageIndex INT;");
    strSql.AppendLine("SET @PageSize=" + PageSize + ";");
    strSql.AppendLine("SET @PageIndex=" + PageIndex + ";");

    strSql.Append("SELECT ");
    if (!string.IsNullOrEmpty(fldName))
    {
        strSql.Append(fldName + " FROM (");
    }
    else
    {
        strSql.Append(" * FROM (");
    }
    strSql.AppendFormat(" SELECT ROW_NUMBER() OVER(ORDER BY {0}) rownum,", orderBy);
    if (!string.IsNullOrEmpty(fldName))
    {
        strSql.AppendFormat(fldName + " FROM {0}", TableName);
    }
    else
    {
        strSql.AppendFormat(" * FROM {0}", TableName);
    }
    if (!string.IsNullOrEmpty(strWhere))
    {
        strSql.AppendLine(" WHERE " + strWhere);
    }
    strSql.AppendLine(" )a");
    strSql.AppendLine(" WHERE rownum > @PageSize * (@PageIndex - 1) AND rownum <= @PageSize * @PageIndex ");
    if (!string.IsNullOrEmpty(orderBy))
    {
        strSql.Append(" ORDER BY " + orderBy);
    }

    DataTable dt = ExecuteReader(strSql.ToString());
    return dt;
}

简单调用：

string where = " 1=1 ";
if (!string.IsNullOrWhiteSpace(startDate))
{
    where += " and [Month]='" + startDate + "'";
}

string orderBy = " UpdateTime desc,JF_MonthReportID desc ";

DataTable dt = SqlHelper.PAGING("[t_MonthReport]", "*", where, orderBy, pageSize, pageIndex);

return dt;

 思路：这次是在SQL里面，先取出所有（未分页）的数据，然后再挑选出你要的那几行（分页的）数据...

方法②，适用于新老版本的SqlServer(sqlserver2005以上版本才有ROW_NUMBER()函数)

大致拼接好的SQL语句如下：

select a.* from (
    select *
    ,ROW_NUMBER() over(order by UpdateTime desc,JF_MonthReportID desc) num 
    from [t_MonthReport] 
    where 1=1 
    and [Month]='2022-02'
) a
where a.num>=1 and a.num<=500

---

方法③，使用offset size rows fetch next size rows only语法分页（推荐）:

(优点：写法及性能明显优于方法②；缺点：只适用于SqlServer2012以上版本)

/// <summary>
/// SQL分页查询 
/// 该语法支持SQL Server2012及以上版本
/// </summary>
/// <param name="TableName">表名(必填)</param>
/// <param name="fldName">显示字段(缺省为:*)</param>
/// <param name="strWhere">查询条件</param>
/// <param name="orderBy">排序条件</param>
/// <param name="PageSize">每页行数</param>
/// <param name="PageIndex">第几页</param>
/// <returns></returns>
public static DataTable PAGING_2012(string TableName, string fldName, string strWhere, string orderBy = "", 
    int PageSize = 500, int PageIndex = 1)
{
    StringBuilder strSql = new StringBuilder();
    strSql.Append("SELECT ");
    if (!string.IsNullOrWhiteSpace(fldName))
    {
        strSql.AppendFormat(fldName + " FROM {0}", TableName);//指定字段
    }
    else
    {
        strSql.AppendFormat(" * FROM {0}", TableName);//全部字段
    }
    if (!string.IsNullOrWhiteSpace(strWhere))
    {
        strSql.Append(" WHERE " + strWhere);
    }
    if (!string.IsNullOrWhiteSpace(orderBy))
    {
        strSql.AppendFormat(" order by {0} offset {1} rows fetch next {2} rows only ", orderBy, (PageIndex - 1) * PageSize, PageSize);
    }
    else
    {
        strSql.AppendFormat(" order by 1 offset {0} rows fetch next {1} rows only ", (PageIndex - 1) * PageSize, PageSize);//默认以第1个字段排序
    }
    //解释一下：offset N rows 跳过前N条，fetch next M rows only 获取下面的M条信息。
    DataTable dt = ExecuteReader(strSql.ToString());
    return dt;
}
/// <summary>
/// SQL分页查询 
/// 该语法支持SQL Server2012及以上版本
/// </summary>
/// <param name="sqlStr">sql语句</param>
/// <param name="orderBy">排序</param>
/// <param name="PageSize">每页行数</param>
/// <param name="PageIndex">第几页</param>
/// <returns></returns>
public static DataTable PAGING_2012(string sqlStr, string orderBy = "", int PageSize = 500, int PageIndex = 1)
{
    StringBuilder strSql = new StringBuilder();
    strSql.Append(sqlStr);
    if (!string.IsNullOrWhiteSpace(orderBy))
    {
        strSql.AppendFormat(" order by {0} offset {1} rows fetch next {2} rows only ", orderBy, (PageIndex - 1) * PageSize, PageSize);
    }
    else
    {
        strSql.AppendFormat(" order by 1 offset {0} rows fetch next {1} rows only ", (PageIndex - 1) * PageSize, PageSize);//默认以第1个字段排序
    }
    //解释一下：offset N rows 跳过前N条，fetch next M rows only 获取下面的M条信息。
    DataTable dt = ExecuteReader(strSql.ToString());
    return dt;
}

 简单调用：

string where = " 1=1 ";
if (!string.IsNullOrWhiteSpace(startDate))
{
    where += " and [Month]='" + startDate + "'";
}

string orderBy = " UpdateTime desc,JF_MonthReportID desc ";
DataTable dt = SqlHelper.PAGING_2012("[t_MonthReport]", "*", where, orderBy, pageSize, pageIndex);
return dt;

大致拼接好的SQL语句如下：

SELECT * FROM [t_MonthReport] WHERE 1=1 and [Month]='2022-02'
 order by  UpdateTime desc,JF_MonthReportID desc  offset 0 rows fetch next 500 rows only 

此法不用先取出所有的数据了，直接取出你要的那几行分页数据...