[. NET] query the total number of SQL data records

[. NET] download the SQL data query Program

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

Query the total number of data records

When the amount of data is too large, the system will need to retrieve data in batches by page, but before that, we need to obtain the total number of data records to inform the System of the total amount of data waiting for processing. In this case, you can rewrite the original SQL query command and add COUNT (*) to it to calculate the total number of query results. (Rewrite the query command as a subquery to obtain the total number of data records, which is designed on the premise of convenient rewriting .)

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

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: GetQueryCountText. Through the GetQueryCountText method, the developer passes in the SQL query command for the query. The GetQueryCountText method will rewrite the SQL query command and return a SQL query command that provides the total number of data records. Developers can use C # to execute this SQL query command to query the total number of data records 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;}

Encapsulated as an extension method

To make it easier for developers to use the GetQueryCountText method, you can encapsulate this method into an extension method of the SqlCommand category: ExecuteQueryCount method, so that the query function of the total number of data records is disguised as a method of the SqlCommand category. In the future, developers only need to create SQL query commands and execute the ExecuteQueryCount method to quickly provide the function of querying the total number of data records 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;    }}