通用分页控件(DataGrid,DataList,Repeater都可以用它来分页)

1.建立用户控件Pager.ascx

1.1 html

< script  language ="javascript" >     
    function callButtonEvent()
    {
        var keycode =window.event.keyCode;
        if(keycode==13)
        {
            if(check()==true)
            {
                event.cancelBubble=true;
                event.returnValue=false;
                document.getElementById('<%=btnGo.ClientID%>').click();
            }
        }
    }
    function check()
    {
        var count =  parseInt(document.getElementById('<%=lblTotal.ClientID%>').outerText);
        var txt = document.getElementById('<%=txtCurrentPage.ClientID%>').value;
        var cur = parseInt(txt);
        if ((cur | NaN) ==0)
        {
            alert('Input page must format as integer.');            
            event.cancelPostBack=true;            
            return false;
        }
        if (cur > count || cur < 1)
        {
            alert('Input page no out of range.');            
            event.cancelPostBack=true;
            return false;
        }
    }
</ script >
< TABLE  ID ="Table1"  CELLSPACING ="0"  CELLPADDING ="0"  WIDTH ="100%"  BORDER ="0" >
     < colgroup >
         < col  width ="400" >
         < col  width ="50" >
         < col  width ="50" >
         < col  width ="40" >
         < col  width ="20" >
         < col  width ="40" >
         < col  width ="40" >
         < col  width ="50" >
         < col  width ="70" >
     </ colgroup >
     < TR  align ="right" >
         < td ></ td >
         < TD >< asp:LinkButton  id ="btnFirstPage"  runat ="server"  CommandArgument ="First" > 第一页 </ asp:LinkButton ></ TD >
         < TD >< asp:LinkButton  id ="btnPrevPage"  runat ="server"  CommandArgument ="Prev" > 上一页 </ asp:LinkButton ></ TD >
         < TD >< ASP:TEXTBOX  ID ="txtCurrentPage"  RUNAT ="server"  MAXLENGTH ="3"  Width ="40" > 0 </ ASP:TEXTBOX ></ TD >
         < TD >< ASP:LABEL  ID ="labOf"  RUNAT ="server" > of </ ASP:LABEL ></ TD >
         < TD >< ASP:LABEL  ID ="lblTotal"  RUNAT ="server" > 0 </ ASP:LABEL ></ TD >
         < TD >< ASP:BUTTON  ID ="btnGo"  RUNAT ="server"  TEXT ="转到"  COMMANDARGUMENT ="Go"  ToolTip ="转到" ></ ASP:BUTTON ></ TD >
         < TD >< asp:LinkButton  id ="btnNextPage"  runat ="server"  CommandArgument ="Next" > 下一页 </ asp:LinkButton ></ TD >
         < TD >< asp:LinkButton  id ="btnLastPage"  runat ="server"  CommandArgument ="Last" > 最后一页 </ asp:LinkButton ></ TD >
     </ TR >
</ TABLE >

1.2 cs代码

