注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

liangxh2008的博客

 
 
 

日志

 
 

GridView自定义分页的四种存储过程  

2010-06-29 19:28:23|  分类: network |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

http://blog.csdn.net/amandag/archive/2008/07/14/2646472.aspx

 

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 = '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.使用临时表
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
-- 创建临时表
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, 103. 在GridView中的应用

view plaincopy to clipboardprint?
01.<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridViewPaging.aspx.cs" Inherits="GridViewPaging" %> 
02. 
03.<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
04. 
05.<html xmlns="http://www.w3.org/1999/xhtml" > 
06.<head runat="server"> 
07.    <title>Paging</title> 
08.</head> 
09.<body> 
10.    <form id="form1" runat="server"> 
11.    <div> 
12.        <asp:LinkButton id="lbtnFirst" runat="server" CommandName="First" OnCommand="lbtnPage_Command">|<</asp:LinkButton> 
13.        <asp:LinkButton id="lbtnPrevious" runat="server" CommandName="Previous" OnCommand="lbtnPage_Command"><<</asp:LinkButton> 
14.        <asp:Label id="lblMessage" runat="server" /> 
15.        <asp:LinkButton id="lbtnNext" runat="server" CommandName="Next" OnCommand="lbtnPage_Command">>></asp:LinkButton> 
16.        <asp:LinkButton id="lbtnLast" runat="server" CommandName="Last" OnCommand="lbtnPage_Command">>|</asp:LinkButton> 
17.        转到第<asp:DropDownList ID="dropPage" runat="server" AutoPostBack="True" OnSelectedIndexChanged="dropPage_SelectedIndexChanged"></asp:DropDownList>页  
18.        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ProductID" DataSourceID="SqlDataSource1"> 
19.            <Columns> 
20.                <asp:BoundField DataField="ProductID" HeaderText="ProductID" InsertVisible="False" ReadOnly="True" /> 
21.                <asp:BoundField DataField="ProductName" HeaderText="ProductName" /> 
22.                <asp:BoundField DataField="SupplierID" HeaderText="SupplierID" /> 
23.                <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" /> 
24.                <asp:BoundField DataField="QuantityPerUnit" HeaderText="QuantityPerUnit" /> 
25.                <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" /> 
26.                <asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock" /> 
27.                <asp:BoundField DataField="UnitsOnOrder" HeaderText="UnitsOnOrder" /> 
28.                <asp:BoundField DataField="ReorderLevel" HeaderText="ReorderLevel" /> 
29.                <asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued" /> 
30.            </Columns> 
31.        </asp:GridView> 
32.        <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"> 
33.            <SelectParameters> 
34.                <asp:Parameter Name="PageNumber" Type="Int32" /> 
35.                <asp:Parameter Name="PageSize" Type="Int32" /> 
36.            </SelectParameters> 
37.        </asp:SqlDataSource> 
38.    </div> 
39.    </form> 
40.</body> 
41.</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>
view plaincopy to clipboardprint?
01.using System;  
02.using System.Data;  
03.using System.Configuration;  
04.using System.Collections;  
05.using System.Web;  
06.using System.Web.Security;  
07.using System.Web.UI;  
08.using System.Web.UI.WebControls;  
09.using System.Web.UI.WebControls.WebParts;  
10.using System.Web.UI.HtmlControls;  
11.using System.Data.SqlClient;  
12. 
13.public partial class GridViewPaging : System.Web.UI.Page  
14.{  
15.    //每页显示的最多记录的条数  
16.    private int pageSize = 10;  
17.    //当前页号  
18.    private int currentPageNumber;  
19.    //显示数据的总条数  
20.    private static int rowCount;  
21.    //总页数  
22.    private static int pageCount;  
23.    
24.    protected void Page_Load(object sender, EventArgs e)  
25.    {  
26.        if (!IsPostBack)  
27.        {  
28.            SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);  
29. 
30.            SqlCommand cmd = new SqlCommand("GetProductsCount", cn);  
31.            cmd.CommandType = CommandType.StoredProcedure;  
32.            cn.Open();  
33.            rowCount = (int)cmd.ExecuteScalar();  
34.            cn.Close();  
35.            pageCount = (rowCount - 1) / pageSize + 1;  
36.            currentPageNumber = 1;  
37.            ViewState["currentPageNumber"] = currentPageNumber;  
38.            lbtnPrevious.Enabled = false;  
39.            lbtnFirst.Enabled = false;  
40. 
41.            for (int i = 1; i <= pageCount; i++)  
42.            {  
43.                dropPage.Items.Add(new ListItem(i.ToString(), i.ToString()));  
44.            }  
45.            dropPage.SelectedValue = dropPage.Items.FindByValue(currentPageNumber.ToString()).Value;  
46.            SqlDataSource1.Select(DataSourceSelectArguments.Empty);  
47.        }  
48.    }  
49. 
50.    protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)  
51.    {  
52.        SqlDataSource1.SelectParameters["PageNumber"].DefaultValue = currentPageNumber.ToString();  
53.        SqlDataSource1.SelectParameters["PageSize"].DefaultValue = pageSize.ToString();  
54.    }  
55. 
56.    protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)  
57.    {  
58.        lblMessage.Text = "共找到" + rowCount + "条记录, 当前第" + currentPageNumber + "/" + pageCount + "页";  
59.    }  
60. 
61.    protected void lbtnPage_Command(object sender, CommandEventArgs e)  
62.    {  
63.        switch (e.CommandName)  
64.        {   
65.            case "First":  
66.                currentPageNumber = 1;  
67.                break;  
68.            case "Previous":  
69.                currentPageNumber = (int)ViewState["currentPageNumber"] - 1 >  1 ? (int)ViewState["currentPageNumber"] - 1 : 1;  
70.                break;  
71.            case "Next":  
72.                currentPageNumber = (int)ViewState["currentPageNumber"] + 1 < pageCount ? (int)ViewState["currentPageNumber"] + 1 : pageCount;  
73.                break;  
74.            case "Last":  
75.                currentPageNumber = pageCount;  
76.                break;  
77.        }  
78.        dropPage.SelectedValue = dropPage.Items.FindByValue(currentPageNumber.ToString()).Value;  
79.        ViewState["currentPageNumber"] = currentPageNumber;  
80.        SetButton(currentPageNumber);  
81.        SqlDataSource1.Select(DataSourceSelectArguments.Empty);  
82.    }  
83. 
84.    private void SetButton(int currentPageNumber)  
85.    {  
86.        lbtnFirst.Enabled = currentPageNumber != 1;  
87.        lbtnPrevious.Enabled = currentPageNumber != 1;  
88.        lbtnNext.Enabled = currentPageNumber != pageCount;  
89.        lbtnLast.Enabled = currentPageNumber != pageCount;  
90.    }  
91. 
92.    protected void dropPage_SelectedIndexChanged(object sender, EventArgs e)  
93.    {  
94.        currentPageNumber = int.Parse(dropPage.SelectedValue);  
95.        ViewState["currentPageNumber"] = currentPageNumber;  
96.        SetButton(currentPageNumber);  
97.        SqlDataSource1.Select(DataSourceSelectArguments.Empty);  
98.    }  
99.} 
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);
    }
}4.分页效果图:

 

 


本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/amandag/archive/2008/07/14/2646472.aspx

  评论这张
 
阅读(1296)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017