ASP. NET tips: Paging solution under access

I will not talk about it more specifically. I will only post the relevant source code ~

Using system;
Using system. Collections. Generic;
Using system. text;
Using system. Data;
Using system. Data. oledb;
Using system. Web;

/** // <Summary>
/// Name: the paging solution under access (similar to the SQL stored procedure)
/// Author: cncxz (insects)
// Blog: http://cncxz.cnblogs.com
/// </Summary>
Public class adopager
{
Protected string m_connstring;
Protected oledbconnection m_conn;

Public adopager ()
{
Createconn (string. Empty );
}
Public adopager (string dbpath)
{
Createconn (dbpath );
}

Private void createconn (string dbpath)
{
If (string. isnullorempty (dbpath ))
{
String STR = system. configuration. configurationmanager. receivettings ["dbpath"] as string;
If (string. isnullorempty (STR ))
STR = "~ /App_data/DB. mdb ";
M_connstring = string. Format (@ "provider = Microsoft. Jet. oledb.4.0; Data Source = {0}", httpcontext. Current. server. mappath (STR ));
}
Else
M_connstring = string. Format (@ "provider = Microsoft. Jet. oledb.4.0; Data Source = {0}", dbpath );

M_conn = new oledbconnection (m_connstring );
}
/** // <Summary>
/// Open the connection
/// </Summary>
Public void connopen ()
{
If (m_conn.state! = Connectionstate. open)
M_conn.open ();
}
/** // <Summary>
/// Close the connection
/// </Summary>
Public void connclose ()
{
If (m_conn.state! = Connectionstate. Closed)
M_conn.close ();
}

Private string recordid (string query, int passcount)
{
Oledbcommand cmd = new oledbcommand (query, m_conn );
String result = string. empty;
Using (idatareader DR = cmd. executereader ())
{
While (dr. Read ())
{
If (passcount <1)
{
Result + = "," + dr. getint32 (0 );
}
Passcount --;
}
}
Return result. substring (1 );
}

/** // <Summary>
/// Obtain the records that should be displayed on the current page. Note: The query must contain an automatic ID column named ID. If it does not meet your requirements, modify the source code.
/// </Summary>
/// <Param name = "pageindex"> current page number </param>
/// <Param name = "pagesize"> page size </param>
/// <Param name = "showstring"> displayed field </param>
/// <Param name = "querystring"> query string, supporting joint query </param>
/// <Param name = "wherestring"> query condition. If conditions are specified, the query condition must start with where. </param>
/// <Param name = "orderstring"> sorting rules </param>
/// <Param name = "pagecount"> outgoing parameter: total page count </param>
/// <Param name = "recordcount"> outgoing parameter: Total record statistics </param>
/// <Returns> load the record's able </returns>
Public datatable executepager (INT pageindex, int pagesize, string showstring, string querystring, string wherestring, string orderstring, out int pagecount, out int recordcount)
{
If (pageindex <1) pageindex = 1;
If (pagesize <1) pagesize = 10;
If (string. isnullorempty (showstring) showstring = "*";
If (string. isnullorempty (orderstring) orderstring = "id DESC ";
Connopen ();
String myvw = string. Format ("({0}) tempvw", querystring );
Oledbcommand distinct COUNT = new oledbcommand (string. Format ("select count (0) as recordcount from {0} {1}", myvw, wherestring), m_conn );

Recordcount = convert. toint32 (repeated count. executescalar ());

If (recordcount % pagesize)> 0)
Pagecount = recordcount/pagesize + 1;
Else
Pagecount = recordcount/pagesize;
Oledbcommand cmdrecord;
If (pageindex = 1) // page 1
{
Cmdrecord = new oledbcommand (string. format ("select top {0} {1} from {2} {3} order by {4}", pagesize, showstring, myvw, wherestring, orderstring), m_conn );
}
Else if (pageindex> pagecount) // exceeds the total number of pages
{
Cmdrecord = new oledbcommand (string. format ("select top {0} {1} from {2} {3} order by {4}", pagesize, showstring, myvw, "where 1 = 2 ", orderstring), m_conn );
}
Else
{
Int pagelowerbound = pagesize * pageindex;
Int pageupperbound = pagelowerbound-pagesize;
String recordids = recordid (string. format ("select top {0} {1} from {2} {3} order by {4}", pagelowerbound, "ID", myvw, wherestring, orderstring ), pageupperbound );
Cmdrecord = new oledbcommand (string. format ("select {0} from {1} Where ID in ({2}) order by {3}", showstring, myvw, recordids, orderstring), m_conn );

}
Oledbdataadapter dataadapter = new oledbdataadapter (cmdrecord );
Datatable dt = new datatable ();
Dataadapter. Fill (DT );
Connclose ();
Return DT;
}
}

Call example: HTMLCode

<% @ Page Language = "C #" autoeventwireup = "true" codefile = "default. aspx. cs" inherits = "_ default" %>

<! Doctype HTML public "-// W3C // dtd xhtml 1.0 transitional // en" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<HTML xmlns = "http://www.w3.org/1999/xhtml">
<Head runat = "server">
<Title> paging demonstration </title>
</Head>
<Body>
<Form ID = "form1" runat = "server">
<Div>
<Br/>
Go to the <asp: textbox id = "txtpagesize" runat = "server" width = "29px"> 1 </ASP: textbox> page <asp: button id = "btnjump" runat = "server" text = "go" onclick = "btnjump_click"/> <br/>
<Asp: gridview id = "gridview1" runat = "server" cellpadding = "4" forecolor = "#333333" gridlines = "NONE" width = "90%">
<Footerstyle backcolor = "#507cd1" font-bold = "true" forecolor = "white"/>
<Rowstyle backcolor = "# eff3fb"/>
<Editrowstyle backcolor = "# 2461bf"/>
<Selectedrowstyle backcolor = "# d1ddf1" font-bold = "true" forecolor = "#333333"/>
<Pagerstyle backcolor = "# 2461bf" forecolor = "white" horizontalalign = "center"/>
<Headerstyle backcolor = "#507cd1" font-bold = "true" forecolor = "white"/>
<Alternatingrowstyle backcolor = "white"/>
</ASP: gridview>

</Div>
<Asp: Label id = "label1" runat = "server" text = "label"> </ASP: Label>
</Form>
</Body>
</Html>

Sample codebehind code

Using system;
Using system. Data;
Using system. configuration;
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;
Using system. Collections. Generic;

Public partial class _ default: system. Web. UI. Page
{
Private adopager mm_pager;
Protected adopager m_pager
{
Get {
If (mm_pager = NULL)
Mm_pager = new adopager ();
Return mm_pager;
}
}
Protected void page_load (Object sender, eventargs E)
{
If (! Ispostback)
Loaddata ();
}
Private int pageindex = 1;
Private int pagesize = 20;
Private int pagecount =-1;
Private int recordcount =-1;

Private void loaddata ()
{
String strquery = "select a. *, B. kindtext from tabletest a left join tablekind B on A. kindcode = B. kindcode ";
String strshow = "ID, subject, kindcode, kindtext ";

Datatable dt = m_pager.executepager (pageindex, pagesize, strshow, strquery, "", "id DESC", out pagecount, out recordcount );
Gridview1.datasource = DT;
Gridview1.databind ();
Label1.text = string. format ("{0} records in total, {1} records per page, page count {2}/{3}", recordcount, pagesize, pageindex, pagecount );
}

Protected void btnjump_click (Object sender, eventargs E)
{
Int. tryparse (txtpagesize. Text, out pageindex );
Loaddata ();
}
}