public   class  Pager : System.Web.UI.UserControl
     {
        protected System.Web.UI.WebControls.Label lblTotal;
        protected System.Web.UI.WebControls.Label labOf;
        protected System.Web.UI.WebControls.TextBox txtCurrentPage;
        protected System.Web.UI.WebControls.Button btnGo;
        protected System.Web.UI.WebControls.LinkButton btnFirstPage;
        protected System.Web.UI.WebControls.LinkButton btnPrevPage;
        protected System.Web.UI.WebControls.LinkButton btnNextPage;
        protected System.Web.UI.WebControls.LinkButton btnLastPage;
        int size=10;//可以在web.config中配置
        public event System.EventHandler NavigationClick;

        private void Page_Load(object sender, System.EventArgs e)
        {    
            this.txtCurrentPage.Attributes.Add("onkeypress","callButtonEvent();");
            this.btnGo.Attributes.Add("onclick","check();");
            if(!this.IsPostBack)
            {                
                SetStyle();    
                SetEnable();
            }
        }

        Web Form Designer generated code#region Web Form Designer generated code
        override protected void OnInit(EventArgs e)
        {
            //
            // CODEGEN: This call is required by the ASP.NET Web Form Designer.
            //
            InitializeComponent();
            base.OnInit(e);
            this.btnFirstPage.Click += new System.EventHandler(this.btnGo_Click);
            this.btnPrevPage.Click += new System.EventHandler(this.btnGo_Click);
            this.btnNextPage.Click += new System.EventHandler(this.btnGo_Click);
            this.btnLastPage.Click += new System.EventHandler(this.btnGo_Click);
            this.btnGo.Click += new System.EventHandler(this.btnGo_Click);
        }
        
        /**//// <summary>
        ///        Required method for Designer support - do not modify
        ///        the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {            
            this.Load += new System.EventHandler(this.Page_Load);

        }
        #endregion

        btnGo_Click#region btnGo_Click
        private void btnGo_Click(object sender, System.EventArgs e)
        {
            LinkButton linkbtn = sender as LinkButton;
            if( null == linkbtn )//button
            {
                Button btn = sender as Button;
                if( null == btn )
                {
                    return;
                }
                else
                {
                    int selPage = -1;
                    try
                    {
                        selPage =Int32.Parse(txtCurrentPage.Text);
                    }
                    catch
                    {
                        selPage = -1;
                    }
                    if (selPage > 0 && selPage <= PageCount)
                    {
                        ViewState["CurrentPageIndex"]  = selPage;
                    }
                    else
                    {
                        return;
                    }
                }
            }
            else//linkbutton
            {
                switch ( linkbtn.CommandArgument.Trim() )
                {
                    case "First":
                        ViewState["CurrentPageIndex"] = 1;
                        break;
                    case "Prev":
                        ViewState["CurrentPageIndex"] = (CurrentPageIndex > 1) ? CurrentPageIndex - 1 : 1;
                        break;
                    case "Next":
                        ViewState["CurrentPageIndex"] = (PageCount > CurrentPageIndex) ? CurrentPageIndex + 1 : PageCount;
                        break;
                    case "Last":                           
                        ViewState["CurrentPageIndex"]  = PageCount;
                        break;                
                    default:
                        break;
                }
            }
            SetEnable();//设置显示样式
            if (NavigationClick!=null)//调用事件 
            {
                NavigationClick( sender,e );
            }
        }
        #endregion

        SetStyle#region SetStyle
        private void SetStyle()
        {
            this.btnFirstPage.Attributes["style"] = "CURSOR: hand";
            this.btnLastPage.Attributes["style"] = "CURSOR: hand";
            this.btnNextPage.Attributes["style"] = "CURSOR: hand";
            this.btnPrevPage.Attributes["style"] = "CURSOR: hand";
        }
        #endregion

        SetEnable#region SetEnable
        // 应根据当前的CurrentPageIndex和pageCount设定哪些按钮可用        
        private void SetEnable()
        {
            this.lblTotal.Text =  PageCount.ToString();
            
            txtCurrentPage.Text =CurrentPageIndex.ToString();
            
            btnPrevPage.Enabled = false;
            btnNextPage.Enabled = false;
            
            if( PageCount >1 )
            {                    
                btnFirstPage.Enabled = btnPrevPage.Enabled = ( CurrentPageIndex >1 );
                btnNextPage.Enabled = btnLastPage.Enabled = ( CurrentPageIndex < PageCount );
            }
            else
            {                
                btnFirstPage.Enabled = false;
                btnLastPage.Enabled = false;
                btnPrevPage.Enabled = false;
                btnNextPage.Enabled = false;
            }
        }
        
        #endregion

        Property#region Property
        //获取或设置当前显示页的索引。                
        public int  CurrentPageIndex
        {
            get
            {
                object cpage=ViewState["CurrentPageIndex"];
                int pindex=(cpage==null)?1:(int)cpage;
                if(pindex>PageCount&&PageCount>0)
                    return PageCount;
                else if(pindex<1)
                    return 1;
                return pindex;
            }
            set
            {
                int cpage=value;
                if(cpage<1)
                    cpage=1;
                else if(cpage>this.PageCount)
                    cpage=this.PageCount;
                ViewState["CurrentPageIndex"]=cpage;
            }
        }

        // 获取或设置需要分页的所有记录的总数。    
        public int RecordCount
        {
            get
            {
                object obj=ViewState["Recordcount"];
                return (obj==null)?0:(int)obj;
            }
            set
            {
                ViewState["Recordcount"]=value;
                SetEnable();
            }
        }

        // 获取当前页之后的页的总数。        
        public int PagesRemain
        {
            get
            {
                return PageCount-CurrentPageIndex;
            }
        }
        // 获取或设置每页显示的项数。            
        public int PageSize
        {
            get
            {            
                object obj=ViewState["PageSize"];
                if (obj==null)
                {
                    obj= size;
                }                
                return (obj==null)?size:(int)obj;
            }
            set
            {
                int pageSize = value;
                
                if (Math.Abs(pageSize) == 0)
                    pageSize = size;

                ViewState["PageSize"]=pageSize;
            }
        }

        // 获取在当前页之后还未显示的剩余记录的项数。
        public int RecordsRemain
        {
            get
            {
                if(CurrentPageIndex<PageCount)
                {
                    return RecordCount-(CurrentPageIndex*PageSize);
                }
                else
                {
                    return 0;
                }
            }
        }

        // 获取所有要分页的记录需要的总页数。        
        public int PageCount
        {
            get{return (RecordCount > 0) ? (int)Math.Ceiling((double)RecordCount/(double)PageSize) : 1;}
        }

        public int XRecord
        {
            get
            {
                return int.Parse( System.Configuration.ConfigurationSettings.AppSettings["XRecord"].Trim() );
            }
        }
        #endregion Property
    }

