GridView four stored procedures for customizing pagination _mssql

1. Why not use the default paging feature of the GridView

First of all, let's talk about why the GridView control does not really know how to get a new page without the default paging functionality of the GridView, it simply requests the bound data source control to return the rows that fit the specified page, and the paging is ultimately done by the data source control. When we use SqlDataSource or use the above code to process pagination. Every time this page is requested or sent back, all records that match the SELECT statement are read and stored in an internal dataset, but only the number of records appropriate to the current page size is displayed. That is, it is possible to use the SELECT statement to return 1 million records, and only 10 records are displayed for each postback. If caching on SqlDataSource is enabled, it is better to set the enablecaching to true. In this case, we only have to access the database server once, the entire dataset is loaded once and stored in the ASP.net cache for a specified period of time. As long as the data remains cached, displaying any page will not require access to the database server again. However, there may be a large amount of data stored in memory, in other words, the Web server's pressure greatly increased. Therefore, if you want to use SqlDataSource to get smaller data, the automatic pagination built in the GridView may be efficient enough, but it is not appropriate for large amounts of data.

2. Paging four kinds of stored procedures (pagination + sorted version Please refer to other articles in the blog)

In most cases we use stored procedures for paging, and today we are free to summarize the 4 ways in which the GridView is paginated using stored procedures (using the top keyword, temporary tables, temporary table variables, and SQL Server 2005 New Row_number () functions)

Subsequent articles will also involve the GridView control using ObjectDataSource Custom paging + sorting, Repeater control custom paging + sorting, interested friends can refer to.

Copy Code code 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 the 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 the products where ProductID not in (select top ' + Convert (varchar, (@PageNumber-1) * @PageSize) + ' ProductID F ROM products) '
EXEC sp_executesql @sql
Go

--exec Getproductsbypage 1, 10
--exec Getproductsbypage 5, 10

--2. Using temporary tables
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
)
--Populating temporary tables
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. Using Table variables
/*
Create a table variable for the data to be paginated, which has an identity column as the primary key. Each record that requires paging is associated with a row index (through the identity column) in the table variable. Once the table variable is generated, A SELECT statement that connects to a database table is executed to obtain the required records. SET rowcount is used to limit the number of records placed in a table variable.
When the value of a SET ROWCOUNT is specified as PageNumber * pagesize, the efficiency of this method depends on the number of pages requested. For comparison to the previous page-for example, to start a few pages-this method works very well. But for a page close to the end, the efficiency of this method is almost the same as the default paging
*/
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
--Stops processing the query after returning the specified number of rows
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. Using the Row_number function
The new feature of--sql Server 2005, which allows records to be sorted in a certain order, with each record and one level associated with this level can be used as row index for 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 (ProductID) as Row,productid, ProductName, SupplierID, CategoryID, QuantityPerUnit, Uni Tprice, 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 Code code 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 ">

<title>Paging</title>
<body>
<form id= "Form1" runat= "Server" >
<div>
<asp:linkbutton id= "Lbtnfirst" runat= "Server" Commandname= "a" 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 <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=" Selectcommandtype= "StoredProcedure" onselecting= "sqldatasource1_selecting" onselected= "SqlDataSource1_Selected" >
<SelectParameters>
<asp:parameter name= "pagenumber" type= "Int32"/>
<asp:parameter name= "PageSize" type= "Int32"/>
</SelectParameters>
</asp:SqlDataSource>
</div>
</form>
</body>

Copy Code code 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 ">

<title>Paging</title>
<body>
<form id= "Form1" runat= "Server" >
<div>
<asp:linkbutton id= "Lbtnfirst" runat= "Server" Commandname= "a" 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 <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=" Selectcommandtype= "StoredProcedure" onselecting= "sqldatasource1_selecting" onselected= "SqlDataSource1_Selected" >
<SelectParameters>
<asp:parameter name= "pagenumber" type= "Int32"/>
<asp:parameter name= "PageSize" type= "Int32"/>
</SelectParameters>
</asp:SqlDataSource>
</div>
</form>
</body>

Copy Code code 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
{
The maximum number of records displayed per page
private int pageSize = 10;
Current page number
private int currentpagenumber;
Total number of bars showing data
private static int rowcount;
Total 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 sqldatasource1_selected (object sender, SqlDataSourceStatusEventArgs e)
{
Lblmessage.text = "Find" + RowCount + "record, current first" + Currentpagenumber + "/" + PageCount + "page";
}

protected void Lbtnpage_command (object sender, CommandEventArgs e)
{
Switch (e.commandname)
{
Case "a":
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
{
The maximum number of records displayed per page
private int pageSize = 10;
Current page number
private int currentpagenumber;
Total number of bars showing data
private static int rowcount;
Total 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 sqldatasource1_selected (object sender, SqlDataSourceStatusEventArgs e)
{
Lblmessage.text = "Find" + RowCount + "record, current first" + Currentpagenumber + "/" + PageCount + "page";
}

protected void Lbtnpage_command (object sender, CommandEventArgs e)
{
Switch (e.commandname)
{
Case "a":
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 Effect Chart: