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

成都網(wǎng)站設(shè)計(jì)、網(wǎng)站制作的關(guān)注點(diǎn)不是能為您做些什么網(wǎng)站，而是怎么做網(wǎng)站，有沒有做好網(wǎng)站，給成都創(chuàng)新互聯(lián)公司一個(gè)展示的機(jī)會(huì)來證明自己，這并不會(huì)花費(fèi)您太多時(shí)間，或許會(huì)給您帶來新的靈感和驚喜。面向用戶友好，注重用戶體驗(yàn)，一切以用戶為中心。

1. 為什么不使用GridView的默認(rèn)分頁功能 首先要說說為什么不用GridView的默認(rèn)的分頁功能，GridView控件并非真正知道如何獲得一個(gè)新頁面，它只是請(qǐng)求綁定的數(shù)據(jù)源控件返回適合規(guī)定頁面的行，分頁最終是由數(shù)據(jù)源控件完成。當(dāng)我們使用SqlDataSource或使用以上的代碼處理分頁時(shí)。每次這個(gè)頁面被請(qǐng)求或者回發(fā)時(shí)，所有和這個(gè)SELECT語句匹配的記錄都被讀取并存儲(chǔ)到一個(gè)內(nèi)部的DataSet中，但只顯示適合當(dāng)前頁面大小的記錄數(shù)。也就是說有可能使用Select語句返回1000000條記錄，而每次回發(fā)只顯示10條記錄。如果啟用了SqlDataSource上的緩存，通過把EnableCaching設(shè)置為true，則情況會(huì)更好一些。在這種情況下，我們只須訪問一次數(shù)據(jù)庫服務(wù)器，整個(gè)數(shù)據(jù)集只加載一次，并在指定的期限內(nèi)存儲(chǔ)在ASP.NET緩存中。只要數(shù)據(jù)保持緩存狀態(tài)，顯示任何頁面將無須再次訪問數(shù)據(jù)庫服務(wù)器。然而，可能有大量數(shù)據(jù)存儲(chǔ)在內(nèi)存中，換而言之，Web服務(wù)器的壓力大大的增加了。因此，如果要使用SqlDataSource來獲取較小的數(shù)據(jù)時(shí)，GridView內(nèi)建的自動(dòng)分頁可能足夠高效了，但對(duì)于大數(shù)據(jù)量來說是不合適的。 2. 分頁的四種存儲(chǔ)過程(分頁+排序的版本請(qǐng)參考Blog里其他文章) 在大多數(shù)情況下我們使用存儲(chǔ)過程來進(jìn)行分頁，今天有空總結(jié)了一下使用存儲(chǔ)過程對(duì)GridView進(jìn)行分頁的4種寫法(分別是使用Top關(guān)鍵字，臨時(shí)表，臨時(shí)表變量和SQL Server 2005 新加的Row_Number()函數(shù)) 后續(xù)的文章中還將涉及GridView控件使用ObjectDataSource自定義分頁 + 排序，Repeater控件自定義分頁 + 排序，有興趣的朋友可以參考。復(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.使用表變量 /* 為要分頁的數(shù)據(jù)創(chuàng)建一個(gè)table變量,這個(gè)table變量里有一個(gè)作為主健的IDENTITY列.這樣需要分頁的每條記錄在table變量里就和一個(gè)row index(通過IDENTITY列)關(guān)聯(lián)起來了.一旦table變量產(chǎn)生,連接數(shù)據(jù)庫表的SELECT語句就被執(zhí)行,獲取需要的記錄.SET ROWCOUNT用來限制放到table變量里的記錄的數(shù)量. 當(dāng)SET ROWCOUNT的值指定為PageNumber * PageSize時(shí),這個(gè)方法的效率取決于被請(qǐng)求的頁數(shù).對(duì)于比較前面的頁來說– 比如開始幾頁的數(shù)據(jù)– 這種方法非常有效. 但是對(duì)接近尾部的頁來說,這種方法的效率和默認(rèn)分頁時(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ù)之后停止處理查詢 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 = &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>頁 <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>頁 <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 { //每頁顯示的最多記錄的條數(shù) private int pageSize = 10; //當(dāng)前頁號(hào) private int currentPageNumber; //顯示數(shù)據(jù)的總條數(shù) private static int rowCount; //總頁數(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 + "頁"; } 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 { //每頁顯示的最多記錄的條數(shù) private int pageSize = 10; //當(dāng)前頁號(hào) private int currentPageNumber; //顯示數(shù)據(jù)的總條數(shù) private static int rowCount; //總頁數(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 + "頁"; } 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)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝您對(duì)創(chuàng)新互聯(lián)的支持。

本文標(biāo)題：GridView中如何自定義分頁的存儲(chǔ)過程
當(dāng)前鏈接：http://aaarwkj.com/article36/jeepsg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供域名注冊(cè)、網(wǎng)站改版、網(wǎng)站設(shè)計(jì)、軟件開發(fā)、微信公眾號(hào)、標(biāo)簽優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)