Simple Application of Asp.net + jquery + jqgrid + json.net (3)

Finally, the sqlcomment class is operated. The source code of the two methods is published here,AlgorithmNot very good. Please tell me if you have better algorithms!

Code

  Public  Dataset getallcomments ()
{
  String  Strsql  =     String  . Empty;

Strsql  =     " Select commentid, typeid, fromid, [name], contents, publishdate, IP from tb_comment  "  ;

  Return  Basicsqlhelper. Query (strsql,  Null  );
}

  ///     <Summary>  
  ///  Deliver data based on specified conditions
  ///    </Summary>  
  ///     <Param name = "start">  Start position  </Param>  
  ///     <Param name = "end">  End position  </Param>  
  ///    <Param name = "orderid">  Sort  </Param>  
  ///     <Param name = "orderway">  Sorting method: ASC/DESC  </Param>  
  ///     <Returns> </returns>  
    Public Dataset getcommentsfromto (  Int  Start,  Int  End,  String  Orderid,  String  Orderway,  Int  Page)
{
  String  Strsql  =     String  . Empty;

 //  Make sure that end is larger than start.  
    If  (Start  >  End)
{
  Int  Temp  =  Start;
Start  =  End;
End  =  Temp;
}

Strsql =     "  Select top  "     +  (End  -  Start  +     1  )  +     "  * From tb_comment "  ;
Strsql  + =     "  Where  "  ;
Strsql  + =     "  (  "  ;
Strsql  + =  Orderid  +    "  Not in  "  ;
Strsql  + =     "  (  "  ;
  If  (Page  =     1  )
{
Strsql + =     "  Select top  "     +  (Start  -     1  )  +     "     "     + Orderid  +     "  From tb_comment  "  ;
}
  Else  
{
Strsql  + =     "  Select top  "     +  (Start -     2  )  +     "     "     +  Orderid  +     "  From tb_comment  "  ;
}
Strsql + =     "  Order  "     +  Orderid  +     "     "     +  Orderway  +     "    "  ;
Strsql  + =     "  )  "  ;
Strsql  + =     "  )  "  ;
Strsql  + =     " Order  "     +  Orderid  +     "     "     +  Orderway  +     "     " ;

  Return  Basicsqlhelper. Query (strsql,  Null  );
} 

Finally, paste a paging SQLCodeFor your reference ~

 

So, sort them out.

SQL Server 2005

Pagecount: number of data entries required for one page

Pageindex: page index

Select top pagecount * from (

Select top pagecount * from (

Select top pagecount * pageindex * From tablename order by ID) as tmp1 order by ID DESC

) As tmp2 order by ID

)

For example, there are 100 posts in the forum, and 20 posts are displayed on each page. If there are 2nd pages. Then, sort by time, fetch the first 40 records, then sort the 40 records by time in reverse order, retrieve the first 20 records, and then sort them by time, read the data in the normal order. Therefore, this page is sorted by the triple top combination.

Oracle 10i:

SQL: A common SELECT statement

Fromindex: slave... Entries

Toindex:... Entries

Common Methods:

Select * from (

Select row _. *, rownum _ from (

SQL

) Row _ Where rownum <= toindex

) Where rownum _> fromindex

In the preceding example, rownum is the sequence number of the query result. For example, if it is 2nd pages, 0th to 40 (<= toindex) posts are obtained. Then, retrieve the post larger than 20.

Sorting with a unique identifier (Id field)

Select * From tablename where ID in (

Select ID from (

Select rownum _, ID from (

Select ID from tablename order by code

) Where rownum <= toindex

) Where rownum _> fromindex

)

========================================================

MySQL page

The general situation is as follows:

Select * From message order by id desc limit 10000, 20

This article introduces a "clue" approach to provide some "clues" for turning pages, such as select * From message order by id desc, paging by ID in descending order, 20 entries per page, currently, there are 10th pages. The maximum ID of the current page is 9527, and the minimum is 9500, if we only provide jumps such as "Previous Page" and "next page" (do not provide jumps to page N), the SQL statement can be:

Select * From message where ID> 9527 order by IDASCLimit 20;

When processing the next page, the SQL statement can be:

Select * From message where ID <9500 order by IDDescLimit 20;

No matter how many pages are displayed, only 20 rows are scanned for each query.

================================= MySQL

I think this is a breakthrough on the basis of the original paging. It is also useful for paging between sqlserver and Oracle. If conditions are met, you can rewrite the paging SQL statements of sqlserver and Oracle, and add the where condition.

If you do not apply any restrictions, you can see that the performance is lower as you roll back. The last page is full table scan. If the limit is added, the time is almost a constant.

In paging, indexes are also very important. You must create an index on the index column to avoid full table scanning.

To sum up, let's say a few words:

SQL Server top,

Oracle rownum,

MySQL uses limit,

Index for sorting columns,

Add the where condition,

High paging performance.

 

OK. If you still do not understand or the debugging fails, leave a message ~