GridView中如何自定義分頁(yè)的存儲(chǔ)過(guò)程

143次閱讀

共計(jì) 13723 個(gè)字符，預(yù)計(jì)需要花費(fèi) 35 分鐘才能閱讀完成。

GridView 中如何自定義分頁(yè)的存儲(chǔ)過(guò)程，很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面丸趣 TV 小編將為大家詳細(xì)講解，有這方面需求的人可以來(lái)學(xué)習(xí)下，希望你能有所收獲。

1. 為什么不使用 GridView 的默認(rèn)分頁(yè)功能首先要說(shuō)說(shuō)為什么不用 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ù)。也就是說(shuō)有可能使用 Select 語(yǔ)句返回 1000000 條記錄，而每次回發(fā)只顯示 10 條記錄。如果啟用了 SqlDataSource 上的緩存，通過(guò)把 EnableCaching 設(shè)置為 true，則情況會(huì)更好一些。在這種情況下，我們只須訪問(wèn)一次數(shù)據(jù)庫(kù)服務(wù)器，整個(gè)數(shù)據(jù)集只加載一次，并在指定的期限內(nèi)存儲(chǔ)在 ASP.NET 緩存中。只要數(shù)據(jù)保持緩存狀態(tài)，顯示任何頁(yè)面將無(wú)須再次訪問(wèn)數(shù)據(jù)庫(kù)服務(wù)器。然而，可能有大量數(shù)據(jù)存儲(chǔ)在內(nèi)存中，換而言之，Web 服務(wù)器的壓力大大的增加了。因此，如果要使用 SqlDataSource 來(lái)獲取較小的數(shù)據(jù)時(shí)，GridView 內(nèi)建的自動(dòng)分頁(yè)可能足夠高效了，但對(duì)于大數(shù)據(jù)量來(lái)說(shuō)是不合適的。2. 分頁(yè)的四種存儲(chǔ)過(guò)程 (分頁(yè) + 排序的版本請(qǐng)參考 Blog 里其他文章) 在大多數(shù)情況下我們使用存儲(chǔ)過(guò)程來(lái)進(jìn)行分頁(yè)，今天有空總結(jié)了一下使用存儲(chǔ)過(guò)程對(duì) GridView 進(jìn)行分頁(yè)的 4 種寫(xiě)法(分別是使用 Top 關(guān)鍵字，臨時(shí)表，臨時(shí)表變量和 SQL Server 2005 新加的 Row_Number() 函數(shù)) 后續(xù)的文章中還將涉及 GridView 控件使用 ObjectDataSource 自定義分頁(yè) + 排序，Repeater 控件自定義分頁(yè) + 排序，有興趣的朋友可以參考。復(fù)制代碼代碼如下:
if exists(select 1 from sys.objects where name = apos;GetProductsCount apos; and type = apos;P apos;) 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;GetProductsByPage apos; and type = apos;P apos;) 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. 使用臨時(shí)表 if exists(select 1 from sys.objects where name = apos;GetProductsByPage apos; and type = apos;P apos;) 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 = @ToID 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(通過(guò) IDENTITY 列)關(guān)聯(lián)起來(lái)了. 一旦 table 變量產(chǎn)生, 連接數(shù)據(jù)庫(kù)表的 SELECT 語(yǔ)句就被執(zhí)行, 獲取需要的記錄.SET ROWCOUNT 用來(lái)限制放到 table 變量里的記錄的數(shù)量. 當(dāng) SET ROWCOUNT 的值指定為 PageNumber * PageSize 時(shí), 這個(gè)方法的效率取決于被請(qǐng)求的頁(yè)數(shù). 對(duì)于比較前面的頁(yè)來(lái)說(shuō)– 比如開(kāi)始幾頁(yè)的數(shù)據(jù)– 這種方法非常有效. 但是對(duì)接近尾部的頁(yè)來(lái)說(shuō), 這種方法的效率和默認(rèn)分頁(yè)時(shí)差不多 */ if exists(select 1 from sys.objects where name = apos;GetProductsByPage apos; and type = apos;P apos;) 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ù)之后停止處理查詢(xún) 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í)可以用來(lái)作為每條記錄的 row index. if exists(select 1 from sys.objects where name = apos;GetProductsByPage apos; and type = apos;P apos;) 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)用復(fù)制代碼代碼如下:
%@ 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 轉(zhuǎn)到第 asp:DropDownList ID= dropPage runat= server AutoPostBack= True OnSelectedIndexChanged= dropPage_SelectedIndexChanged /asp:DropDownList 頁(yè) 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
復(fù)制代碼代碼如下:
%@ 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 轉(zhuǎn)到第 asp:DropDownList ID= dropPage runat= server AutoPostBack= True OnSelectedIndexChanged= dropPage_SelectedIndexChanged /asp:DropDownList 頁(yè) 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
復(fù)制代碼代碼如下:
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 = 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 + 條記錄, 當(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 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 {// 每頁(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 = 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 + 條記錄, 當(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 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]

看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注丸趣 TV 行業(yè)資訊頻道，感謝您對(duì)丸趣 TV 的支持。

正文完