Net paging implementation Series ii SQL statement Paging

This article from: http://hi.baidu.com/maozilee/blog/item/99dc0cf49248a46ddcc47403.html

SQL statement. In many cases, it is based on select top {0} * From t_o_log where id not in (select top {1} ID from t_o_log order by id desc) order by ID DESC The basic principle is to get the total number of M records, and get the first N records on the required page through M records. For example, if each page is 10 records, you need to go to 10 pages. The total number of records to be retrieved is 100, and what we need is actually the last 10 records. Therefore, the first 90 records are discarded. For convenience, this example uses aspnetpager as the display page number and paging orientation. Of course, you can also write the previous and next page redirection work by yourself. The implementation method is as follows: Step 1: drag the DataGrid Control (or datalist, etc.) from the Toolbox> Web form>) <Asp: DataGrid id = "datagrid1" runat = "server" width = "100%" autogeneratecolumns = "false" singlevalue = "# F 7f 7f 7 "oldvalue =" # ffffff "> </ASP: DataGrid> Step 2 double-click the form to enter codebehide Private void page_load (Object sender, system. eventargs E) { // Place user code here to initialize the page If (! Page. ispostback) { Getlog (); } } Private void getlog () { String strsql; // Count the total number of records Sqlconnection Conn = New sqlconnection (); Conn. connectionstring = @ "Server = MIS \ pmserver; uid = sa; Pwd = sa; database = test "; Conn. open (); Strsql = "select count (ID) from t_o_log "; Dataset DS = new dataset (); Sqldataadapter ADP = new sqldataadapter (strsql, Conn ); ADP. Fill (DS, "typeidlist"); this. aspnetpager1.recordcount = system. Convert. toint32 (Ds. Tables [0]. Rows [0] [0]); Binddata (); } Private void binddata () { String strsql; Int recordpage = (aspnetpager1.currentpageindex-1) * aspnetpager1.pagesize; Sqlconnection Conn = New sqlconnection (); Conn. connectionstring = @ "Server = MIS \ pmserver; uid = sa; Pwd = sa; database = test "; Conn. open (); Strsql = string. format ("select top {0} * From t_o_log where id not in (select top {1} ID from t_o_log order by id desc) order by id desc", aspnetpager1.pagesize. tostring (), recordpage. tostring ()); Dataset DS = new dataset (); Sqldataadapter ADP = new sqldataadapter (strsql, Conn ); ADP. Fill (DS, "typeidlist "); This. datagrid1.datasource = Ds. Tables ["t_o_log"]; This. datagrid1.databind (); // Dynamically set user-defined text content ADP. Dispose (); Aspnetpager1.custominfotext = "total records: <font color = \" Blue \ "> <B>" + aspnetpager1.recordcount. tostring () + "</B> </font> "; Aspnetpager1.custominfotext + = "Total number of pages: <font color = \" Blue \ "> <B>" + aspnetpager1.pagecount. tostring () + "</B> </font> "; Aspnetpager1.custominfotext + = "Current page: <font color = \" Red \ "> <B>" + aspnetpager1.currentpageindex. tostring () + "</B> </font> "; } Private void aspnetpager1_pagechanged (Object SRC, Wuqi. webdiyer. pagechangedeventargs E) {Aspnetpager1.currentpageindex = E. newpageindex; Binddata (); System. Text. stringbuilder sb = new stringbuilder ("<script language = \" javascript \ "> <! -- \ N "); SB. append ("Var El = Document. All ;"); SB. append (this. datagrid1.clientid ); SB. append (". scrollintoview (true );"); SB. append ("<"); SB. append ("/"); SB. append ("script> "); If (! Page. isstartupscriptregistered ("scrollscript ")) Page. registerstartupscript ("scrollscript", SB. tostring ());