GridView自定义分页存储过程

  要做一个GridView分页的应用，可以前用惯了Sql2005的CET，现在开发的项目用的却是Sql2000。CET用不了了只好写了一个临时表的存储过程。用存储过程实现GridView分页效果总体分为两大块：一个是存储过程，另一个就是自定义的按钮控件。下面是详细代码。
第一 存储过程
  存储过程1：从数据库取得要查询记录的总条数     

CREATE procedure P_PageSP_TotalNum
    @Sql varchar(2000),
    @FilterStr varchar(500)

as

declare @Str varchar(2000)
set @Str ='
declare @indextable table(id int identity(1,1),nid int);
insert into @indextable(nid) select [ID] from  (' + @Sql + ' ' + @FilterStr + ') as a  ;
select count(*) from @indextable'

exec(@Str)
GO

    存储过程2：按页取数据

CREATE procedure P_PageSP
    @Lower varchar(4),        --要查询的下限
    @Upper varchar(4),        --要查询的上限
    @TableName varchar(100),     --查询的表名
    @OrderBy varchar(200),        --排序条件
    @FilterStr varchar(1000),        --过滤条件
    @Sql varchar(2000)        --查询语句

AS

declare @Str varchar(2000)

set @Str ='
declare @indextable table(id int identity(1,1),nid int);
insert into @indextable(nid) select [ID] from  (' + @Sql + ' ' + @FilterStr + ') as a  '  + @OrderBy  + ' ;
' + @Sql + ' inner join @indextable t on t.nid='+ @TableName +'.ID 
where t.id > ' + @Lower + ' and t.id <= ' + @Upper + ' ' 
exec(@Str)
GO

第二 自定义按钮控件
    aspx代码

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="GvPageSet.ascx.cs" Inherits="Control_GvPageSet" %>
<div style="text-align:right; width:100%;background-color:#c6d7ef">
<table cellpadding="0" cellspacing="0">
    <tr>
        <td style="width: 200px; text-align: right">
            <asp:Label ID="Label1" runat="server" Text="当前页:"></asp:Label><asp:Label ID="lblCurrentInfo"
                runat="server">1</asp:Label>&nbsp; &nbsp;<asp:Label ID="Label2" runat="server" Text="共"></asp:Label><asp:Label ID="lblCount"
                runat="server">1</asp:Label><asp:Label ID="Label3" runat="server" Text="页"></asp:Label><asp:Label
                    ID="lblTotalNum" runat="server">5</asp:Label><asp:Label ID="Label5" runat="server" Text="条"></asp:Label>
            &nbsp; &nbsp;
        </td>
        <td style="width: 33px">
            <asp:LinkButton ID="lbtnFirst" runat="server" OnClick="PageIndex_Changed">首页</asp:LinkButton></td>
        <td style="width: 50px">
            <asp:LinkButton ID="lbtnPrev" runat="server" OnClick="PageIndex_Changed">上一页</asp:LinkButton></td>
        <td style="width: 50px">
            <asp:LinkButton ID="lbtnNext" runat="server" OnClick="PageIndex_Changed">下一页</asp:LinkButton></td>
        <td style="width: 33px">
            <asp:LinkButton ID="lbtnLast" runat="server" OnClick="PageIndex_Changed">末页</asp:LinkButton></td>
    </tr>
</table></div>

    CS代码

CS
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;
using System.ComponentModel;

public partial class Control_GvPageSet : System.Web.UI.UserControl
{

    Properties#region Properties
    [BrowsableAttribute(true)]
    [DescriptionAttribute("要绑定数据的GridView.ID")]
    [DefaultValueAttribute("")]
    [CategoryAttribute("Appearance")]
    public string gvID
    {
        get
        {
            return (ViewState["gvID"] == null) ? "" : ViewState["gvID"].ToString();
        }
        set
        {
            ViewState["gvID"] = value;
        }
    }

    [BrowsableAttribute(true)]
    [DescriptionAttribute("每一页显示的行数，默认每页10条")]
    [DefaultValueAttribute("")]
    [CategoryAttribute("Appearance")]
    public int PageSize
    {
        get
        {
            return (ViewState["PageSize"] == null) ? 10 : Convert.ToInt32(ViewState["PageSize"]);
        }
        set
        {
            ViewState["PageSize"] = value;
        }
    }

