If a single field sorting paging, there are now a lot of stored procedures and SQL statements, pagination, only take pagesize records, you can meet the problem is:
This single field must be unique
This field must be sorted
Multi-field sorting is not supported
In response to this problem, I used C # to do a class, to solve the above multiple-field sorting paging and every time to take pagesize records of the problem first look at the code:
Copy Code code as follows:
Using System;
Using System.Collections.Specialized;
Namespace Web
{
<summary>
Summary description of Multiorderpagersql
</summary>
public class Multiorderpagersql
{
Private NameValueCollection orders = new NameValueCollection ();
private string Table_;
private string Where_= "";//1=1 and 2=2 format
private string Outfields_;
private int nowpageindex_=0;
private int pagesize_=0;
private string sql_;//the SQL to be returned
Public Multiorderpagersql ()
{
}
/**************** Method *******************/
public void Addorderfield (string field, string direction)
{
Orders. ADD (field, direction);
}
public string GetSQL ()
{
Sort fields
String orderlist= "";//the sort expected by the user
String orderList2 = "";//reverse ordering of user expectations
String orderList3 = ""; the sort that the user expects, with the prefix removed. The order of the outer layers in a compound query cannot be similar to Table1.id, remove Table1.
if (orders. Count > 0)
{
string[] str = orders. AllKeys;
foreach (string s in str)
{
String direction= "ASC";//default One Direction
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] + ",";
}
Get rid of 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 SQL
String strtemp;
strtemp = "SELECT * FROM \ n" (select top {7} * FROM (select top {6} {0} {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;
}
/**************** Property *******************/
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;}
}
}
}
To say the principle first: In fact very simple, because the AC and MS SQL 2000 does not like MS SQL 2005 Row_number function, we can not start from here, such as you take the second page, that is the serial number from 10-20, we first in accordance with a collation of the top 20 data out, Then in accordance with the previous collation of the reverse rule of the collation of the data, and then take the first 10, then this time is to take the data, this time has not ended, and then the results according to the previous sorting rules can be. I think the efficiency bottleneck will appear in the sort. See how it's going to work:
Copy Code code 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");//First sort field
Sql.addorderfield ("T1.hits", "desc")//second sort 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 tests were passed on AC and Ms SQL 2000 (5).
Temporarily make such a class, no stored procedures, to do, there is a little difficulty, hehe.
