The development platform is 2003, and the control used is repeater.
Using system;
Using system. Data;
Using system. Data. sqlclient;
Namespace pagination. Class
{
/// <Summary>
/// Summary of pager.
/// </Summary>
Public class Pager
{
Public Pager ()
{
//
// Todo: add the constructor logic here
//
}
/// <Param name = "fieldlist"> </param> Field List, such as ID, title, and content
/// <Param name = "condition"> </param> condition, such as ID <100
/// <Param name = "pkey"> </param> Primary Key, auto-Increment
/// <Param name = "tablename"> </param> table name, such as news
/// <Param name = "sort"> </param> sort. 0 indicates descending order, and 1 indicates ascending order.
/// <Param name = "pagesize"> </param> size of each page
/// <Param name = "cpage"> </param> current page number
Public dataset getcurrentdataset (string fieldlist, string condition, string pkey, string tablename, int sort, int pagesize, int cpage) // obtain the current page record
{
Sqlconnection conn = new sqlconnection (system. configuration. configurationsettings. deleettings ["CS"]);
Sqldataadapter cmd = new sqldataadapter (getsql (fieldlist, condition, pkey, tablename, sort, pagesize, cpage), Conn );
Dataset DS = new dataset ();
Cmd. Fill (DS );
Return Ds;
}
Static string getsql (string fieldlist, string condition, string pkey, string tablename, int sort, int pagesize, int cpage)
{
String SQL = "";
If (sort = 0)
{
If (condition! = "")
{
SQL = "select top" + pagesize. tostring () + "" + fieldlist + "from" + tablename + "where" + condition + "and" + pkey + "not in (select top" + pagesize * (cpage-1) + "" + pkey + "from" + tablename + "where" + condition + "and order by" + pkey + "DESC) order by" + pkey + "DESC ";
}
Else
{
SQL = "select top" + pagesize. tostring () + "" + fieldlist + "from" + tablename + "where" + pkey + "not in (select top" + pagesize * (cpage-1) + "" + pkey + "from" + tablename + "order by" + pkey + "DESC) order by" + pkey + "DESC ";
}
}
Else
{
If (condition! = "")
{
SQL = "select top" + pagesize. tostring () + "" + fieldlist + "from" + tablename + "where" + condition + "and" + pkey + "not in (select top" + pagesize * (cpage-1) + "" + pkey + "from" + tablename + "where" + condition + "and order by" + pkey + "ASC) order by" + pkey + "ASC ";
}
Else
{
SQL = "select top" + pagesize. tostring () + "" + fieldlist + "from" + tablename + "where" + pkey + "not in (select top" + pagesize * (cpage-1) + "" + pkey + "from" + tablename + "order by" + pkey + "ASC) order by" + pkey + "ASC ";
}
}
Return SQL;
}
Public int getcounts (string pkey, string tablename, string condition) // obtain the total number of records.
{
String SQL;
If (condition! = "")
{
SQL = "select count (" + pkey + ") from" + tablename;
}
Else
{
SQL = "select count (" + pkey + ") from" + tablename;
}
Sqlconnection conn = new sqlconnection (system. configuration. configurationsettings. deleettings ["CS"]);
Sqlcommand cmd = new sqlcommand (SQL, Conn );
Conn. open ();
Sqldatareader DR = cmd. executereader (commandbehavior. closeconnection );
Dr. Read ();
String STR = Dr [0]. tostring ();
Dr. Close ();
Return convert. toint32 (STR );
}
}
}
The total number of records and the dataset of the current page can be output here.
Call instances for this generic paging class
1. Create Table news
Create Table [News] (
[ID] [int] identity (1, 1) not null,
[Title] [nvarchar] (100) Collate chinese_prc_ci_as null,
[Content] [text] collate chinese_prc_ci_as null,
Constraint [pk_news] primary key clustered
(
[ID]
) On [primary]
) On [primary] textimage_on [primary]
Go
2. Create a data storage process
Create proc addnews
@ Count int
As
Declare @ I int
Set @ I = 0
Set nocount on
While @ I <@ count
Begin
Set @ I = @ I + 1
Insert into news (title, content) values ('Million-level data paging test', 'test by day★Feng ')
End
Set nocount off
Go
3. generate data
Addnews 2000000
4. Create An ASPX File
<Asp: repeater id = "repeater1" runat = "server">
<Headertemplate>
<Table width = "800" border = "1" cellpadding = "3">
</Headertemplate>
<Itemtemplate>
<Tr>
<TD> <% # databinder. eval (container. dataitem, "ID") %> </TD>
<TD> <% # databinder. eval (container. dataitem, "title") %> </TD>
<TD> <% # databinder. eval (container. dataitem, "content") %> </TD>
</Tr>
</Itemtemplate>
<Footertemplate>
</Table>
</Footertemplate>
</ASP: repeater>
<Div align = "center"> <font face = ""> total </font>
<Asp: Label id = "lblcount" runat = "server"> </ASP: Label> <font face = ""> record, current
<Asp: Label id = "lblcurrent" runat = "server"> </ASP: Label> page & nbsp;
<Asp: linkbutton id = "hlprev" runat = "server"> previous page </ASP: linkbutton> & nbsp;
<Asp: linkbutton id = "hlnext" runat = "server"> next page </ASP: linkbutton> & nbsp;
<Asp: dropdownlist id = "dropdownlist1" runat = "server" autopostback = "true"> </ASP: dropdownlist> </font> </div>
5. CS files
Protected system. Web. UI. webcontrols. Label lblcount;
Protected system. Web. UI. webcontrols. Label lblcurrent;
Protected system. Web. UI. webcontrols. dropdownlist dropdownlist1;
Protected system. Web. UI. webcontrols. linkbutton hlprev;
Protected system. Web. UI. webcontrols. linkbutton hlnext;
Protected system. Web. UI. webcontrols. Repeater repeater1;
Private int pagesize = 20;
Static int cpage;
Static int totalpages;
Private void page_load (Object sender, system. eventargs E)
{
// Place the user hereCodeTo initialize the page
If (! This. ispostback)
{
Cpage = 1;
Class. Pager Pg = new class. Pager ();
Int counts = pg. getcounts ("ID", "news ","");
Lblcount. Text = counts. tostring (); // obtain the total number of records.
Totalpages = counts/pagesize;
If (counts <pagesize)
Totalpages = 1;
Else
{
If (counts % pagesize! = 0) totalpages ++;
} // Calculate the total number of pages
Lblcurrent. Text = cpage. tostring () + "/" + totalpages;
Dataset DS = new dataset ();
DS = pg. getcurrentdataset ("ID, title, content", "", "ID", "news", 1, 10, cpage );
BIND (DS );
Gethl ();
Getddlist ();
}
}
Private void getddlist ()
{
If (totalpages! = 1)
{
For (INT I = 1; I <= totalpages; I ++)
{
Dropdownlist1.items. Add (New listitem (I. tostring (), I. tostring ()));
}
}
Else
{
Dropdownlist1.items. Add (New listitem ("1", "1 "));
}
}
Private void gethl () // get the status of the previous page and next page
{
If (lblcurrent. Text = "1 ")
{
Hlprev. Enabled = false;
Hlnext. Enabled = false;
}
Else
{
If (cpage = 1)
Hlprev. Enabled = false;
Else
Hlprev. Enabled = true;
If (cpage = convert. toint32 (totalpages ))
Hlnext. Enabled = false;
Else
Hlnext. Enabled = true;
}
Lblcurrent. Text = cpage. tostring () + "/" + totalpages;
}
Private void BIND (Dataset DS)
{
Repeater1.datasource = Ds;
Repeater1.databind ();
}
# Code generated by region web Form Designer
Override protected void oninit (eventargs E)
{
//
// Codegen: This call is required by the ASP. NET web form designer.
//
Initializecomponent ();
Base. oninit (E );
}
/// <Summary>
/// The designer supports the required methods-do not use the code editor
/// Modify the content of this method.
/// </Summary>
Private void initializecomponent ()
{
This. hlprev. Click + = new system. eventhandler (this. hlprev_click );
This. hlnext. Click + = new system. eventhandler (this. hlnext_click );
This. dropdownlist1.selectedindexchanged + = new system. eventhandler (this. dropdownlist+selectedindexchanged );
This. Load + = new system. eventhandler (this. page_load );
}
# Endregion
Private void hlprev_click (Object sender, system. eventargs E)
{
Cpage = cpage-1;
Class. Pager Pg = new class. Pager ();
Dataset DS = new dataset ();
DS = pg. getcurrentdataset ("ID, title, content", "", "ID", "news", cpage, 10, cpage );
BIND (DS );
Gethl ();
Dropdownlist1.clearselection ();
Dropdownlist1.items. findbyvalue (cpage. tostring (). Selected = true;
}
Private void hlnext_click (Object sender, system. eventargs E)
{
Cpage = cpage + 1;
Class. Pager Pg = new class. Pager ();
Dataset DS = new dataset ();
DS = pg. getcurrentdataset ("ID, title, content", "", "ID", "news", cpage, 10, cpage );
BIND (DS );
Gethl ();
Dropdownlist1.clearselection ();
Dropdownlist1.items. findbyvalue (cpage. tostring (). Selected = true;
}
Private void dropdownlistpolicselectedindexchanged (Object sender, system. eventargs E)
{
Cpage = convert. toint32 (dropdownlist1.selectedvalue );
Class. Pager Pg = new class. Pager ();
Dataset DS = new dataset ();
DS = pg. getcurrentdataset ("ID, title, content", "", "ID", "news", cpage, 10, cpage );
BIND (DS );
Gethl ();
}
My machine Pentium m1.7g 480 mddrm, with a physical memory of about 70 MB. The average paging time of 0.5 million data records is about seconds.