General SQL class for generating multi-field sorting Paging

If a single field is sorted by page, there are a lot of stored procedures and SQL statements. When paging, only the pageSize record is obtained. The problem is:
This single field must be unique
This field must be sorted.
Multi-field sorting is not supported
To solve this problem, I used C # As a class to solve the above problem of sorting multiple fields by PAGE and getting pageSize records each time. Let's first look at the Code: Copy codeThe Code is as follows:

Using System;
Using System. Collections. Specialized;
Namespace web
{
/// <Summary>
/// Summary of MultiOrderPagerSQL
/// </Summary>
Public class MultiOrderPagerSQL
{
Private NameValueCollection orders = new NameValueCollection ();
Private string table _;
Private string where _ = ""; // The format of 1 = 1 and 2 = 2
Private string outfields _;
Private int nowPageIndex _ = 0;
Private int pagesize _ = 0;
Private string SQL _; // The SQL statement to be returned
Public MultiOrderPagerSQL ()
{
}
*******************/
Public void addOrderField (string field, string direction)
{
Orders. Add (field, direction );
}
Public string getSQL ()
{
// Sort Fields
String orderList = ""; // The sorting expected by the user
String orderList2 = ""; // reverse sorting of the user's expected sorting
String orderList3 = ""; // The expected sorting by the user, excluding the prefix. The outer sorting in the composite query cannot be similar to table1.id, and table1 ..
If (orders. Count> 0)
{
String [] str = orders. AllKeys;
Foreach (string s in str)
{
String direction = "asc"; // The default direction is one.
If (orders [s]. ToString () = "asc ")
Direction = "desc ";
// Remove the prefix field name
String s2 = "";
Int index = s. IndexOf (".") + 1;
S2 = s. Substring (index );
OrderList = orderList + s + "" + orders [s] + ",";
OrderList2 = orderList2 + s2 + "" + direction + ",";
OrderList3 = orderList3 + s2 + "" + orders [s] + ",";
}
// Remove the last number
OrderList = orderList. Substring (0, orderList. Length-1 );
OrderList2 = orderList2.Substring (0, orderList2.Length-1 );
OrderList3 = orderList3.Substring (0, orderList3.Length-1 );
}
// Return orderList2;
// Form an SQL statement
String strTemp;
StrTemp = "select * from \ n (select top {7} * from (select top {6} {0} from {1} \ n ";
If (where _! = "")
StrTemp = strTemp + "where {2} \ n ";
If (orderList! = "")
StrTemp = strTemp + "order by {3}) as tmp order by {4} \ n) \ n as tmp2 \ n order by {5} \ n ";
StrTemp = string. Format (strTemp, outfields _, table _, where _, orderList, orderList2, orderList3, nowPageIndex _ * pagesize _, pagesize _);
Return strTemp;
}
*******************/
Public string table
{
Set {table _ = value ;}
}
Public string where
{
Set {where _ = value ;}
}
Public string outfields
{
Set {outfields _ = value ;}
}
Public int nowPageIndex
{
Set {nowPageIndex _ = value ;}
}
Public int pagesize
{
Set {pagesize _ = value ;}
}
}
}

Let's talk about the principle first: in fact, it is very simple. Because AC and ms SQL 2000 do not have the row_number function like ms SQL 2005, we can't start from here. For example, if you take the second page, that is, the sequence number ranges from 10 to 20. We first extract the first 20 pieces of data according to a certain sorting rule, and then sort the data according to the reverse rules of the previous sorting rule, the first 10 data records are retrieved at this time, and the results are sorted according to the previous sorting rules. I think the efficiency bottleneck will appear in sorting. Let's see how it is used:

Copy codeThe Code is as follows: using System;
Using System. Data;
Using System. Configuration;
Using System. Collections;
Using System. Web;
Using System. Web. Security;
Using System. Web. UI;
Using System. Web. UI. WebControls;
Using System. Web. UI. WebControls. WebParts;
Using System. Web. UI. HtmlControls;
Public partial class MultiOrderPagerSQLTest: System. Web. UI. Page
{
Protected void Page_Load (object sender, EventArgs e)
{
Web. MultiOrderPagerSQL SQL = new web. MultiOrderPagerSQL ();
// SQL. addOrderField ("t1.id", "desc"); // The first sorting Field
SQL. addOrderField ("t1.hits", "desc"); // second sorting Field
SQL. table = "joke t1, type t2 ";
SQL. outfields = "t1. *, t2.type ";
SQL. nowPageIndex = 5;
SQL. pagesize = 10;
SQL. where = "t1.typeid = t2.typeid ";
Response. Write (SQL. getSQL ());
}
}

The above test passed on AC and ms SQL 2000 (5.

For the time being, this class is not made into a stored procedure. It is still difficult to do so.