[.NET] SQL数据分页查询

程序下载

范例下载：

原始码下载：

NuGet封装：

数据查询

开发系统时，使用C#执行SQL查询指令，就可以从SQL数据库里查询所需数据。

SELECT Id, Name FROM Users

数据分页查询

当数据量过多时，系统会需要采用分页的方式来分批取得数据。这时可以改写原有的SQL查询指令，在其中加入ROW_NUMBER()，来为每笔资料打上编号。后续依照系统需求，取得某个编号范围内的数据，就完成在系统中提供数据分页查询的功能。(MS SQL 2012之后的SQL版本，改用OFFSET - FETCH会更简洁。)

SELECT * FROM   (            SELECT ROW_NUMBER() OVER(ORDER BY Id ASC) __RowNumber,                     Id, Name             FROM   Users        ) __RowNumberTableWHERE  __RowNumberTable.__RowNumber BETWEEN @__StartRowNumber AND @__EndRowNumber

提取为共享方法

上述这个改写SQL查询指令的动作，是很机械化的固定动作，透过抽取其中的动作流程，可以建立一个共享方法：GetLimitText方法。透过这个GetLimitText方法，开发人员传入查询的SQL查询指令，GetLimitText方法就会改写这个SQL查询指令，回传一个提供数据分页查询的SQL分页查询指令。开发人员使用C#来执行这个SQL分页查询指令，就能在系统中提供数据分页查询的功能。

using (SqlCommand command = new SqlCommand()){    // Connection    command.Connection = connection;    // CommandParameters    command.Parameters.Add(new SqlParameter("@__StartRowNumber", index + 1));    command.Parameters.Add(new SqlParameter("@__EndRowNumber", index + count));    // CommandText    command.CommandText = @"SELECT Id, Name FROM Users";    // LimitText    command.CommandText = SqlCommandExtensions.GetLimitText(command.CommandText, "Id ASC");    // Create    using (SqlDataReader reader = command.ExecuteReader())    {        dataTable.Load(reader);    }}

public static string GetLimitText(string commandText, string orderbyText){    #region Contracts    if (string.IsNullOrEmpty(commandText) == true) throw new ArgumentNullException();    if (string.IsNullOrEmpty(orderbyText) == true) throw new ArgumentNullException();    #endregion    // Remove "SELECT"    commandText = commandText.Trim().Remove(0, 6);    // LimitText    var limitText = @"SELECT *                         FROM   (                                SELECT ROW_NUMBER() OVER(ORDER BY {0}) __RowNumber, {1}                                ) __RowNumberTable                        WHERE  __RowNumberTable.__RowNumber BETWEEN @__StartRowNumber AND @__EndRowNumber";    limitText = string.Format(limitText, orderbyText, commandText);    // Return    return limitText;}

封装为扩充方法

为了更方便开发人员使用GetLimitText方法，可以将这个方法近一步封装成为SqlCommand类别的扩充方法：ExecuteReader方法，让数据分页查询功能伪装成为SqlCommand类别的方法。后续开发人员只要建立SQL查询指令，并且执行ExecuteReader方法，就能够很快速的在系统中提供数据分页查询的功能。

using (SqlCommand command = new SqlCommand()){    // Connection    command.Connection = connection;    // CommandText    command.CommandText = @"SELECT Id, Name FROM Users";    // Create    using (SqlDataReader reader = command.ExecuteReader(index, count, "Id ASC"))    {        dataTable.Load(reader);    }}

public static SqlDataReader ExecuteReader(this SqlCommand command, int index, int count, string orderbyText){    #region Contracts    if (command == null) throw new ArgumentNullException();    if (string.IsNullOrEmpty(orderbyText) == true) throw new ArgumentNullException();    #endregion    // CommandParameters    command.Parameters.Add(new SqlParameter("@__StartRowNumber", index + 1));    command.Parameters.Add(new SqlParameter("@__EndRowNumber", index + count));    // LimitText    var limitText = GetLimitText(command.CommandText, orderbyText);    // ExecuteReader    var commandText = command.CommandText;    try    {        // Set        command.CommandText = limitText;        // Execute        return command.ExecuteReader();    }    finally    {        // Reset        command.CommandText = commandText;    }}