GridView中如何自定义分页的存储过程

59次阅读

共计 13723 个字符，预计需要花费 35 分钟才能阅读完成。

GridView 中如何自定义分页的存储过程，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面丸趣 TV 小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

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;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. 使用临时表 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 — 创建临时表 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;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 – 在返回指定的行数之后停止处理查询 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;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 中的应用复制代码代码如下:
%@ 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]

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注丸趣 TV 行业资讯频道，感谢您对丸趣 TV 的支持。

正文完