[. NET] Total number of SQL data queries

[. NET] Total number of SQL data query program download

Sample download: Click here to download

Download the original code: Click here to download

NuGet Package: Click here to download

Data query

When developing the system, you can query the required data from the SQL database by executing SQL query instructions in C #.

SELECT Id, Name FROM Users

Total number of data queries

When the amount of data is too large, the system will need to use paging to obtain the data in batches, but before this need to obtain the total number of data, to tell the system how much data waiting to be processed. You can then overwrite the original SQL query instruction, adding count (*) to calculate the total number of results for the query. (the query instruction is rewritten as a subquery to obtain the total number of data, is designed to facilitate rewriting as a precondition.) )

SELECT COUNT(*) FROM   (         SELECT Id, Name FROM Users       ) __QueryCountTable

Extract as Shared method

The above-mentioned action of rewriting SQL query instruction, is a very mechanized fixed action, through extracting the action flow, can establish a sharing method: Getquerycounttext method. Through this getquerycounttext method, the developer passes the SQL query instruction of the query, the Getquerycounttext method will rewrite the SQL query instruction, and return a SQL Total number query instruction that provides the data of the total number of bytes. The developer uses C # to execute this SQL total number of query instructions, which provides the ability to query the total number of data in the system.

using (SqlCommand command = new SqlCommand()){    // Connection    command.Connection = connection;    // CommandText    command.CommandText = @"SELECT Id, Name FROM Users";    // QueryCountText    command.CommandText = SqlCommandExtensions.GetQueryCountText(command.CommandText);    // Execute    count = Convert.ToInt32(command.ExecuteScalar());}

public static string GetQueryCountText(string commandText){    #region Contracts    if (string.IsNullOrEmpty(commandText) == true) throw new ArgumentNullException();    #endregion    // QueryCountText    var queryCountText = @"SELECT COUNT(*)                            FROM  (                                    {0}                                  ) __QueryCountTable";    queryCountText = string.Format(queryCountText, commandText);    // Return    return queryCountText;}

Encapsulation as an extension method

In order to make it easier for developers to use the Getquerycounttext method, this approach can be encapsulated as an extension of the SqlCommand class in one step: Executequerycount method, The method of making the total number of data query function disguised as the SqlCommand category. As soon as the following developers build SQL query instructions and execute the Executequerycount method, they can quickly provide the function of the total number of data queries in the system.

using (SqlCommand command = new SqlCommand()){    // Connection    command.Connection = connection;    // CommandText    command.CommandText = @"SELECT Id, Name FROM Users";    // Execute    count = command.ExecuteQueryCount();}

public static int ExecuteQueryCount(this SqlCommand command){    #region Contracts    if (command == null) throw new ArgumentNullException();    #endregion    // QueryCountText    var queryCountText = GetQueryCountText(command.CommandText);    // ExecuteQueryCount    var commandText = command.CommandText;    try    {        // Set        command.CommandText = queryCountText;        // Execute        return Convert.ToInt32(command.ExecuteScalar());    }    finally    {        // Reset        command.CommandText = commandText;    }}