When you use ASP.net to develop a Web program, you often experience problems with paging the data list page. In ASP.net, pagination can be implemented by binding data controls, such as the GridView, DataList, Repeater, ListView, and so on, to achieve paging effects. Some of the comparisons between them:
GridView: High development efficiency, with pagination, sorting, but high resource consumption.
DataList: Paging and sorting requires manual encoding, and paging needs to be implemented using the PagedDataSource class.
Repeater: does not provide any layout, the development cycle is long.
Note that when you use these controls, the ViewState feature must be turned on (that is, enableviewstate= "true" in net, which is open by default) because if you turn off ViewState view state, when you click on the next page of these jump buttons. NET pages cannot remember the current state.
But here's the point:
These paging methods that use the controls are essentially jumps that are implemented using JavaScript methods:
For example, DataList with the PagedDataSource class implementation code as follows:
| The code is as follows |
Copy Code |
<a id= "Lnkbtnnext" href= javascript:__dopostback (' Lnkbtnnext ', ') "> Next </a>
The GridView's own paging code is as follows:
<a href= "Javascript:__dopostback (' GridView1 ', ' page$2 ')" style= "color: #333333;" > 2nd page </a> |
And we know, as a Web site program development, seo/seo.html "target=" _blank > Search engine on the page (front) JS link is not sensitive, and will not crawl and index JS in the link, which will cause a serious situation, That is, the list page starts on the second page and cannot be crawled and indexed, and a large number of pages cannot appear on the search engine's results page.
Therefore, it is easy to design a more general efficient asp.net paging algorithm to achieve the following methods:
Put the code in the Default.aspx foreground page (note that you must add the runat= "Server" tag):
<div id= "divlist" runat= "Server" ></div>
Write code in the Default.aspx.cs background code page:
| The code is as follows |
Copy Code |
| Using System;
Using System.Collections;
Using System.Configuration;
Using System.Data;
Using System.Linq;
Using System.Web;
Using System.Web.Security;
Using System.Web.UI;
Using System.Web.UI.HtmlControls;
Using System.Web.UI.WebControls;
Using System.Web.UI.WebControls.WebParts;
Using System.Xml.Linq;
Using System.Data.SqlClient; public partial class _default:system.web.ui.page
{
ComClass CC = new ComClass ();//instantiated protected void Page_Load (object sender, EventArgs e)
{//initialization
if (! IsPostBack)
{
string html = Getlistdata ();
divlist. InnerHtml = html;
}
} public static bool Isnum (String str)
{//To determine if ASCII is a pure digit
if (str = NULL | | | str = = "") return false;
for (int i = 0; i < str. Length; i++)
{
if (Str[i] < ' 0 ' | | | str[i] > ' 9 ')
return false;
}
return true;
} public string Getlistdata ()
{//Binding data
String html = "";
SqlConnection conn = Cc.getconn ();
Conn. Open ();
SqlCommand cmd;
SqlDataReader Dr;
Try
{
int page = 1;
String page_get = request["page"]; Get current Page
if (Isnum (page_get)) page = Convert.ToInt32 (page_get);
int pagesize = 5; How many per page
int totalrecords, totalpages; Total number of records
cmd = new SqlCommand ("SELECT count (*) from data", conn);
Dr = cmd. ExecuteReader ();
Dr. Read ();
Totalrecords = Int32.Parse (dr[0]. ToString ());
Dr. Close ();
Cmd. Dispose ();
Total pages
if (totalrecords% pagesize = = 0) TotalPages = totalrecords/pagesize;
else TotalPages = totalrecords/pagesize + 1;
Current page
if (page < 1) page = 1;
if (page > totalpages) page = TotalPages; ---core paging algorithm---
String sql = "SELECT top" + pagesize + "* FROM data ORDER by id DESC";
if (page > 1) sql = "SELECT top" + pagesize + "* FROM data where id< (select Min (ID) to (select Top + pagesize) * (PAGE-1) + "ID from the data order by id DESC) as T" ORDER by id DESC ";
cmd = new SqlCommand (SQL, conn);
Dr = cmd. ExecuteReader ();
while (Dr. Read ())
{
HTML + + dr["id"]. ToString () + "," + dr["title"]. ToString () + "<br/>";
}
Dr. Close ();
Cmd. Dispose (); Paging Code
string cfile = Request.path; Virtual path for the current request
String pagestr = "Rn<div class= ' page_fenye ' ><a href= '" + CFile + "' > Home </a>";
for (int i=1; i<=totalpages; i++)
{
if (i = = page) Pagestr + + + "<span>" + i + "<span>";
Else
{
if (Math.Abs (page-i) < 8)
{
if (i = = 1) pagestr + = "<a href= '" + CFile + "' >" + i + "</a>";
else pagestr + = "<a href=" "+ CFile +" "page=" + i + "' >" + i + "</a>";
}
}
}
Pagestr + + "<a href=" "+ CFile +" "page=" + totalpages + "' > Last </a>";
Pagestr + + "<span>" + page + "/" + totalpages + "page, total" + totalrecords + "</span></div>";
HTML + pagestr;
}
catch (Exception ex) {html = "Exception error:" + ex. ToString (); }
finally {Conn. Close (); }
return HTML;
}
}
|
Stored Procedure Paging method
OK, we first create a database: Data_test, and create a table in this database: tb_testtable
1create Database Data_test--Creating databases Data_test
2GO
3use Data_test
4GO
5create Table Tb_testtable--Creating tables
6 (
ID int identity (1,1) primary key,
UserName nvarchar is not NULL,
Userpwd nvarchar is not NULL,
UserEmail nvarchar () null
11)
12GO
Then we insert 2 million data into the datasheet:
1--Insert Data
2set Identity_insert tb_testtable on
3declare @count int
4set @count =1
5while @count <=2000000
6begin
Insert into tb_testtable (Id,username,userpwd,useremail) VALUES (@count, ' admin ', ' admin888 ', ' lli0077@yahoo.com.cn ')
Set @count = @count +1
9end
10set Identity_insert tb_testtable off
Here's a 2-point code using Select Max, which is pretty well established.
| The code is as follows |
Copy Code |
| 1--/*-----Stored Procedure paging Sun Wei 2005-03-28 create-------* *
2--/*-----Stored Procedures Paging process dust 2008-9-1 modified----------* *
3--/*-----2-point data processing so that the first half of the query data and query the second half of the data performance of the same-------* *
5alter PROCEDURE Proc_paged_2part_selectmax
6 (
7@tblname nvarchar,----a connection to a table or tables to display
8@fldname nvarchar = ' * ',----list of fields to display
9@pagesize int = Ten,----the number of records displayed per page
10@page int = 1,----to display the record for that page
11@fldsort nvarchar = null,----sort field list or condition
12@sort bit = 0,----Sort method, 0 is ascending, 1 is descending (if a multiple-field arrangement sort refers to the order of the last sorted field (the last sort field is not sorted)--Program reference: ' Sorta ASC,SORTB Des C,SORTC ')
13@strcondition nvarchar (1000) = NULL,----query criteria, no where
14@id nvarchar,----primary key for primary table
15@dist bit = 0,----whether to add a query field DISTINCT default 0 does not add/1 add
16@pagecount int = 1 OUTPUT,----Total number of pages after the query results are paginated
17@counts int = 1 output----The number of records queried
18)
19AS
20SET NOCOUNT on
21Declare @sqlTmp nvarchar (1000)----to store dynamically generated SQL statements
22Declare @strTmp nvarchar (1000)----A query statement that holds the total number of query results
23Declare @strID nvarchar (1000)----A query that holds the ID at the beginning or end of a query
25Declare @strSortType nvarchar----data collation a
26Declare @strFSortType nvarchar----data collation B
28Declare @SqlSelect nvarchar----SQL construction of queries containing distinct
29Declare @SqlCounts nvarchar ()----SQL construction of the total number of queries containing distinct
31declare @timediff datetime-time-consuming test time lag
32select @timediff =getdate ()
34if @Dist = 0
35begin
Set @SqlSelect = ' SELECT '
Set @SqlCounts = ' Count (*) '
38end
39else
40begin
Set @SqlSelect = ' SELECT distinct '
Set @SqlCounts = ' Count (DISTINCT ' + @ID + ') '
43end
45
46if @Sort =0
47begin
Set @strFSortType = ' ASC '
Set @strSortType = ' DESC '
50end
51else
52begin
Set @strFSortType = ' DESC '
Set @strSortType = ' ASC '
55end
57
--------Generate query Statements--------
60--here @strtmp the statement to get the number of query results
61if @strCondition is null or @strCondition = '--no display condition set
62begin
Set @sqlTmp = @fldName + ' from ' + @tblName
Set @strTmp = @SqlSelect + ' @Counts = ' + @SqlCounts + ' from ' + @tblName
Set @strID = ' from ' + @tblName
66end
67else
68begin
Set @sqlTmp = + @fldName + ' from ' + @tblName + ' WHERE (1>0) ' + @strCondition
Set @strTmp = @SqlSelect + ' @Counts = ' + @SqlCounts + ' from ' + @tblName + ' WHERE (1>0) ' + @strCondition
Set @strID = ' from ' + @tblName + ' WHERE (1>0) ' + @strCondition
72end
The total number of query results obtained by---------
75exec sp_executesql @strTmp, N ' @Counts int out ', @Counts out
76declare @tmpCounts int
77if @Counts = 0
Set @tmpCounts = 1
79else
Set @tmpCounts = @Counts
82--Total paging
Set @pageCount = (@tmpCounts + @pageSize-1)/@pageSize
85/**//**//**//** The current page is greater than the total number of pages to take the last page **/
If @page > @pageCount
Set @page = @pageCount
--/*-----Data Paging 2-------* *
DECLARE @pageIndex INT--Total/page size
DECLARE @lastcount INT--Total% page size
The Set @pageIndex = @tmpCounts/@pageSize
Set @lastcount = @tmpCounts% @pageSize
If @lastcount > 0
Set @pageIndex = @pageIndex + 1
Else
Set @lastcount = @pagesize
100--//*** Display Paging
If @strCondition is null or @strCondition = '--no display condition set
Begin
If @pageIndex <2 or @page <= @pageIndex/2 + @pageIndex% 2--data processing in the first half
Begin
If @page =1
Set @strTmp = @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR (4)) + "+ @fldName + ' from ' + @tblName
+ ' ORDER BY ' + @fldSort + ' + @strFSortType
Else
Begin
If @Sort =1
Begin
Set @strTmp = @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR (4)) + "+ @fldName + ' from ' + @tblName
+ ' where ' + @ID + ' < (select min (' + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-1) as Varchar) + ' + @i D + ' from ' + @tblName
+ ' ORDER BY ' + @fldSort + ' + @strFSortType + ') as Tbminid '
+ ' ORDER BY ' + @fldSort + ' + @strFSortType
End
Else
Begin
Set @strTmp = @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR (4)) + "+ @fldName + ' from ' + @tblName
+ ' where ' + @ID + ' > (select max (' + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-1) as Varchar) + ' + @i D + ' from ' + @tblName
+ ' ORDER BY ' + @fldSort + ' + @strFSortType + ') as Tbminid '
+ ' ORDER BY ' + @fldSort + ' + @strFSortType
End
End
End
Else
Begin
Set @page = @pageIndex-@page +1--the latter half of data processing
If @page <= 1--last page data display
Set @strTmp = @SqlSelect + ' * FROM ("+ @SqlSelect + ' top" + CAST (@lastcount as VARCHAR (4)) + ' + @fldName + ' from ' + @tblName
+ ' ORDER BY ' + @fldSort + ' + @strSortType + '] as TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @strFSortType
Else
If @Sort =1
Begin
Set @strTmp = @SqlSelect + ' * FROM ("+ @SqlSelect + ' top" + CAST (@pageSize as VARCHAR (4)) + ' + @fldName + ' from ' + @tblName
+ ' where ' + @ID + ' > (select max (' + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-2) + @lastcount as Varchar (20) + ' + @ID + ' from ' + @tblName
+ ' ORDER BY ' + @fldSort + ' + @strSortType + ') as Tbmaxid '
+ ' ORDER BY ' + @fldSort + ' + @strSortType + '] as TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @strFSortType
End
Else
Begin
Set @strTmp = @SqlSelect + ' * FROM ("+ @SqlSelect + ' top" + CAST (@pageSize as VARCHAR (4)) + ' + @fldName + ' from ' + @tblName
+ ' where ' + @ID + ' < (select min (' + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-2) + @lastcount as Varchar (20) + ' + @ID + ' from ' + @tblName
+ ' ORDER BY ' + @fldSort + ' + @strSortType + ') as Tbmaxid '
+ ' ORDER BY ' + @fldSort + ' + @strSortType + '] as TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @strFSortType
End
End
End
The other--there are query conditions
Begin
If @pageIndex <2 or @page <= @pageIndex/2 + @pageIndex% 2--data processing in the first half
Begin
If @page =1
Set @strTmp = @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR (4)) + "+ @fldName + ' from ' + @tblName
+ ' where 1=1 ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strFSortType
else if (@Sort =1)
Begin
Set @strTmp = @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR (4)) + "+ @fldName + ' from ' + @tblName
+ ' where ' + @ID + ' < (select min (' + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-1) as Varchar) + ' + @i D + ' from ' + @tblName
+ ' WHERE (1=1) ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strFSortType + ') as Tbminid '
+ ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strFSortType
End
Else
Begin
Set @strTmp = @SqlSelect + ' top ' + CAST (@pageSize as VARCHAR (4)) + "+ @fldName + ' from ' + @tblName
+ ' where ' + @ID + ' > (select max (' + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-1) as Varchar) + ' + @i D + ' from ' + @tblName
+ ' WHERE (1=1) ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strFSortType + ') as Tbminid '
+ ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strFSortType
End
End
Else
Begin
Set @page = @pageIndex-@page +1--the latter half of data processing
If @page <= 1--last page data display
Set @strTmp = @SqlSelect + ' * FROM ("+ @SqlSelect + ' top" + CAST (@lastcount as VARCHAR (4)) + ' + @fldName + ' from ' + @tblName
+ ' WHERE (1=1) ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strSortType + ') as TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @str Fsorttype
else if (@Sort =1)
Set @strTmp = @SqlSelect + ' * FROM ("+ @SqlSelect + ' top" + CAST (@pageSize as VARCHAR (4)) + ' + @fldName + ' from ' + @tblName
+ ' where ' + @ID + ' > (select max (' + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-2) + @lastcount as Varchar (20) + ' + @ID + ' from ' + @tblName
+ ' WHERE (1=1) ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strSortType + ') as Tbmaxid '
+ ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strSortType + '] as TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @strFSortType
Else
Set @strTmp = @SqlSelect + ' * FROM ("+ @SqlSelect + ' top" + CAST (@pageSize as VARCHAR (4)) + ' + @fldName + ' from ' + @tblName
+ ' where ' + @ID + ' < (select min (' + @ID + ') from (' + @SqlSelect + ' top ' + CAST (@pageSize * (@page-2) + @lastcount as Varchar (20) + ' + @ID + ' from ' + @tblName
+ ' WHERE (1=1) ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strSortType + ') as Tbmaxid '
+ ' + @strCondition + ' ORDER BY ' + @fldSort + ' + @strSortType + '] as TEMPTB ' + ' ORDER BY ' + @fldSort + ' + @strFSortType
End
End
191------Return Query Results-----
192exec sp_executesql @strTmp
193select DateDiff (MS, @timediff, GETDATE ()) as time consuming
194--print @strTmp
195SET NOCOUNT off
196GO |
