Four stored procedures for custom pagination in GridView

1. Why not use the default page function of the GridView?

First, let's talk about why the default paging function of the GridView is not used. The GridView control does not really know how to obtain a new page. It only requests the bound data source control to return rows suitable for the specified page, the Data Source Control completes paging. When we use SqlDataSource or the above Code to process pages. Every time this page is requested or sent back, all records matching this SELECT statement are read and stored in an internal DataSet, however, only the number of records that are suitable for the current page size is displayed. That is to say, it is possible to use the Select statement to return 1000000 records, and only 10 records are displayed for each return. If the cache on SqlDataSource is enabled, it will be better if you set EnableCaching to true. In this case, we only need to access the database server once, the entire dataset is loaded only once, and stored in the ASP. NET cache within the specified period of time. As long as the data remains cached, no page is displayed and you do not need to access the database server again. However, a large amount of data may be stored in the memory. In other words, the pressure on Web servers is greatly increased. Therefore, if you want to use SqlDataSource to obtain small data, the built-in automatic paging in the GridView may be efficient enough, but it is not suitable for large data volumes.

2. Four paging stored procedures (for paging + sorting versions, refer to other articles in the Blog)

In most cases, we use stored procedures for paging. Today we are free to summarize the four types of paging statements for the GridView using stored procedures (Top keywords, temporary tables, temporary table variables and the Row_Number () function newly added by SQL Server 2005)

The subsequent articles will also involve the use of ObjectDataSource custom page + sorting and Repeater control custom page + sorting. For more information, see.
Copy codeThe Code is as follows:
If exists (select 1 from sys. objects where name = 'getproductscount 'and type = 'P ')
Drop proc GetProductsCount
Go
Create procedure GetProductsCount
As
Select count (*) from products
Go

-- 1. Use Top
If exists (select 1 from sys. objects where name = 'getproductsbypage' and type = 'P ')
Drop proc GetProductsByPage
Go
Create procedure GetProductsByPage
@ PageNumber int,
@ PageSize int
AS
Declare @ SQL nvarchar (4000)
Set @ SQL = 'select top '+ Convert (varchar, @ PageSize)
+ '* From products where productid not in (select top' + Convert (varchar, (@ PageNumber-1) * @ PageSize) + 'productid from products )'
Exec sp_executesql @ SQL
Go

-- Exec GetProductsByPage 1, 10
-- Exec GetProductsByPage 5, 10

-- 2. Use a temporary table
If exists (select 1 from sys. objects where name = 'getproductsbypage' and type = 'P ')
Drop proc GetProductsByPage
Go
Create procedure GetProductsByPage
@ PageNumber int,
@ PageSize int
AS
-- Create a temporary table
Create table # TempProducts
(
ID int identity primary key,
ProductID int,
ProductName varchar (40 ),
SupplierID int,
CategoryID int,
QuantityPerUnit nvarchar (20 ),
UnitPrice money,
UnitsInStock smallint,
UnitsOnOrder smallint,
ReorderLevel smallint,
Discontinued bit
)
-- Fill temporary table
Insert into # TempProducts
(ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products

DECLARE @ FromID int
DECLARE @ ToID int
SET @ FromID = (@ PageNumber-1) * @ PageSize) + 1
SET @ ToID = @ PageNumber * @ PageSize

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM # TempProducts
Where id >=@ FromID and id <= @ ToID
Go

-- Exec GetProductsByPage 1, 10
-- Exec GetProductsByPage 5, 10

-- 3. Use table Variables
/*
Create a table variable for the data to be paged. This table variable contains an IDENTITY column as the primary key. in this way, each record on the page needs to be associated with a row index (through the IDENTITY column) in the table variable. once the table variable is generated, the SELECT statement connecting to the database table is executed to obtain the required records. set rowcount is used to limit the number of records placed in the table variable.
When the set rowcount value is SET to PageNumber * PageSize, the efficiency of this method depends on the number of pages requested. this method is very effective for comparing the previous page-for example, the data on the first few pages. however, for pages close to the end, the efficiency of this method is similar to that of default pages.
*/
If exists (select 1 from sys. objects where name = 'getproductsbypage' and type = 'P ')
Drop proc GetProductsByPage
Go
Create procedure GetProductsByPage
@ PageNumber int,
@ PageSize int
AS
DECLARE @ TempProducts TABLE
(
ID int IDENTITY,
Productid int
)
DECLARE @ maxRows int
SET @ maxRows = @ PageNumber * @ PageSize
-- Stop processing the query after the specified number of rows is returned
Set rowcount @ maxRows

Insert into @ TempProducts (productid)
SELECT productid
FROM products
Order by productid

Set rowcount @ PageSize

