GridView自定義分頁(yè)的四種存儲(chǔ)過程

347

首先要說說為什么不用GridView的默認(rèn)的分頁(yè)功能，GridView控件并非真正知道如何獲得一個(gè)新頁(yè)面，它只是請(qǐng)求綁定的數(shù)據(jù)源控件返回適合規(guī)定頁(yè)面的行，分頁(yè)最終是由數(shù)據(jù)源控件完成。

1. 為什么不使用GridView的默認(rèn)分頁(yè)功能

首先要說說為什么不用GridView的默認(rèn)的分頁(yè)功能，GridView控件并非真正知道如何獲得一個(gè)新頁(yè)面，它只是請(qǐng)求綁定的數(shù)據(jù)源控件返回適合規(guī)定頁(yè)面的行，分頁(yè)最終是由數(shù)據(jù)源控件完成。當(dāng)我們使用SqlDataSource或使用以上的代碼處理分頁(yè)時(shí)。每次這個(gè)頁(yè)面被請(qǐng)求或者回發(fā)時(shí)，所有和這個(gè)SELECT語(yǔ)句匹配的記錄都被讀取并存儲(chǔ)到一個(gè)內(nèi)部的DataSet中，但只顯示適合當(dāng)前頁(yè)面大小的記錄數(shù)。也就是說有可能使用Select語(yǔ)句返回1000000條記錄，而每次回發(fā)只顯示10條記錄。如果啟用了SqlDataSource上的緩存，通過把EnableCaching設(shè)置為true，則情況會(huì)更好一些。在這種情況下，我們只須訪問一次數(shù)據(jù)庫(kù)服務(wù)器，整個(gè)數(shù)據(jù)集只加載一次，并在指定的期限內(nèi)存儲(chǔ)在ASP.NET緩存中。只要數(shù)據(jù)保持緩存狀態(tài)，顯示任何頁(yè)面將無須再次訪問數(shù)據(jù)庫(kù)服務(wù)器。然而，可能有大量數(shù)據(jù)存儲(chǔ)在內(nèi)存中，換而言之，Web服務(wù)器的壓力大大的增加了。因此，如果要使用SqlDataSource來獲取較小的數(shù)據(jù)時(shí)，GridView內(nèi)建的自動(dòng)分頁(yè)可能足夠高效了，但對(duì)于大數(shù)據(jù)量來說是不合適的。

2. 分頁(yè)的四種存儲(chǔ)過程(分頁(yè)+排序的版本請(qǐng)參考Blog里其他文章)

在大多數(shù)情況下我們使用存儲(chǔ)過程來進(jìn)行分頁(yè)，今天有空總結(jié)了一下使用存儲(chǔ)過程對(duì)GridView進(jìn)行分頁(yè)的4種寫法(分別是使用Top關(guān)鍵字，臨時(shí)表，臨時(shí)表變量和SQL Server 2005 新加的Row_Number()函數(shù))

后續(xù)的文章中還將涉及GridView控件使用ObjectDataSource自定義分頁(yè) + 排序，Repeater控件自定義分頁(yè) + 排序，有興趣的朋友可以參考。
代碼如下:
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.使用臨時(shí)表
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
— 創(chuàng)建臨時(shí)表
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
)
— 填充臨時(shí)表
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 go

–exec GetProductsByPage 1, 10
–exec GetProductsByPage 5, 10

–3.使用表變量
/*
為要分頁(yè)的數(shù)據(jù)創(chuàng)建一個(gè)table變量,這個(gè)table變量里有一個(gè)作為主健的IDENTITY列.這樣需要分頁(yè)的每條記錄在table變量里就和一個(gè)row index(通過IDENTITY列)關(guān)聯(lián)起來了.一旦table變量產(chǎn)生,連接數(shù)據(jù)庫(kù)表的SELECT語(yǔ)句就被執(zhí)行,獲取需要的記錄.SET ROWCOUNT用來限制放到table變量里的記錄的數(shù)量.
當(dāng)SET ROWCOUNT的值指定為PageNumber * PageSize時(shí),這個(gè)方法的效率取決于被請(qǐng)求的頁(yè)數(shù).對(duì)于比較前面的頁(yè)來說– 比如開始幾頁(yè)的數(shù)據(jù)– 這種方法非常有效. 但是對(duì)接近尾部的頁(yè)來說,這種方法的效率和默認(rèn)分頁(yè)時(shí)差不多
*/
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
–在返回指定的行數(shù)之后停止處理查詢
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函數(shù)
–SQL Server 2005的新特性,它可以將記錄根據(jù)一定的順序排列,每條記錄和一個(gè)等級(jí)相關(guān) 這個(gè)等級(jí)可以用來作為每條記錄的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中的應(yīng)用

代碼如下:

Paging

| >>>|轉(zhuǎn)到第頁(yè)














代碼如下:

Paging

| >>>|轉(zhuǎn)到第頁(yè)














代碼如下:
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
{
//每頁(yè)顯示的最多記錄的條數(shù)
private int pageSize = 10;
//當(dāng)前頁(yè)號(hào)
private int currentPageNumber;
//顯示數(shù)據(jù)的總條數(shù)
private static int rowCount;
//總頁(yè)數(shù)
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 {
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 + “條記錄, 當(dāng)前第” + currentPageNumber + “/” + pageCount + “頁(yè)”;
}

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 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
{
//每頁(yè)顯示的最多記錄的條數(shù)
private int pageSize = 10;
//當(dāng)前頁(yè)號(hào)
private int currentPageNumber;
//顯示數(shù)據(jù)的總條數(shù)
private static int rowCount;
//總頁(yè)數(shù)
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 {
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 + “條記錄, 當(dāng)前第” + currentPageNumber + “/” + pageCount + “頁(yè)”;
}

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 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.分頁(yè)效果圖:

? 版權(quán)聲明
THE END
數(shù)據(jù)庫(kù)
# Collection
喜歡就支持一下吧
點(diǎn)贊7 分享