C # efficient paging sorting using ROW_NUMBER in concatenated SQL statements

If you want to use a database in the project, you must use paging sorting. During database query optimization, I wrote the following code overnight to splice SQL statements sorted by page.

Copy codeThe Code is as follows: // <summary>
/// Obtain paging SQL statements for a single table (view)
/// </Summary>
/// <Param name = "tableName"> table name or view name </param>
/// <Param name = "key"> unique key </param>
/// <Param name = "fields"> obtained field </param>
/// <Param name = "condition"> query conditions (excluding WHERE) </param>
/// <Param name = "collatingSequence"> sorting rules (excluding order by) </param>
/// <Param name = "pageSize"> page size </param>
/// <Param name = "pageIndex"> page number (starting from 1) </param>
/// <Returns> paging SQL statement </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>
/// Add the table name prefix to the field
/// </Summary>
/// <Param name = "tableName"> table name </param>
/// <Param name = "fields"> Field </param>
/// <Param name = "separator"> delimiter used to identify 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 );
}

Assume that the following table is available:Copy codeThe Code is as follows: 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 (sequence number, non-empty, auto-increment)
Tbl_Product-> ProductId (product Id, primary key)
Tbl_Product-> ProductName (product name, not blank)
Tbl_Product-> IsDeleted (Virtual deletion tag, not empty)
Call BasicFunction. getPagingSQL ("Tbl_Product", "ID", "ID, ProductId, ProductName", "IsDeleted = 0", "ProductName ASC, id desc", 5, 5 ), basicFunction is the static class of the paging sorting method. The generated paging sorting SQL statement is as follows (the format has been adjusted manually ):Copy codeThe Code is as follows: 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

The list of queried fields removes the fields that you don't care about. (IsDeleted is used here, because IsDeleted = 0 in the condition, all the products found are not deleted );
Sorting basis. When calling this method, we should try to ensure that the sorting basis can uniquely determine the position of records in the result set (Here we add a secondary sorting basis, id desc, if the product name is duplicate, );
Some suggestions for performance optimization: if the field value is calculated, for example, the total price = unit price * quantity, and the records whose total price is greater than the actual number need to be sorted in ascending or descending order of the total price, if you do not need a temporary table, you can buy a new computer when the data volume is large! Why do you want to buy a new computer! O (distinct _ distinct) O ~
We also recommend that you use ROW_NUMBER together with "TOP n". n equals int. MaxValue is faster than "TOP n.
Finally, please kindly ask someone to test the performance. Please, I am a newbie in the database, and I do not know much about the performance testing of the database.

I only know that I still have confidence in the paging sorting I wrote (* ^__ ^!

Starter: blog Park-> no trace of sword