SELECT p .*
FROM @ TempProducts t inner join products p
ON t. productid = p. productid
Where id> (@ PageNumber-1) * @ PageSize
Set rowcount 0
GO

-- Exec GetProductsByPage 1, 10
-- Exec GetProductsByPage 5, 10

-- 4. Use the row_number Function
-- New Features of SQL Server 2005, which can sort records in a certain order. Each record and a level can be used as the row index of each record.
If exists (select 1 from sys. objects where name = 'getproductsbypage' and type = 'P ')
Drop proc GetProductsByPage
Go
Create procedure GetProductsByPage
@ PageNumber int,
@ PageSize int
AS
Select ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
From
(Select row_number () Over (order by productid) as row, ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
From products) as ProductsWithRowNumber
Where row between (@ PageNumber-1) * @ PageSize + 1 and @ PageNumber * @ PageSize
Go

-- Exec GetProductsByPage 1, 10
-- Exec GetProductsByPage 5, 10

3. Application in the GridView

Copy codeThe Code is as follows:
<% @ Page Language = "C #" AutoEventWireup = "true" CodeFile = "GridViewPaging. aspx. cs" Inherits = "GridViewPaging" %>

<! 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 </title>
</Head>
<Body>
<Form id = "form1" runat = "server">
<Div>
<Asp: LinkButton id = "lbtnFirst" runat = "server" CommandName = "First" OnCommand = "lbtnPage_Command" >|</asp: LinkButton>
<Asp: LinkButton id = "lbtnPrevious" runat = "server" CommandName = "Previous" OnCommand = "lbtnPage_Command"> </asp: LinkButton>
<Asp: Label id = "lblMessage" runat = "server"/>
<Asp: LinkButton id = "lbtnNext" runat = "server" CommandName = "Next" OnCommand = "lbtnPage_Command" >>></asp: LinkButton>
<Asp: LinkButton id = "lbtnLast" runat = "server" CommandName = "Last" OnCommand = "lbtnPage_Command" >>|</asp: LinkButton>
Go to the <asp: DropDownList ID = "dropPage" runat = "server" AutoPostBack = "True" OnSelectedIndexChanged = "dropPage_SelectedIndexChanged"> </asp: DropDownList> page
<Asp: GridView ID = "GridView1" runat = "server" AutoGenerateColumns = "False" DataKeyNames = "ProductID" DataSourceID = "SqlDataSource1">
<Columns>
<Asp: BoundField DataField = "ProductID" HeaderText = "ProductID" InsertVisible = "False" ReadOnly = "True"/>
<Asp: BoundField DataField = "ProductName" HeaderText = "ProductName"/>
<Asp: BoundField DataField = "SupplierID" HeaderText = "SupplierID"/>
<Asp: BoundField DataField = "CategoryID" HeaderText = "CategoryID"/>
<Asp: BoundField DataField = "QuantityPerUnit" HeaderText = "QuantityPerUnit"/>
<Asp: BoundField DataField = "UnitPrice" HeaderText = "UnitPrice"/>
<Asp: BoundField DataField = "UnitsInStock" HeaderText = "UnitsInStock"/>
<Asp: BoundField DataField = "UnitsOnOrder" HeaderText = "UnitsOnOrder"/>
<Asp: BoundField DataField = "ReorderLevel" HeaderText = "ReorderLevel"/>
<Asp: CheckBoxField DataField = "Discontinued" HeaderText = "Discontinued"/>
</Columns>
</Asp: GridView>
<Asp: SqlDataSource ID = "SqlDataSource1" runat = "server" ConnectionString = "Data Source =. \ sqlexpress; Initial Catalog = Northwind; Integrated Security = True "ProviderName =" System. data. sqlClient "SelectCommand =" GetProductsByPage "SelectCommandType =" StoredProcedure "OnSelecting =" sqldatasourceceselecting "OnSelected =" SqlDataSource1_Selected ">
<SelectParameters>
<Asp: Parameter Name = "PageNumber" Type = "Int32"/>
<Asp: Parameter Name = "PageSize" Type = "Int32"/>
</SelectParameters>
</Asp: SqlDataSource>
</Div>
</Form>
</Body>
</Html>

Copy codeThe Code is as follows:
<% @ Page Language = "C #" AutoEventWireup = "true" CodeFile = "GridViewPaging. aspx. cs" Inherits = "GridViewPaging" %>