    [BrowsableAttribute(true)]
    [DescriptionAttribute("要从中读取数据的DataTable.Name")]
    [DefaultValueAttribute("")]
    [CategoryAttribute("Data")]
    public string TableName
    {
        get
        {
            return (ViewState["TableName"] == null) ? "" : ViewState["TableName"].ToString();
        }
        set
        {
            ViewState["TableName"] = value;
        }
    }

    [BrowsableAttribute(true)]
    [DescriptionAttribute("要从中读取数据的DataTable.Name")]
    [DefaultValueAttribute("")]
    [CategoryAttribute("Data")]
    public string OrderBy
    {
        get
        {
            return (ViewState["OrderBy"] == null) ? "" : ViewState["OrderBy"].ToString();
        }
        set
        {
            ViewState["OrderBy"] = value;
        }
    }

    [BrowsableAttribute(true)]
    [DescriptionAttribute("读取数据的Sql语句，多表 联合查询")]
    [DefaultValueAttribute("")]
    [CategoryAttribute("Data")]
    public string Sql
    {
        get
        {
           return (ViewState["Sql"] == null) ? "" : ViewState["Sql"].ToString();
        }
        set
        {
            ViewState["Sql"] = value;
        }
    }

    [BrowsableAttribute(true)]
    [DescriptionAttribute("过滤条件，可以为空")]
    [DefaultValueAttribute("")]
    [CategoryAttribute("Data")]
    public string FilterString
    {
        get
        {
           return (ViewState["FilterString"] == null) ? "" : ViewState["FilterString"].ToString();
        }
        set
        {
            ViewState["FilterString"] = value;
        }
    }

    [BrowsableAttribute(false)]
    [DescriptionAttribute("当前页数")]
    [DefaultValueAttribute("")]
    public int CurrentInfo
    {
        get
        {
            return Convert.ToInt32(lblCurrentInfo.Text.Trim());
        }
    }
    #endregion

