[.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
        ) __RowNumberTable
WHERE  __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;
    }
}