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

 http://blog.csdn.net/lovexx1122/archive/2008/07/29/2731885.aspx
使用存储过程对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 = &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
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridViewPaging.aspx.cs" Inherits="GridViewPaging" %>  
  2.   
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  4.   
  5. <html xmlns="http://www.w3.org/1999/xhtml" >  
  6. <head runat="server">  
  7.     <title>Paging</title>  
  8. </head>  
  9. <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>
  1. using System;   
  2. using System.Data;   
  3. using System.Configuration;   
  4. using System.Collections;   
  5. using System.Web;   
  6. using System.Web.Security;   
  7. using System.Web.UI;   
  8. using System.Web.UI.WebControls;   
  9. 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);
    }
}

分页效果图:

分页效果图

posted on 2009-07-19 03:59  阿C's  阅读(315)  评论(0)  编辑  收藏  举报
Copyright © 2024 阿C's
Powered by .NET 9.0 on Kubernetes Powered by: 博客园