    protected override void OnInit(EventArgs e)
    {
        this.Visible = false;
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            //this.lbtnPrev.Enabled = false;
            //this.lblCurrentInfo.Text = "1";
        }
    }

    /**//// <summary>
    /// 绑定GridView控件，供页面调用
    /// </summary>
    public void BindGridView()
    {
        GetPageNum();
        //this.Visible = false;
        this.lbtnPrev.Enabled = false;
        this.lblCurrentInfo.Text = "1";
        BindGV();
    }

    /**//// <summary>
    /// 绑定GridView控件，供页面调用
    /// </summary>
    /// <param name="currentInfo">要显示的当前页</param>
    public void BindGridView(int currentInfo)
    {
        GetPageNum();
        //this.Visible = false;
        this.lbtnPrev.Enabled = false;
        this.lblCurrentInfo.Text = currentInfo.ToString();
        BindGV();
    }

    /**//// <summary>
    /// 获取结果总数目和总页数
    /// </summary>
    /// <returns>总页数</returns>
    public int GetPageNum()
    {
        SqlParameter[] para = new SqlParameter[2];
        SqlParameter p;
        p = new SqlParameter("@FilterStr", SqlDbType.VarChar, 500);
        p.Value = FilterString;
        para[0] = p;
        p = new SqlParameter("@Sql", SqlDbType.VarChar, 2000);
        p.Value = Sql;
        para[1] = p;

        int TotalNum = (int)MIS.DAO.PSqlClient.ExecuteScalar(new MIS.DAO.SqlConn().Conn, CommandType.StoredProcedure, "P_PageSP_TotalNum", para);
        lblTotalNum.Text = TotalNum.ToString();
        int pageNum = Convert.ToInt32(Math.Ceiling(Convert.ToDouble((double)TotalNum / PageSize)));
        lblCount.Text = pageNum.ToString();

        return pageNum;
    }

    按钮处理事件#region 按钮处理事件
    /**//**/
    /**//// <summary>
    /// 按钮处理事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    public void PageIndex_Changed(object sender, EventArgs e)
    {
        LinkButton linkbtn = (LinkButton)sender;
        switch (linkbtn.ID.Trim())
        {
            case "lbtnFirst":
                this.lblCurrentInfo.Text = "1";

                BindGV();

                break;
            case "lbtnPrev":

                this.lblCurrentInfo.Text = (Convert.ToInt32(this.lblCurrentInfo.Text) - 1).ToString();


                BindGV();

                break;
            case "lbtnNext":

                this.lblCurrentInfo.Text = (Convert.ToInt32(this.lblCurrentInfo.Text) + 1).ToString();

                BindGV();

                break;
            case "lbtnLast":
                this.lblCurrentInfo.Text = this.lblCount.Text;

                BindGV();

                break;
        }
        SetBtnState();
    }
    private void SetBtnState()
    {
        if (Convert.ToInt32(this.lblCurrentInfo.Text) >= Convert.ToInt32(this.lblCount.Text) && Convert.ToInt32(this.lblCurrentInfo.Text) <= 1)
        {
            this.lbtnPrev.Enabled = false;
            this.lbtnNext.Enabled = false;
            this.lbtnFirst.Enabled = false;
            this.lbtnLast.Enabled = false;
        }
        else if (Convert.ToInt32(this.lblCurrentInfo.Text) >= Convert.ToInt32(this.lblCount.Text))
        {
            this.lbtnPrev.Enabled = true;
            this.lbtnNext.Enabled = false;
            this.lbtnFirst.Enabled = true;
            this.lbtnLast.Enabled = false;
        }
        else if (Convert.ToInt32(this.lblCurrentInfo.Text) <= 1)
        {
            this.lbtnPrev.Enabled = false;
            this.lbtnNext.Enabled = true;
            this.lbtnFirst.Enabled = false;
            this.lbtnLast.Enabled = true;
        }
        else
        {
            this.lbtnPrev.Enabled = true;
            this.lbtnNext.Enabled = true;
            this.lbtnFirst.Enabled = true;
            this.lbtnLast.Enabled = true;
        }
    }
    #endregion

    /**//// <summary>
    /// 获取数据绑定GridView
    /// </summary>
    private void BindGV()
    {
        int lower, upper;
        int currentPage = Convert.ToInt32(lblCurrentInfo.Text.Trim());
        lower = (currentPage - 1) * PageSize;
        upper = lower + PageSize;

        SqlParameter[] para = new SqlParameter[6];
        SqlParameter p;
        p = new SqlParameter("@Lower", SqlDbType.VarChar, 4);
        p.Value = lower.ToString();
        para[0] = p;
        p = new SqlParameter("@Upper", SqlDbType.VarChar, 4);
        p.Value = upper.ToString();
        para[1] = p;
        p = new SqlParameter("@TableName", SqlDbType.VarChar, 100);
        p.Value = TableName;
        para[2] = p;
        p = new SqlParameter("@FilterStr", SqlDbType.VarChar, 1000);
        p.Value = FilterString;
        para[3] = p;
        p = new SqlParameter("@Sql", SqlDbType.VarChar, 2000);
        p.Value = Sql;
        para[4] = p;
        p = new SqlParameter("@OrderBy", SqlDbType.VarChar,200);
        p.Value = OrderBy;
        para[5] = p;

        DataTable dt = MIS.DAO.PSqlClient.ExecuteDataTable(new MIS.DAO.SqlConn().Conn, CommandType.StoredProcedure, "P_PageSP", para);
        if (dt.Rows.Count == 0)
        {
            this.Visible = false;
        }
        else
        {
            this.Visible = true;
        }
        GridView gv = (GridView)Page.FindControl(gvID);
        gv.DataSource = dt;
        gv.DataBind();
        SetBtnState();
    }

}

第三 应用
   把自定义控件拖到用于分页的GridView页面上，属性栏设置GridView的各个属性(都有详细的属性说明)。页面CS代码需要绑定GridView的地方调用自定义控件的BindGridView()方法绑定数据(如果是查询页面调用方法之前应先设置控件的FilterStr属性）。

   点击下载