[.NET] SQL數(shù)據(jù)分頁(yè)查詢

[.NET] SQL數(shù)據(jù)分頁(yè)查詢

程序下載

范例下載：點(diǎn)此下載

原始碼下載：點(diǎn)此下載

NuGet封裝：點(diǎn)此下載

數(shù)據(jù)查詢

開發(fā)系統(tǒng)時(shí)，使用C#執(zhí)行SQL查詢指令，就可以從SQL數(shù)據(jù)庫(kù)里查詢所需數(shù)據(jù)。

SELECT Id, Name FROM Users

數(shù)據(jù)分頁(yè)查詢

當(dāng)數(shù)據(jù)量過(guò)多時(shí)，系統(tǒng)會(huì)需要采用分頁(yè)的方式來(lái)分批取得數(shù)據(jù)。這時(shí)可以改寫原有的SQL查詢指令，在其中加入ROW_NUMBER()，來(lái)為每筆資料打上編號(hào)。后續(xù)依照系統(tǒng)需求，取得某個(gè)編號(hào)范圍內(nèi)的數(shù)據(jù)，就完成在系統(tǒng)中提供數(shù)據(jù)分頁(yè)查詢的功能。(MS SQL 2012之后的SQL版本，改用OFFSET - FETCH會(huì)更簡(jiǎn)潔。)

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

提取為共享方法

上述這個(gè)改寫SQL查詢指令的動(dòng)作，是很機(jī)械化的固定動(dòng)作，透過(guò)抽取其中的動(dòng)作流程，可以建立一個(gè)共享方法：GetLimitText方法。透過(guò)這個(gè)GetLimitText方法，開發(fā)人員傳入查詢的SQL查詢指令，GetLimitText方法就會(huì)改寫這個(gè)SQL查詢指令，回傳一個(gè)提供數(shù)據(jù)分頁(yè)查詢的SQL分頁(yè)查詢指令。開發(fā)人員使用C#來(lái)執(zhí)行這個(gè)SQL分頁(yè)查詢指令，就能在系統(tǒng)中提供數(shù)據(jù)分頁(yè)查詢的功能。

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;}

封裝為擴(kuò)充方法

為了更方便開發(fā)人員使用GetLimitText方法，可以將這個(gè)方法近一步封裝成為SqlCommand類別的擴(kuò)充方法：ExecuteReader方法，讓數(shù)據(jù)分頁(yè)查詢功能偽裝成為SqlCommand類別的方法。后續(xù)開發(fā)人員只要建立SQL查詢指令，并且執(zhí)行ExecuteReader方法，就能夠很快速的在系統(tǒng)中提供數(shù)據(jù)分頁(yè)查詢的功能。

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;    }}