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 = apos;GetProductsCountapos; and type = apos;Papos;)
drop proc GetProductsCount
go
CREATE PROCEDURE GetProductsCount
as
select count(*) from products
go
--1.使用Top
if exists(select 1 from sys.objects where name = apos;GetProductsByPageapos; and type = apos;Papos;)
drop proc GetProductsByPage
go
CREATE PROCEDURE GetProductsByPage
@PageNumber int,
@PageSize int
AS
declare @sql nvarchar(4000)
set @sql = apos;select top apos; + Convert(varchar, @PageSize)
+ apos; * from products where productid not in (select top apos; + Convert(varchar, (@PageNumber - 1) * @PageSize) + apos; productid from products)apos;
exec sp_executesql @sql
go
--exec GetProductsByPage 1, 10
--exec GetProductsByPage 5, 10
--2.使用臨時表
if exists(select 1 from sys.objects where name = apos;GetProductsByPageapos; and type = apos;Papos;)
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 = apos;GetProductsByPageapos; and type = apos;Papos;)
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 = apos;GetProductsByPageapos; and type = apos;Papos;)
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.分頁效果圖:
您可能感興趣的文章:- GridView分頁的實現以及自定義分頁樣式功能實例
- C#自定義DataGridViewColumn顯示TreeView
- yii2.0之GridView自定義按鈕和鏈接用法
- GridView自定義刪除操作的具體方法
- 自定義GridView并且實現拖拽(附源碼)
- asp.net gridview自定義value值的代碼
- asp.net gridview分頁：第一頁 下一頁 1 2 3 4 上一頁 最末頁
- asp.net中的GridView分頁問題
- Android入門之ActivityGroup+GridView實現Tab分頁標簽的方法
- asp.net Gridview分頁保存選項
- 基于GridView和ActivityGroup實現的TAB分頁(附源碼)
- GridView自定義分頁實例詳解(附demo源碼下載)
