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

GridView自定义分页的四种存储过程
2008-7-15
    今天有空总结了一下使用存储过程对GridView进行分页的4种写法(分别是使用Top关键字，临时表，临时表变量和SQL Server 2005 新加的Row_Number()函数)          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 = '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 

        --在返回指定的行数之后停止处理查询          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 = '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, 10view plaincopy to clipboardprint?
        <% @  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" SortExpression="ProductID" />
                        <asp:BoundField DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" />
                        <asp:BoundField DataField="SupplierID" HeaderText="SupplierID" SortExpression="SupplierID" />
                        <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" SortExpression="CategoryID" />
                        <asp:BoundField DataField="QuantityPerUnit" HeaderText="QuantityPerUnit" SortExpression="QuantityPerUnit" />
                        <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" SortExpression="UnitPrice" />
                        <asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock" SortExpression="UnitsInStock" />
                        <asp:BoundField DataField="UnitsOnOrder" HeaderText="UnitsOnOrder" SortExpression="UnitsOnOrder" />
                        <asp:BoundField DataField="ReorderLevel" HeaderText="ReorderLevel" SortExpression="ReorderLevel" />
                        <asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued" SortExpression="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);
            }
        }