• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    GridView自定义分页的四种存储过程
    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;GetProductsCountapos; and type = apos;Papos;)
    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;GetProductsByPageapos; and type = apos;Papos;)
    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;GetProductsByPageapos; and type = apos;Papos;)
    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;GetProductsByPageapos; and type = apos;Papos;)
    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;GetProductsByPageapos; and type = apos;Papos;)
    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]
    4.分页效果图:
     
    您可能感兴趣的文章:
    • GridView分页的实现以及自定义分页样式功能实例
    • C#自定义DataGridViewColumn显示TreeView
    • yii2.0之GridView自定义按钮和链接用法
    • GridView自定义删除操作的具体方法
    • 自定义GridView并且实现拖拽(附源码)
    • asp.net gridview自定义value值的代码
    • asp.net gridview分页:第一页 下一页 1 2 3 4 上一页 最末页
    • asp.net中的GridView分页问题
    • Android入门之ActivityGroup+GridView实现Tab分页标签的方法
    • asp.net Gridview分页保存选项
    • 基于GridView和ActivityGroup实现的TAB分页(附源码)
    • GridView自定义分页实例详解(附demo源码下载)
    上一篇:MSSQL 数据库备份和还原的几种方法 图文教程
    下一篇:Godaddy 导入导出MSSQL数据库的实现步骤
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

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