<! 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 </title>
</Head>
<Body>
<Form id = "form1" runat = "server">
<Div>
<Asp: LinkButton id = "lbtnFirst" runat = "server" CommandName = "First" OnCommand = "lbtnPage_Command" >|</asp: LinkButton>
<Asp: LinkButton id = "lbtnPrevious" runat = "server" CommandName = "Previous" OnCommand = "lbtnPage_Command"> </asp: LinkButton>
<Asp: Label id = "lblMessage" runat = "server"/>
<Asp: LinkButton id = "lbtnNext" runat = "server" CommandName = "Next" OnCommand = "lbtnPage_Command" >>></asp: LinkButton>
<Asp: LinkButton id = "lbtnLast" runat = "server" CommandName = "Last" OnCommand = "lbtnPage_Command" >>|</asp: LinkButton>
Go to the <asp: DropDownList ID = "dropPage" runat = "server" AutoPostBack = "True" OnSelectedIndexChanged = "dropPage_SelectedIndexChanged"> </asp: DropDownList> page
<Asp: GridView ID = "GridView1" runat = "server" AutoGenerateColumns = "False" DataKeyNames = "ProductID" DataSourceID = "SqlDataSource1">
<Columns>
<Asp: BoundField DataField = "ProductID" HeaderText = "ProductID" InsertVisible = "False" ReadOnly = "True"/>
<Asp: BoundField DataField = "ProductName" HeaderText = "ProductName"/>
<Asp: BoundField DataField = "SupplierID" HeaderText = "SupplierID"/>
<Asp: BoundField DataField = "CategoryID" HeaderText = "CategoryID"/>
<Asp: BoundField DataField = "QuantityPerUnit" HeaderText = "QuantityPerUnit"/>
<Asp: BoundField DataField = "UnitPrice" HeaderText = "UnitPrice"/>
<Asp: BoundField DataField = "UnitsInStock" HeaderText = "UnitsInStock"/>
<Asp: BoundField DataField = "UnitsOnOrder" HeaderText = "UnitsOnOrder"/>
<Asp: BoundField DataField = "ReorderLevel" HeaderText = "ReorderLevel"/>
<Asp: CheckBoxField DataField = "Discontinued" HeaderText = "Discontinued"/>
</Columns>
</Asp: GridView>
<Asp: SqlDataSource ID = "SqlDataSource1" runat = "server" ConnectionString = "Data Source =. \ sqlexpress; Initial Catalog = Northwind; Integrated Security = True "ProviderName =" System. data. sqlClient "SelectCommand =" GetProductsByPage "SelectCommandType =" StoredProcedure "OnSelecting =" sqldatasourceceselecting "OnSelected =" SqlDataSource1_Selected ">
<SelectParameters>
<Asp: Parameter Name = "PageNumber" Type = "Int32"/>
<Asp: Parameter Name = "PageSize" Type = "Int32"/>
</SelectParameters>
</Asp: SqlDataSource>
</Div>
</Form>
</Body>
</Html>