2.建立DataGridPage.aspx
3.copy如下html代码

< HTML >
     < HEAD >
         < title > DataGridPage </ title >
         < meta  content ="Microsoft Visual Studio .NET 7.1"  name ="GENERATOR" >
         < meta  content ="C#"  name ="CODE_LANGUAGE" >
         < meta  content ="JavaScript"  name ="vs_defaultClientScript" >
         < meta  content ="http://schemas.microsoft.com/intellisense/ie5"  name ="vs_targetSchema" >
     </ HEAD >
     < body  MS_POSITIONING ="GridLayout" >
         < form  id ="Form1"  method ="post"  runat ="server" >
             < asp:datagrid  id ="DataGrid1"  style ="Z-INDEX: 101; LEFT: 632px; POSITION: absolute; TOP: 40px"
                runat ="server" ></ asp:datagrid >< uc1:pager  id ="Pager1"  runat ="server" ></ uc1:pager >< asp:datalist  id ="DataList1"  style ="Z-INDEX: 102; LEFT: 264px; POSITION: absolute; TOP: 40px"
                runat ="server" >
                 < ItemTemplate >
                     < table >
                         < tr >
                             < td > 用户ID: </ td >
                             < td > <% # DataBinder.Eval(Container.DataItem, "UserID")  %> </ td >
                             < td > 用户名: </ td >
                             < td > <% # DataBinder.Eval(Container.DataItem, "UserName")  %> </ td >
                         </ tr >
                     </ table >
                 </ ItemTemplate >
             </ asp:datalist >
             < asp:repeater  id ="Repeater1"  runat ="server" >
                 < HEADERTEMPLATE >
                     < table  cellpadding ="0"  cellspacing ="0"  border ="0" >
                         < tr >
                             < td > 用户ID </ td >
                             < td > 用户名: </ td >
                         </ tr >
                 </ HEADERTEMPLATE >
                 < ITEMTEMPLATE >
                     < tr >
                         < td >
                             <% # DataBinder.Eval(Container.DataItem, "UserID") %>
                         </ td >
                         < td > <% # DataBinder.Eval(Container.DataItem, "UserName")  %> </ td >
                     </ tr >
                 </ ITEMTEMPLATE >
                 < FOOTERTEMPLATE >
                     </ table >
                 </ FOOTERTEMPLATE >
             </ asp:repeater ></ form >
     </ body >
</ HTML >

4.拖入用户控件Pager.ascx
5.copy如下cs代码

