GridView自界說分頁的四種存儲進程。本站提示廣大學習愛好者：（GridView自界說分頁的四種存儲進程）文章只能為提供參考，不一定能成為您想要的結果。以下是GridView自界說分頁的四種存儲進程正文
1. 為何不應用GridView的默許分頁功效
起首要說說為何不消GridView的默許的分頁功效，GridView控件並不是真正曉得若何取得一個新頁面，它只是要求綁定的數據源控件前往合適劃定頁面的行，分頁終究是由數據源控件完成。當我們應用SqlDataSource或應用以上的代碼處置分頁時。每次這個頁面被要求或許回發時，一切和這個SELECT語句婚配的記載都被讀取並存儲到一個外部的DataSet中，但只顯示合適以後頁面年夜小的記載數。也就是說有能夠應用Select語句前往1000000筆記錄，而每次回發只顯示10筆記錄。假如啟用了SqlDataSource上的緩存，經由過程把EnableCaching設置為true，則情形會更好一些。在這類情形下，我們只須拜訪一次數據庫辦事器，全部數據集只加載一次，並在指定的刻日內存儲在ASP.NET緩存中。只需數據堅持緩存狀況，顯示任何頁面將不必再次拜訪數據庫辦事器。但是，能夠有年夜量數據存儲在內存中，換而言之，Web辦事器的壓力年夜年夜的增長了。是以，假如要應用SqlDataSource來獲得較小的數據時，GridView內建的主動分頁能夠足夠高效了，但關於年夜數據量來講是不適合的。
2. 分頁的四種存儲進程(分頁+排序的版本請參考Blog裡其他文章)
在年夜多半情形下我們應用存儲進程來停止分頁，明天有空總結了一下應用存儲進程對GridView停止分頁的4種寫法(分離是應用Top症結字，暫時表，暫時表變量和SQL Server 2005 新加的Row_Number()函數)
後續的文章中還將觸及GridView控件應用ObjectDataSource自界說分頁 + 排序，Repeater控件自界說分頁 + 排序，有興致的同伙可以參考。
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.應用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.應用暫時表
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 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
)
-- 填充暫時表
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.應用表變量
/*
為要分頁的數據創立一個table變量,這個table變量裡有一個作為主健的IDENTITY列.如許須要分頁的每筆記錄在table變量裡就和一個row index(經由過程IDENTITY列)聯系關系起來了.一旦table變量發生,銜接數據庫表的SELECT語句就被履行,獲得須要的記載.SET ROWCOUNT用來限制放到table變量裡的記載的數目.
當SET ROWCOUNT的值指定為PageNumber * PageSize時,這個辦法的效力取決於被要求的頁數.關於比擬後面的頁來講– 好比開端幾頁的數據– 這類辦法異常有用. 然則對接近尾部的頁來講,這類辦法的效力和默許分頁時差不多
*/
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
--在前往指定的行數以後停滯處置查詢
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.應用row_number函數
--SQL Server 2005的新特征,它可以將記載依據必定的次序分列,每筆記錄和一個品級相干 這個品級可以用來作為每筆記錄的row index.
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. 在GridView中的運用
<%@ 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>
轉到第<asp:DropDownList ID="dropPage" runat="server" AutoPostBack="True" OnSelectedIndexChanged="dropPage_SelectedIndexChanged"></asp:DropDownList>頁
<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="SqlDataSource1_Selecting" OnSelected="SqlDataSource1_Selected">
<SelectParameters>
<asp:Parameter Name="PageNumber" Type="Int32" />
<asp:Parameter Name="PageSize" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
<%@ 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>
轉到第<asp:DropDownList ID="dropPage" runat="server" AutoPostBack="True" OnSelectedIndexChanged="dropPage_SelectedIndexChanged"></asp:DropDownList>頁
<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="SqlDataSource1_Selecting" OnSelected="SqlDataSource1_Selected">
<SelectParameters>
<asp:Parameter Name="PageNumber" Type="Int32" />
<asp:Parameter Name="PageSize" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
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
{
//每頁顯示的最多記載的條數
private int pageSize = 10;
//以後頁號
private int currentPageNumber;
//顯示數據的總條數
private static int rowCount;
//總頁數
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 = "共找到" + rowCount + "筆記錄, 以後第" + currentPageNumber + "/" + pageCount + "頁";
}
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
{
//每頁顯示的最多記載的條數
private int pageSize = 10;
//以後頁號
private int currentPageNumber;
//顯示數據的總條數
private static int rowCount;
//總頁數
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 = "共找到" + rowCount + "筆記錄, 以後第" + currentPageNumber + "/" + pageCount + "頁";
}
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.分頁後果圖:
