First of all, the implementation of the way:
1, let's assume that there is a primary key field ID (integer) in the table that has already been indexed, and we'll follow this field to get the data paginated.
2, the size of the page we put in the @pagesize
3, the current page number we put in the @currentpage
4, how to let the record pointer quickly scroll to the data we want to start the line, this is the key! With SET ROWCOUNT, we are easy to implement.
5. If we successfully scroll the record pointer to the line at the beginning of the data we want to fetch, and then we record the value of the ID field for that line of records, then, with top and condition, we can easily get the data for the specified page. Of course, with SET ROWCOUNT, do we still use top?
Look at the SET rowcount how to help us:
Copy Code code as follows:
Declare @ID int
Declare @MoveRecords int
--@CurrentPage and @pagesize are incoming parameters
Set @MoveRecords = @CurrentPage * @PageSize +1
--The following two lines implement the row to quickly scroll to the data we want to fetch and record the ID
Set RowCount @MoveRecords
Select @ID =id from Table1 order by ID
Set RowCount @PageSize
-Hate to reduce trouble to use *, but here to illustrate the convenience, temporarily use
Select * from Table1 Where id>= @ID the ORDER by ID
Set ROWCOUNT 0
You can try, in a 1000 W record of the table, all of a sudden page to page 100th (100 per page), see how fast!
Because the usual reverse row of more, above also very good change.
Change the order by ID to order by ID DESC
Change the Where id>= @ID order by ID to where id<= @ID order by ID DESC
It's OK.
Copy Code code as follows:
Set ANSI_NULLS on
SET QUOTED_IDENTIFIER ON
Go
Create PROCEDURE [dbo]. [Tag_page_name_select]
--Incoming maximum number of display records and current page number
@MaxPageSize int,
@PageNum int,
--Set an output parameter to return the total number of records for paging list use
@Count int Output
As
BEGIN
SET NOCOUNT on;
DECLARE
--Define Sort name parameters
@Name nvarchar (50),
--Define cursor position
@Cursor int
--First get the total number of records
Select @Count = Count (tag_name)
from [viewdatabase0716]. [dbo]. [View_tag];
--Define where the cursor needs to start
Set @Cursor = @MaxPageSize * (@PageNum-1) +1
--If the cursor is greater than the total number of records place the cursor at the beginning of the last page
IF @Cursor > @Count
BEGIN
--Returns the last full page if the last page is equal to the maximum number of records per record
IF @Count% @MaxPageSize = 0
BEGIN
IF @Cursor > @MaxPageSize
Set @Cursor = @Count-@MaxPageSize + 1
ELSE
Set @Cursor = 1
End
--otherwise return the last page of the remaining record
ELSE
Set @Cursor = @Count-(@Count% @MaxPageSize) + 1
End
--point the pointer at the beginning of the page
Set RowCount @Cursor
--Where the record starts
Select @Name = tag_name
from [viewdatabase0716]. [dbo]. [View_tag]
Order by Tag_name;
--Set Start position
Set RowCount @MaxPageSize
--Get the page record
Select *
from [viewdatabase0716]. [dbo]. [View_tag]
Where tag_name >= @Name
ORDER BY Tag_name
Set ROWCOUNT 0
End
Page-Paging controls
Copy Code code as follows:
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.Text;
<summary>
Extended connection string
</summary>
public class Exstringbuilder
{
Private StringBuilder insertstring;
Private StringBuilder pagestring;
private int privatepagenum = 1;
private int privatemaxpagesize = 25;
private int privatemaxpages = 10;
private int privatecount;
private int privateallpage;
Public Exstringbuilder ()
{
insertstring = new StringBuilder ("");
}
<summary>
Get the generated HTML
</summary>
public string gethtml
{
Get
{
return insertstring.tostring ();
}
}
<summary>
Get the generated paging HTML
</summary>
public string getpagehtml
{
Get
{
return pagestring.tostring ();
}
}
<summary>
Set or get the current number of pages
</summary>
public int Pagenum
{
Get
{
return privatepagenum;
}
Set
{
if (value >= 1)
{
Privatepagenum = value;
}
}
}
<summary>
Set or get the maximum number of pages
</summary>
public int MaxPageSize
{
Get
{
return privatemaxpagesize;
}
Set
{
if (value >= 1)
{
Privatemaxpagesize = value;
}
}
}
<summary>
Sets or gets the maximum number of pages per display
</summary>
public int Maxpages
{
Get
{
return privatemaxpages;
}
Set
{
Privatemaxpages = value;
}
}
<summary>
Set or get Total data
</summary>
public int Datecount
{
Get
{
return privatecount;
}
Set
{
Privatecount = value;
}
}
<summary>
Get total pages of data
</summary>
public int Allpage
{
Get
{
return privateallpage;
}
}
<summary>
Initializing pagination
</summary>
public void pagination ()
{
pagestring = new StringBuilder ("");
Get Total Pages
Privateallpage = (int) math.ceiling ((decimal) Privatecount/(decimal) privatemaxpagesize);
Prevent superscript or subscript from crossing the border
if (Privatepagenum > Privateallpage)
{
Privatepagenum = Privateallpage;
}
Scrolling cursor page-splitting method
int Leftrange, Rightrange, Leftstart, rightend;
Leftrange = (privatemaxpages + 1)/2-1;
Rightrange = (privatemaxpages + 1)/2;
if (privatemaxpages >= privateallpage)
{
Leftstart = 1;
Rightend = Privateallpage;
}
Else
{
if (Privatepagenum <= leftrange)
{
Leftstart = 1;
Rightend = Leftstart + PrivateMaxPages-1;
}
else if (Privateallpage-privatepagenum < Rightrange)
{
Rightend = Privateallpage;
Leftstart = rightend-privatemaxpages + 1;
}
Else
{
Leftstart = Privatepagenum-leftrange;
Rightend = Privatepagenum + rightrange;
}
}
Generate page Number list statistics
Pagestring.append (...);
StringBuilder previousstring = new StringBuilder ("");
If on the first page
if (Privatepagenum > 1)
{
...
}
Else
{
...
}
If on the first component page
if (Privatepagenum > Privatemaxpages)
{
...
}
Else
{
...
}
Pagestring.append (previousstring);
Build an intermediate page
for (int i = Leftstart i <= rightend; i++)
{
When the current page
if (i = = Privatepagenum)
{
...
}
Else
{
...
}
}
StringBuilder laststring = new StringBuilder ("");
If on the last page
if (Privatepagenum < privateallpage)
{
...
}
Else
{
...
}
If the last group
if ((Privatepagenum + privatemaxpages) < Privateallpage)
{
...
}
Else
{
...
}
Pagestring.append (laststring);
}
<summary>
Generate Tag Category table
</summary>
public void tagtable (Exdatarow myexdatarow)
{
Insertstring.append (...);
}
Call Method:
Get paging settings and insert session
Exrequest myexrequest = new Exrequest ();
Myexrequest.pagesession ("Tag_", new string[] {"page", "Size"});
Generate Tag Paging
Exstringbuilder Tag = new Exstringbuilder ();
Set how many records to display each time
Tag.maxpagesize = Convert.ToInt32 (session["tag_size"));
Set the maximum number of page numbers to display
Tag.maxpages = 9;
Set the current page
Tag.pagenum = Convert.ToInt32 (session["Tag_page"));
string[][] Mynamenvalue = new string[2][]{
New string[]{"MaxPageSize", "Pagenum", "Count"},
New String[]{tag.maxpagesize.tostring (), Tag.PageNum.ToString ()}
};
Calling stored procedures
DataTable mydatatable = Mysql.batchgetdb ("Tag_page_name_select", Mynamenvalue, "Count");
Tag.datecount = (int) mysql.outputcommand.parameters["@Count"]. Value;
Tag.pagination ();
headpage.innerhtml = footpage.innerhtml = tag.getpagehtml;
for (int i = 0, j = MyDataTable.Rows.Count I < J; i++)
{
Tag.tagtable (New Exdatarow (mydatatable.rows[i));
}
tagbox.innerhtml = tag.gethtml;