public   class  DataGridPage : System.Web.UI.Page
     {
        protected System.Web.UI.WebControls.DataGrid DataGrid1;
        protected UserControl.Pager Pager1;//定义用户控件，根据用户控件所在目录做适当的调整
        protected System.Web.UI.WebControls.DataList DataList1;
        protected System.Web.UI.WebControls.Repeater Repeater1;
        public static string ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
        
        private void Page_Load(object sender, System.EventArgs e)
        {
            if (!this.IsPostBack)
            {                
                BindData();
            }
        }
        
        Bind Data#region Bind Data
        private void BindData()
        {            
            int output=0;
            this.SortExpression="UserID asc";
            this.FilterExpression="1=1";
            ds = GetList(this.Pager1.CurrentPageIndex,this.Pager1.PageSize,this.SortExpression,this.FilterExpression,ref output);

            if (ds!=null && ds.Tables.Count>0)
            {
                //DataGrid分页
                DataGrid1.DataSource = ds.Tables[0];
                DataGrid1.DataBind();
                //DataList分页
                DataList1.DataSource=ds.Tables[0];
                DataList1.DataBind();
                //Repeater分页
                Repeater1.DataSource=ds.Tables[0];
                Repeater1.DataBind();
            }
            Pager1.RecordCount=output;
        }
        #endregion        
    
        ExecSPDataSet#region ExecSPDataSet
        public static DataSet ExecSPDataSet(string sql,System.Data.IDataParameter[] paramers)
        {
            SqlConnection conn=new SqlConnection(ConnectionString);
            SqlCommand sqlcom=new SqlCommand(sql,conn);
            sqlcom.CommandType= CommandType.StoredProcedure ;

            foreach(System.Data.IDataParameter paramer in paramers)
            {
                sqlcom.Parameters.Add(paramer);
            }            
            conn.Open();
            
            SqlDataAdapter da=new SqlDataAdapter();
            da.SelectCommand=sqlcom;
            DataSet ds=new DataSet();
            da.Fill(ds);
        
            conn.Close();
            return ds;
        }

        #endregion

        GetList#region GetList
        public static DataSet GetList(int page_num,int row_in_page,string order_column,string comb_condition,ref int output)
        {            
            string sql="tp_Fetch_List";
            System.Data.SqlClient.SqlParameter[] p=new SqlParameter[5];
            
            p[0]=new SqlParameter();
            p[0].ParameterName ="@page_num";
            p[0].Value =page_num;
            p[0].DbType=System.Data.DbType.Int32;

            p[1]=new SqlParameter();
            p[1].ParameterName ="@row_in_page";
            p[1].Value =row_in_page;
            p[1].DbType=System.Data.DbType.Int32;

            p[2]=new SqlParameter();
            p[2].ParameterName ="@order_column";
            p[2].Value =order_column;
            p[2].DbType=System.Data.DbType.String;

            p[3]=new SqlParameter();
            p[3].ParameterName ="@row_total";
            p[3].Direction=System.Data.ParameterDirection.Output;
            p[3].DbType=System.Data.DbType.Int32;

            p[4]=new SqlParameter();
            p[4].ParameterName ="@comb_condition";
            p[4].Value =comb_condition;
            p[4].DbType=System.Data.DbType.String;
            DataSet ds=ExecSPDataSet(sql,p);
            if (p[3].Value!=DBNull.Value  && p[3].Value.ToString()!=string.Empty )
                output=Convert.ToInt32(p[3].Value);
            return ds;                
        }
        #endregion    

        property#region property
        private DataSet ds
        {
            get
            {
                return ViewState["ds"] as DataSet;
            }
            set
            {
                ViewState["ds"] = value;
            }
        }
        public string FilterExpression
        {
            get
            {
                if (this.ViewState["FilterExpression"]!=null)
                    return (string)this.ViewState["FilterExpression"];
                return string.Empty ;
            }
            set
            {
                this.ViewState["FilterExpression"]=value;

            }
        }

        public string SortExpression
        {
            get
            {
                if (this.ViewState["SortExpression"]!=null)
                    return (string)this.ViewState["SortExpression"];
                return string.Empty ;
            }
            set
            {
                this.ViewState["SortExpression"]=value;

            }
        }

        #endregion

        Web Form Designer generated code#region Web Form Designer generated code
        override protected void OnInit(EventArgs e)
        {
            //
            // CODEGEN: This call is required by the ASP.NET Web Form Designer.
            //
            InitializeComponent();
            base.OnInit(e);
            this.Pager1.NavigationClick+=new EventHandler(Pager1_NavigationClick);
        }
        
        /**//// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {    
            this.Load += new System.EventHandler(this.Page_Load);

        }
        #endregion

        private void Pager1_NavigationClick(object sender, EventArgs e)
        {
            BindData();
        }
    }

