Pagination like csdn
Paging sample
Source code download: http://download.csdn.net/source/369744
SQL
Create procedure sp_page
@ Currentpage int, @ pagesize int, @ field_info varchar (500), @ table_info varchar (20), @ field_id varchar (10), @ intorder int, @ otherwhere varchar (50 ), @ recordcount int output, @ pagecount int output
-- @ Currentpage indicates the page to be displayed. @ pagesize indicates the number of lines displayed on each page. @ field_info indicates that the field to be displayed can be *, and @ table_info indicates the table or view to be queried, @ field_id is sorted by this field, @ intorder0 is sorted in ascending order, 1 is sorted in descending order, @ otherwhere is the condition, @ recordcount is the total number of rows, and @ pagecount is the total number of pages.
As
Begin
Declare @ minpage int, @ maxpage int
Declare @ SQL varchar (1000)
Declare @ sqlt nvarchar (300)
Declare @ order varchar (4)
Set @ field_info = Replace (@ field_info, ''', '') -- remove 'from @ field_info'
Set @ table_info = Replace (@ table_info, ''', '') -- remove 'from @ table_info'
Set @ field_id = Replace (@ field_id, ''', '') -- remove 'from @ field_id'
Set @ otherwhere = Replace (@ otherwhere, ''', ''') -- replace 'in @ otherwhere with ''to correct the SQL statement'
Set @ sqlt = 'select @ recordcount = count ('+ @ field_id +') from '+ @ table_info
Exec sp_executesql @ sqlt, n' @ recordcount int output', @ recordcount output -- how to put the exec execution result into a variable. If it is a string, use N, the variable after N must have the same name as the variable in @ sqlt.
If @ pagesize <= 0
Begin
Set @ pagesize = 10
End
Else if @ pagesize> @ recordcount
Begin
Set @ pagesize = @ recordcount
End
Set @ pagecount = @ recordcount/@ pagesize
If (@ recordcount % @ pagesize )! = 0) -- add one page if there are not all
Begin
Set @ pagecount = @ recordcount/@ pagesize
Set @ pagecount = @ pagecount + 1
End
Else
Begin
Set @ pagecount = @ recordcount/@ pagesize
End
If @ currentpage <= 0
Begin
Set @ currentpage = 1
End
Else if @ currentpage> @ pagecount
Begin
Set @ currentpage = @ pagecount -- if the input page number is greater than the total page number, it indicates the last page.
End
Set @ minpage = (@ currentpage-1) * @ pagesize + 1
Set @ maxpage = @ minpage + @ pagesize-1
Begin
If @ intorder = 0
Set @ order = 'asc'
Else
Set @ order = 'desc'
If @ field_info like''
Set @ field_info = '*'
If @ otherwhere like''
Set @ SQL = 'select' + @ field_info + 'from (select '+ @ field_info +', row_number () over (order by '+ @ field_id + ''+ @ order +') as rownumber from '+ @ table_info + ') as a where rownumber between '+ convert (varchar (10), @ minpage) + 'and' + convert (varchar (10), @ maxpage)
Else
Set @ SQL = 'select' + @ field_info + 'from (select '+ @ field_info +', row_number () over (order by '+ @ field_id + ''+ @ order +') as rownumber from '+ @ table_info + ') as a where rownumber between '+ convert (varchar (10), @ minpage) + 'and' + convert (varchar (10), @ maxpage) + 'and' + @ otherwhere
Exec (@ SQL)
End
End
Declare @ rcon int
Declare @ pcon int
Exec sp_page 8, 73, '', 'user _ info', 'id', 0,'', @ rcon output, @ pcon output
Html
<%... @ Page Language = "C #" autoeventwireup = "true" codefile = "default2.aspx. cs" inherits = "pageing_default2" %>
<%... @ Register tagprefix = "PC3" namespace = "cutepager" assembly = "aspnetpagerv2netfx2_0" %>
<! 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>
<Link href = "styles/lightstyle.css" type = "text/CSS" rel = "stylesheet"/>
</Head>
<Body>
<Form ID = "form1" runat = "server">
<Div>
<Asp: repeater id = "repeater1" runat = "server">
<Headertemplate>
<Table Style = "background-color: # ffcc66;" cellpadding = "5" cellspacing = "0">
<Tr>
<TH style = "width: 70px;">
Index </Th>
<TH style = "width: 200px;">
Productname </Th>
</Tr>
</Table>
</Headertemplate>
<Itemtemplate>
<Table cellpadding = "5" cellspacing = "0" style = "background-color: # f0f1f2;">
<Tr>
<TD style = "width: 70px;" align = "center"> <%... # eval ("rownumber") %> </TD>
<TD style = "width: 200px;"> <%... # eval ("productname") %> </TD>
</Tr>
</Table>
</Itemtemplate>
<Alternatingitemtemplate>
<Table cellpadding = "5" cellspacing = "0" style = "background-color: # ccccff;">
<Tr>
<TD style = "width: 70px;" align = "center"> <%... # eval ("rownumber") %> </TD>
<TD style = "width: 200px;"> <%... # eval ("productname") %> </TD>
</Tr>
</Table>
</Alternatingitemtemplate>
</ASP: repeater>
<Br/>
<PC3: pager id = "pager1" runat = "server" oncommand = "pager_command" showfirstlast = "true">
</PC3: pager>
</Div>
</Form>
</Body>
</Html>
C #
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 pageing_default2: system. Web. UI. Page
...{
Protected void page_load (Object sender, eventargs E)
...{
If (! Ispostback)
...{
Pager1.currentindex = 1;
Bindrepeater (1 );
}
}
Const string strconn = "Data Source = sa; initial catalog = northwind; Integrated Security = true ";
Public void pager_command (Object sender, commandeventargs E)
...{
Int currnetpageindx = convert. toint32 (E. commandargument );
Pager1.currentindex = currnetpageindx;
Bindrepeater (currnetpageindx );
}
Private void bindrepeater (INT pageno)
...{
/**//*
@ Currentpage int, -- @ currentpage shows the page
@ Pagesize int, -- @ pagesize indicates the number of lines displayed on each page.
@ Field_info varchar (500), -- @ field_info: The field to be displayed can be *
@ Table_info varchar (20), -- @ table_info indicates the table or view to be queried.
@ Field_id varchar (10), -- @ field_id is sorted by this field
@ Intorder int, -- @ intorder0: ascending; 1: Descending;
@ Otherwhere varchar (50), -- @ otherwhere is the condition
@ Recordcount int output, -- @ recordcount indicates the total number of rows.
@ Pagecount int output -- @ pagecount indicates the total number of pages.
*/
Sqlconnection Cn = new sqlconnection (strconn );
Sqlcommand cmd = new sqlcommand ("DBO. sp_page", CN );
Cmd. commandtype = commandtype. storedprocedure;
Sqldatareader Dr;
Cmd. Parameters. Add ("@ currentpage", sqldbtype. Int, 4). value = pageno;
Cmd. Parameters. Add ("@ pagesize", sqldbtype. Int, 4). value = pager1.pagesize;
Cmd. Parameters. Add ("@ field_info", sqldbtype. varchar, 500). value = "*";
Cmd. Parameters. Add ("@ table_info", sqldbtype. varchar, 20). value = "Products"; // table name
Cmd. Parameters. Add ("@ field_id", sqldbtype. varchar, 10). value = "productid ";
Cmd. Parameters. Add ("@ intorder", sqldbtype. INT). value = 1; // sort
Cmd. Parameters. Add ("@ otherwhere", sqldbtype. varchar, 50). value = ""; // Condition
Cmd. Parameters. Add ("@ recordcount", sqldbtype. INT). Direction = parameterdirection. output; // The total number of rows
Cmd. Parameters. Add ("@ pagecount", sqldbtype. INT). Direction = parameterdirection. output;
CN. open ();
Dr = cmd. executereader ();
Repeater1.datasource = Dr;
Repeater1.databind ();
Dr. Close ();
CN. Close ();
Int32 _ totalrecords = convert. toint32 (CMD. Parameters ["@ recordcount"]. value );
Pager1.itemcount = _ totalrecords;
Pager1.pagecount = convert. toint32 (CMD. Parameters ["@ pagecount"]. value );
}
}
this article from the csdn blog, reprinted please indicate the source: http://blog.csdn.net/liuliang1232005/archive/2008/05/24/2476231.aspx
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