Copy codeThe Code is as follows:
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 GridViewPaging: System. Web. UI. Page
{
// Maximum number of records displayed on each page
Private int pageSize = 10;
// Current page number
Private int currentPageNumber;
// Display the total number of data entries
Private static int rowCount;
// Total number of pages
Private static int pageCount;

Protected void Page_Load (object sender, EventArgs e)
{
If (! IsPostBack)
{
SqlConnection cn = new SqlConnection (System. Configuration. ConfigurationManager. ConnectionStrings ["NorthwindConnectionString"]. ConnectionString );

SqlCommand cmd = new SqlCommand ("GetProductsCount", cn );
Cmd. CommandType = CommandType. StoredProcedure;
Cn. Open ();
RowCount = (int) cmd. ExecuteScalar ();
Cn. Close ();
PageCount = (rowCount-1)/pageSize + 1;
CurrentPageNumber = 1;
ViewState ["currentPageNumber"] = currentPageNumber;
LbtnPrevious. Enabled = false;
LbtnFirst. Enabled = false;

For (int I = 1; I <= pageCount; I ++)
{
DropPage. Items. Add (new ListItem (I. ToString (), I. ToString ()));
}
DropPage. SelectedValue = dropPage. Items. FindByValue (currentPageNumber. ToString (). Value;
SqlDataSource1.Select (DataSourceSelectArguments. Empty );
}
}

Protected void SqlDataSource1_Selecting (object sender, SqlDataSourceSelectingEventArgs e)
{
SqlDataSource1.SelectParameters ["PageNumber"]. DefaultValue = currentPageNumber. ToString ();
SqlDataSource1.SelectParameters ["PageSize"]. DefaultValue = pageSize. ToString ();
}

Protected void sqldatasourceincluselected (object sender, SqlDataSourceStatusEventArgs e)
{
LblMessage. Text = "found" + rowCount + "records, current" + currentPageNumber + "/" + pageCount + "page ";
}

Protected void lbtnPage_Command (object sender, CommandEventArgs e)
{
Switch (e. CommandName)
{
Case "First ":
CurrentPageNumber = 1;
Break;
Case "Previous ":
CurrentPageNumber = (int) ViewState ["currentPageNumber"]-1> 1? (Int) ViewState ["currentPageNumber"]-1: 1;
Break;
Case "Next ":
CurrentPageNumber = (int) ViewState ["currentPageNumber"] + 1 <pageCount? (Int) ViewState ["currentPageNumber"] + 1: pageCount;
Break;
Case "Last ":
CurrentPageNumber = pageCount;
Break;
}
DropPage. SelectedValue = dropPage. Items. FindByValue (currentPageNumber. ToString (). Value;
ViewState ["currentPageNumber"] = currentPageNumber;
SetButton (currentPageNumber );
SqlDataSource1.Select (DataSourceSelectArguments. Empty );
}

Private void SetButton (int currentPageNumber)
{
LbtnFirst. Enabled = currentPageNumber! = 1;
LbtnPrevious. Enabled = currentPageNumber! = 1;
LbtnNext. Enabled = currentPageNumber! = PageCount;
LbtnLast. Enabled = currentPageNumber! = PageCount;
}

Protected void dropPage_SelectedIndexChanged (object sender, EventArgs e)
{
CurrentPageNumber = int. Parse (dropPage. SelectedValue );
ViewState ["currentPageNumber"] = currentPageNumber;
SetButton (currentPageNumber );
SqlDataSource1.Select (DataSourceSelectArguments. Empty );
}
}

[/Code]
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 GridViewPaging: System. Web. UI. Page
{
// Maximum number of records displayed on each page
Private int pageSize = 10;
// Current page number
Private int currentPageNumber;
// Display the total number of data entries
Private static int rowCount;
// Total number of pages
Private static int pageCount;

Protected void Page_Load (object sender, EventArgs e)
{
If (! IsPostBack)
{
SqlConnection cn = new SqlConnection (System. Configuration. ConfigurationManager. ConnectionStrings ["NorthwindConnectionString"]. ConnectionString );

SqlCommand cmd = new SqlCommand ("GetProductsCount", cn );
Cmd. CommandType = CommandType. StoredProcedure;
Cn. Open ();
RowCount = (int) cmd. ExecuteScalar ();
Cn. Close ();
PageCount = (rowCount-1)/pageSize + 1;
CurrentPageNumber = 1;
ViewState ["currentPageNumber"] = currentPageNumber;
LbtnPrevious. Enabled = false;
LbtnFirst. Enabled = false;

For (int I = 1; I <= pageCount; I ++)
{
DropPage. Items. Add (new ListItem (I. ToString (), I. ToString ()));
}
DropPage. SelectedValue = dropPage. Items. FindByValue (currentPageNumber. ToString (). Value;
SqlDataSource1.Select (DataSourceSelectArguments. Empty );
}
}

Protected void SqlDataSource1_Selecting (object sender, SqlDataSourceSelectingEventArgs e)
{
SqlDataSource1.SelectParameters ["PageNumber"]. DefaultValue = currentPageNumber. ToString ();
SqlDataSource1.SelectParameters ["PageSize"]. DefaultValue = pageSize. ToString ();
}

Protected void sqldatasourceincluselected (object sender, SqlDataSourceStatusEventArgs e)
{
LblMessage. Text = "found" + rowCount + "records, current" + currentPageNumber + "/" + pageCount + "page ";
}

Protected void lbtnPage_Command (object sender, CommandEventArgs e)
{
Switch (e. CommandName)
{
Case "First ":
CurrentPageNumber = 1;
Break;
Case "Previous ":
CurrentPageNumber = (int) ViewState ["currentPageNumber"]-1> 1? (Int) ViewState ["currentPageNumber"]-1: 1;
Break;
Case "Next ":
CurrentPageNumber = (int) ViewState ["currentPageNumber"] + 1 <pageCount? (Int) ViewState ["currentPageNumber"] + 1: pageCount;
Break;
Case "Last ":
CurrentPageNumber = pageCount;
Break;
}
DropPage. SelectedValue = dropPage. Items. FindByValue (currentPageNumber. ToString (). Value;
ViewState ["currentPageNumber"] = currentPageNumber;
SetButton (currentPageNumber );
SqlDataSource1.Select (DataSourceSelectArguments. Empty );
}

Private void SetButton (int currentPageNumber)
{
LbtnFirst. Enabled = currentPageNumber! = 1;
LbtnPrevious. Enabled = currentPageNumber! = 1;
LbtnNext. Enabled = currentPageNumber! = PageCount;
LbtnLast. Enabled = currentPageNumber! = PageCount;
}

Protected void dropPage_SelectedIndexChanged (object sender, EventArgs e)
{
CurrentPageNumber = int. Parse (dropPage. SelectedValue );
ViewState ["currentPageNumber"] = currentPageNumber;
SetButton (currentPageNumber );
SqlDataSource1.Select (DataSourceSelectArguments. Empty );
}
}
[/Code]
4. pagination: