[. NET] querying SQL data by PAGE

[. NET] SQL data paging query program download

Download example:Click here to download

Download the original code:Click here to download

NuGet encapsulation:Click here to download

Data Query

When developing a system, you can use C # To execute the SQL query command to query the required data from the SQL database.

SELECT Id, Name FROM Users

Data paging Query

When the amount of data is too large, the system will use paging to obtain data in batches. In this case, you can rewrite the original SQL query command and add ROW_NUMBER () to it to add numbers for each piece of data. After obtaining data within a certain number range according to system requirements, the system will be able to provide the data paging query function. (For SQL Versions later than ms SQL 2012, using OFFSET-FETCH is simpler .)

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

Extracted as a sharing method

The preceding rewriting of the SQL query command is a mechanical and fixed action. by extracting the action flow, you can create a sharing method: GetLimitText. By using the GetLimitText method, the developer passes in the SQL query command to query. The GetLimitText method will rewrite the SQL query command and return an SQL paging query command that Provides paging query of data. Developers can use C # to execute this SQL paging query command to provide the data paging query function in the system.

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

Encapsulated as an extension method

To make it easier for developers to use the GetLimitText method, we can encapsulate this method as an extension method of the SqlCommand category: ExecuteReader method, so that the data paging query function can be disguised as a method of the SqlCommand category. In the future, developers only need to create SQL query commands and execute the ExecuteReader method to quickly provide the data paging query function in the system.

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