No nonsense. Check the Code:
1. First, I used a stored procedure to solve this problem. To understand this problem, I should first start with the stored procedure. The Code is as follows:
Create proc getdataset
@ Tablelist varchar (200) = '*', -- searches for table fields, such as 'id, datatime, job', which are separated by commas (,).
@ Tablename varchar (30), -- Name of the table to be searched
@ Selectwhere varchar (500) = '', -- search condition. Do not write where here, for example: Job = 'teacher' and class = '2'
@ Selectorderid varchar (20), -- table primary key field name. For example, ID
@ Selectorder varchar (200) = '', -- sort. Multiple fields can be used for sorting, but the primary key field must be at the beginning. You can also leave it unspecified, for example, order by class ASC.
@ Intpageno Int = 1, -- page number
@ Intpagesize Int = 10, -- number of entries displayed on each page
@ Recordcount int output -- total number of records (Stored Procedure output parameters)
As Declare @ tmpselect nvarchar (600)
Declare @ TMP nvarchar (600) Set nocount on -- disable counting Set @ tmpselect = 'select @ recordcount = count (*) from '+ @ tablename + ''+ @ selectwhere Execute sp_executesql
@ Tmpselect, -- execute the preceding SQL statement
N' @ recordcount int output', -- execute the SQL statement of the output data, and output the total number of records
@ Recordcount output If (@ recordcount = 0) -- if there is no post, zero is returned.
Return 0
/* Determine whether the page number is correct */
If (@ intpageno-1) * @ intpagesize> @ recordcount -- if the page number is greater than the total number of pages, an error is returned.
Return (-1)
Set nocount off -- enable count
If @ selectwhere! =''
Begin
Set @ tmpselect = 'select top '+ STR (@ intpagesize) + ''+ @ tablelist + 'from' + @ tablename + 'where' + @ selectorderid + 'not in (select top' + STR (@ intPageNo-1) * @ intpagesize) + ''+ @ selectorderid + 'from' + @ tablename +'' + @ selectwhere + ''+ @ selectorder + ') and' + @ selectwhere +'' + @ selectorder
End
Else
Begin
Set @ tmpselect = 'select top '+ STR (@ intpagesize) + ''+ @ tablelist + 'from' + @ tablename + 'where' + @ selectorderid + 'not in (select top' + STR (@ intPageNo-1) * @ intpagesize) + ''+ @ selectorderid + 'from' + @ tablename +'' + @ selectorder + ')' + @ selectorder
End
Execute sp_executesql @ tmpselect
Return (@ rowcount)
Go |
In fact, the Code is also very simple. Programmers basically understand databases, and this stored procedure is not a problem.
I have explained other codes. I have not explained the color section. I will explain it here. It's actually quite simple. Let's take a look:
Select top '+ STR (@ intPageNo-1) * @ intpagesize) + ''+ @ selectorderid + 'from' + @ tablename +'' + @ selectwhere + ''+ @ selectorder +'
What is the execution result of this Code? Is it a set of primary keys in front of the current page, now we select from all the tables that the value of the primary key is not within the result. Isn't the pagesize record the content of the current page?
2. Why don't I need to change the ASPX page? I will write the code here:
| <% @ Page Language = "C #" autoeventwireup = "true" codefile = "AA. 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> No title page </title>
</Head>
<Body>
<Form ID = "form1" runat = "server">
<Div>
<Asp: gridview id = "gridview1" runat = "server" autogeneratecolumns = "false" Height = "180px" width = "867px">
<Columns>
<Asp: boundfield datafield = "job_id" headertext = "job_id"/>
<Asp: boundfield datafield = "job_desc" headertext = "job_desc"/>
<Asp: boundfield datafield = "max_lvl" headertext = "max_lxl"/>
</Columns>
</ASP: gridview> </Div>
<Asp: hyperlink id = "hylfirst" runat = "server"> homepage </ASP: hyperlink>
<Asp: hyperlink id = "hylprev" runat = "server"> previous page </ASP: hyperlink>
<Asp: hyperlink id = "hylnext" runat = "server"> next page </ASP: hyperlink>
<Asp: hyperlink id = "hylend" runat = "server"> last page </ASP: hyperlink>
Page <asp: Label id = "lbrow" runat = "server" text = "label"> </ASP: Label>,
Total <asp: Label id = "lbpage" runat = "server" text = "label"> </ASP: Label> pages, total <asp: Label
Id = "lbrecord" runat = "server" text = "label"> </ASP: Label> records, go to <asp: textbox id = "txtlink"
Runat = "server" width = "29px"> </ASP: textbox>
Page <asp: linkbutton id = "Link" runat = "server" onclick = "link_click" tabindex = "1"> to </ASP: linkbutton>
</Form>
</Body>
</Html> |
3. The CS page is actually a good example of every page, that is, some common code ...... I add the Code. If you have any questions, please reply to me and explain:
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;
Using system. Data. sqlclient;Public partial class _ default: system. Web. UI. Page
{
Protected void page_load (Object sender, eventargs E)
{ This. BIND ();
} Protected void link_click (Object sender, eventargs E)
{
Int page = convert. toint32 (txtlink. Text );
Response. Redirect ("AA. aspx? Currentpage = "+ Page + "");
}
Public void BIND ()
{
Int sumpage;
Int pageno = 1;
Int pagesize = 3;
If (request. querystring ["currentpage"] = NULL)
{
Pageno = 1;
}
Else
{
Pageno = int32.parse (request. querystring ["currentpage"]);
} Sqlconnection conn = new sqlconnection (configurationmanager. etettings ["constr"]);
Sqldataadapter da = new sqldataadapter ();
Da. selectcommand = new sqlcommand ();
Da. selectcommand. Connection = conn;
Da. selectcommand. commandtext = "getdataset ";
Da. selectcommand. commandtype = commandtype. storedprocedure;
Da. selectcommand. Parameters. Add ("@ tablelist", sqldbtype. varchar, 200). value = "job_id, job_desc, max_lvl ";
Da. selectcommand. Parameters. Add ("@ tablename", sqldbtype. varchar, 30). value = "Jobs ";
// Da. selectcommand. Parameters. Add ("@ selectwhere", sqldbtype. varchar, 500). value = "Where d = 1 ";
Da. selectcommand. Parameters. Add ("@ selectorderid", sqldbtype. varchar, 20). value = "job_id ";
Da. selectcommand. Parameters. Add ("@ selectorder", sqldbtype. varchar, 200). value = "order by min_lvl ASC ";
Da. selectcommand. Parameters. Add ("@ intpageno", sqldbtype. INT). value = pageno;
Da. selectcommand. Parameters. Add ("@ intpagesize", sqldbtype. INT). value = pagesize;
Da. selectcommand. Parameters. Add ("@ recordcount", sqldbtype. INT). Direction = parameterdirection. output;
Da. selectcommand. Parameters. Add ("rowcount", sqldbtype. INT). Direction = parameterdirection. returnvalue;
Dataset DS = new dataset ();
Da. Fill (DS, "Jobs ");
Gridview1.datasource = Ds;
Gridview1.databind ();
Int32 recordcount = (int32) Da. selectcommand. Parameters ["@ recordcount"]. value; // obtain the total number of records, which is the output value.
Int32 rowcount = (int32) Da. selectcommand. Parameters ["rowcount"]. value; // obtain the number of records on the current page, which is not equal to pagesize on the last page,
Lbrecord. Text = recordcount. tostring ();
Lbrow. Text = pageno. tostring ();
Sumpage = (int32) recordcount/pagesize;
If (recordcount % pagesize> 0)
{
Sumpage = sumpage + 1;
}
Lbpage. Text = sumpage. tostring ();
If (pageno> 1)
{
Hylfirst. navigateurl = "AA. aspx? Currentpage = 1 ";
Hylprev. navigateurl = string. Concat ("AA. aspx? Currentpage = "," ", pageno-1 );
}
Else
{
Hylprev. navigateurl = "";
Hylfirst. navigateurl = "";
Hylfirst. Visible = false;
Hylprev. Visible = false;
}
If (pageno <sumpage)
{
Hylend. navigateurl = string. Concat ("AA. aspx? Currentpage = "," ", sumpage );
Hylnext. navigateurl = string. Concat ("AA. aspx? Currentpage = "," ", pageno + 1 );
}
Else
{
Hylnext. navigateurl = "";
Hylend. navigateurl = "";
Hylend. Visible = false;
Hylnext. Visible = false;
} }
} |
|
