The C splicing SQL statement implements efficient paging sort with ROW_NUMBER

If you're going to use a database in your project, you're definitely going to use paging sort. Before doing database query optimization, I wrote the following code overnight to splice the SQL statement of paging sort

 
/// <summary> 
///  Single table (view) gets paging SQL statements  
/// </summary> 
/// <param name="tableName"> Table or view name </param> 
/// <param name="key"> only 1 key </param> 
/// <param name="fields"> Retrieved field </param> 
/// <param name="condition"> Query criteria (not included WHERE ) </param> 
/// <param name="collatingSequence"> Collation (not included ORDER BY ) </param> 
/// <param name="pageSize"> Page size </param> 
/// <param name="pageIndex"> The page number (from 1 Start) </param> 
/// <returns> paging SQL statements </returns> 
public static string GetPagingSQL( 
string tableName, 
string key, 
string fields, 
string condition, 
string collatingSequence, 
int pageSize, 
int pageIndex) 
{ 
string whereClause = string.Empty; 
if (!string.IsNullOrEmpty(condition)) 
{ 
whereClause = string.Format("WHERE {0}", condition); 
} 

if (string.IsNullOrEmpty(collatingSequence)) 
{ 
collatingSequence = string.Format("{0} ASC", key); 
} 

StringBuilder sbSql = new StringBuilder(); 

sbSql.AppendFormat("SELECT {0} ", PrependTableName(tableName, fields, ',')); 
sbSql.AppendFormat("FROM ( SELECT TOP {0} ", pageSize * pageIndex); 
sbSql.AppendFormat(" [_RowNum_] = ROW_NUMBER() OVER ( ORDER BY {0} ), ", collatingSequence); 
sbSql.AppendFormat(" {0} ", key); 
sbSql.AppendFormat(" FROM {0} ", tableName); 
sbSql.AppendFormat(" {0} ", whereClause); 
sbSql.AppendFormat(" ) AS [_TempTable_] "); 
sbSql.AppendFormat(" INNER JOIN {0} ON [_TempTable_].{1} = {0}.{1} ", tableName, key); 
sbSql.AppendFormat("WHERE [_RowNum_] > {0} ", pageSize * (pageIndex - 1)); 
sbSql.AppendFormat("ORDER BY [_TempTable_].[_RowNum_] ASC "); 

return sbSql.ToString(); 
} 

/// <summary> 
///  Prefix fields with table names  
/// </summary> 
/// <param name="tableName"> The name of the table </param> 
/// <param name="fields"> field </param> 
/// <param name="separator"> Identifies the separator between fields </param> 
/// <returns></returns> 
public static string PrependTableName(string tableName, string fields, char separator) 
{ 
StringBuilder sbFields = new StringBuilder(); 

string[] fieldArr = fields.Trim(separator).Split(separator); 
foreach (string str in fieldArr) 
{ 
sbFields.AppendFormat("{0}.{1}{2}", tableName, str.Trim(), separator); 
} 

return sbFields.ToString().TrimEnd(separator); 
} 

Suppose we have the following product list:

 
CREATE TABLE [dbo].[Tbl_Product] 
( 
[ID] [int] IDENTITY(1, 1) 
NOT NULL , 
[ProductId] [varchar](50) NOT NULL , 
[ProductName] [nvarchar](50) NOT NULL , 
[IsDeleted] [int] NOT NULL 
CONSTRAINT [DF_Tbl_Product_IsDeleted] DEFAULT ( (0) ) , 
CONSTRAINT [PK_Tbl_Product] PRIMARY KEY CLUSTERED ( [ProductId] ASC ) 
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] 
) 
ON [PRIMARY] 

Tbl_Product- > ID (serial number, non-empty, self-increment)
Tbl_Product- > ProductId (product Id, primary key)
Tbl_Product- > ProductName (product name, not null)
Tbl_Product- > IsDeleted (virtual delete tag, not null)
BasicFunction.GetPagingSQL ("Tbl_Product", "ID", "ID,ProductId,ProductName", "IsDeleted=0", "ProductName ASC, ID DESC", 5, 5), BasicFunction is the static class where the paging sort method is located. The generated paging sort SQL statement is as follows (the format has been manually adjusted) :

 
SELECT Tbl_Product.ID , 
Tbl_Product.ProductId , 
Tbl_Product.ProductName 
FROM ( SELECT TOP 25 
[_RowNum_] = ROW_NUMBER() OVER ( ORDER BY ProductName ASC, ID DESC ) , 
ID 
FROM Tbl_Product 
WHERE IsDeleted = 0 
) AS [_TempTable_] 
INNER JOIN Tbl_Product ON [_TempTable_].ID = Tbl_Product.ID 
WHERE [_RowNum_] > 20 
ORDER BY [_TempTable_].[_RowNum_] ASC 

Query field list, remove the field does not care about (here is IsDeleted, because the condition of IsDeleted=0, found out the products are not deleted);
Sort basis, when calling this method, try to ensure that the sort basis can determine the location of the record in the result set only by 1 (auxiliary sort basis is added here, ID DESC, if the product is renamed, the late one is added first);
Performance optimization 1 point of advice: if the value of the field is calculated, such as: total price = unit price * quantity, and this time the total price is greater than the number of records, also have to take the total price increasing or decreasing sort, if not a temporary table, when the data is large, wait to buy a new computer! You ask me why I buy a new computer, oh, because you will destroy the current computer! O ~ O (studying studying)
Another tip: when using ROW_NUMBER, remember that 1 must be used together with "TOP n" 1. n equals int.MaxValue is faster than TOP n.
Finally, please which kind person to test the performance, please, I database novice, do not know the performance of the database test.

All I know is that I'm confident in the paging sort I wrote, (*^ ^ ^*) hee hee!

Starting: boke park - > The sword passed without leaving a mark