6.测试表结构

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[TestGrid] ' )  and   OBJECTPROPERTY (id, N ' IsUserTable ' )  =   1 )
drop   table   [ dbo ] . [ TestGrid ]
GO

CREATE   TABLE   [ dbo ] . [ TestGrid ]  (
     [ UserID ]   [ int ]   NOT   NULL  ,
     [ UserName ]   [ nvarchar ]  ( 50 ) COLLATE Chinese_PRC_CI_AS  NULL  ,
     [ Country ]   [ nvarchar ]  ( 50 ) COLLATE Chinese_PRC_CI_AS  NULL  ,
     [ State ]   [ nvarchar ]  ( 50 ) COLLATE Chinese_PRC_CI_AS  NULL  ,
     [ Enabled ]   [ bit ]   NULL  
)  ON   [ PRIMARY ]
GO

7.测试存储过程

create   PROCEDURE  tp_Fetch_List(  
   @page_num                  INT ,
   @row_in_page               INT ,
   @order_column              VARCHAR ( 50 ),
   @row_total                 INT   OUTPUT,
   @comb_condition            VARCHAR ( 500 )
)
AS
BEGIN
     SET  NOCOUNT  ON

     DECLARE  
       @jcc_status               INT ,
       @sql                    NVARCHAR ( 4000 ),
       @row_ahead          INT
     
   SET   @jcc_status   =   0

   SET   @row_ahead   =  ( @page_num - 1 )  *   @row_in_page


SET   @sql = ' SELECT TOP  ' +   cast ( @row_in_page   as   varchar ( 255 ))  +    '  * FROM (  '
SET   @sql   =   @sql   +   ' SELECT   *

FROM TestGrid 
 ) as A where 1=1 '

IF   LEN ( @comb_condition ) > 0
         SET   @sql   =   @sql   +   '  AND ( '   +   @comb_condition    +   ' ) '     

SET   @sql   =   @sql   +   ' and UserID not in ( select UserID from ( '
SET   @sql   =   @sql   +   ' SELECT TOP  '   +   cast ( @row_ahead   as   varchar ( 255 ))  +   '  * From ( '
SET   @sql   =   @sql   +   ' SELECT   *

FROM TestGrid 
 ) as A where 1=1 '
     IF   LEN ( @comb_condition ) > 0
         SET   @sql   =   @sql   +   '  AND (  '   +   @comb_condition    +   '  ) '     

     IF   LEN ( @order_column ) > 0
         BEGIN
             SET   @sql   =   @sql   +   '  ORDER BY  '   +   @order_column      +   '  ) AS B ) '
         END
     ELSE
         BEGIN
             SET   @sql   =   @sql   +   '  ) AS B ) '
         END

     IF   LEN ( @order_column ) > 0
         BEGIN
             SET   @sql   =   @sql   +   '  ORDER BY  '   +   @order_column      
         END

  print   @sql

     EXEC  ( @sql )

     SET   @sql =  N ' SELECT @row_total=COUNT(*) FROM ( '
SET   @sql   =   @sql   +   ' SELECT  *

FROM TestGrid 
 ) as A where 1=1 '
IF   LEN ( @comb_condition ) > 0
         SET   @sql   =   @sql   +   '  AND ( '   +   @comb_condition    +   ' ) '     

print   @sql

     EXEC  sp_executesql  @sql ,N ' @row_total INT OUT ' , @row_total  OUT

     IF   @@ERROR   !=   0
     BEGIN
         SELECT   @jcc_status   =   - 98
     END


exit_bk:

--  exit with MS SQL Server error
   IF   @jcc_status   =   - 98
     BEGIN
       RAISERROR  ( ' MS SQL Server error, please contact your system administrator. ' , 16 , 1 ) WITH  NOWAIT
       RETURN  ( @jcc_status )
     END

--  normal exit 
   RETURN  ( 0 )
END

GO

--  declare @aa int 
--  exec tp_Fetch_List 1,10,'',@aa out ,'1=1'
--  select @aa

8.源代码下载 /Files/singlepine/DataGridPage.rar