通用分页存储过程 + 自定义控件

看到园子里的朋友发的一个通用分页存储过程,呵呵,正好把自己项目中的分页模块给大家分享一下

如存在不足的地方,请大家多多指教!

一.分页存储过程:

CREATE   PROCEDURE [dbo].[PagingRecord]
    ( 
    @TableName varchar(100),--表名或视图表 
    @OrderField varchar(100),--排序字段及类型(多个条件用逗号分开)如：JobID DESC,Checkintime
    @PageSize int,--页尺寸
    @PageIndex int,--页号,从0开始
    @DoCount  AS bit=1 ,-- 0值返回记录总数, 非 0 值则返回记录
    @StrWhere varchar(2000),--条件 
    @FieldList varchar(2000)--欲选择字段列表      
    ) 
AS 

BEGIN TRAN

DECLARE @SqlQuery varchar(4000)

IF @DoCount<>0
    Goto GetCount
Else
    Goto GetSearch



GetCount:--返回记录总数
    DECLARE @SearchSql AS Nvarchar(4000)
    SET @SearchSql= 'SELECT Count(*) AS Total FROM '+@TableName+' WHERE '+@StrWhere
    exec sp_executesql @SearchSql
    print @SearchSql 
COMMIT TRAN
    return

GetSearch:

    SET @SqlQuery='SELECT '+@FieldList+'
    FROM (SELECT row_number() over(ORDER BY '+@OrderField+') as rownum, 
            '+@FieldList+'
          FROM '+@TableName+' WHERE '+@StrWhere +') as temp
    WHERE rownum BETWEEN ('+cast(@PageIndex as varchar)+'-1)*'+cast(@PageSize as varchar)+'+1 and '+cast(@PageIndex as varchar)+'*'+cast(@PageSize as varchar) + ' ORDER BY '+@OrderField
    -- print @SqlQuery
    SET NOCOUNT ON
    execute(@SqlQuery)
    SET NOCOUNT OFF


COMMIT TRAN

二.在程序中定义一个公用方法,具体放哪,自己把握吧

        /// <summary>
        /// 简单分页程序
        /// </summary>
        /// <param name="tblName">表名</param>
        /// <param name="orderEx">排序公式(如: SendTime desc , pkid asc)</param>
        /// <param name="pagesize">單頁大小</param>
        /// <param name="pageindex">頁碼</param>
        /// <param name="strWhere">查詢條件</param>
        /// <param name="feildlist">顯示的字段</param>
        /// <param name="recordCount">返回的記錄總數</param>
        /// <returns></returns>
        public static DataSet GetPagingRecord(string tblName, string orderEx, int pagesize, int pageindex, string strWhere, string feildlist, out int recordCount)
        {
            SqlParameter[] cmdParas ={
                                          new SqlParameter("@TableName",SqlDbType.VarChar,255),
                                          new SqlParameter("@OrderField",SqlDbType.VarChar,255),
                                          new SqlParameter("@PageSize",SqlDbType.Int,4),
                                          new SqlParameter("@PageIndex",SqlDbType.Int,4),
                                          new SqlParameter("@DoCount",SqlDbType.Bit,1),
                                          new SqlParameter("@StrWhere",SqlDbType.VarChar,2000),
                                          new SqlParameter("@FieldList",SqlDbType.VarChar,2000)
                                      };
            cmdParas[0].Value = tblName;
            cmdParas[1].Value = orderEx;
            cmdParas[2].Value = pagesize;
            cmdParas[3].Value = pageindex;
            cmdParas[4].Value = true;
            cmdParas[5].Value = strWhere;
            cmdParas[6].Value = feildlist;

            DataSet ds = SqlBase.RunProcedure("PagingRecord", cmdParas, "table");
            recordCount = ds.Tables[0].Rows.Count > 0 ? Int32.Parse(ds.Tables[0].Rows[0][0].ToString()) : 0;

            SqlParameter[] cloneParas = new SqlParameter[cmdParas.Length];
            for (int i = 0, j = cmdParas.Length; i < j; i++)
            {
                cloneParas[i] = (SqlParameter)((ICloneable)cmdParas[i]).Clone();
            }

            cloneParas[4].Value = false;
            return SqlBase.RunProcedure("PagingRecord", cloneParas, "table");
        }

        /// <summary>
        /// 简单分页程序
        /// </summary>
        /// <param name="tblName">表名</param>
        /// <param name="orderEx">排序公式(如: SendTime desc , pkid asc)</param>
        /// <param name="pagesize">單頁大小</param>
        /// <param name="pageindex">頁碼</param>
        /// <param name="strWhere">查詢條件</param>
        /// <param name="feildlist">顯示的字段</param>
        /// <returns></returns>
        public static DataSet GetPagingRecord(string tblName, string orderEx, int pagesize, int pageindex, string strWhere, string feildlist)
        {
            SqlParameter[] cmdParas ={
                                          new SqlParameter("@TableName",SqlDbType.VarChar,255),
                                          new SqlParameter("@OrderField",SqlDbType.VarChar,255),
                                          new SqlParameter("@PageSize",SqlDbType.Int,4),
                                          new SqlParameter("@PageIndex",SqlDbType.Int,4),
                                          new SqlParameter("@DoCount",SqlDbType.Bit,1),
                                          new SqlParameter("@StrWhere",SqlDbType.VarChar,2000),
                                          new SqlParameter("@FieldList",SqlDbType.VarChar,2000)
                                      };
            cmdParas[0].Value = tblName;
            cmdParas[1].Value = orderEx;
            cmdParas[2].Value = pagesize;
            cmdParas[3].Value = pageindex;
            cmdParas[4].Value = false;
            cmdParas[5].Value = strWhere;
            cmdParas[6].Value = feildlist;

            return SqlBase.RunProcedure("PagingRecord", cmdParas, "table");
        }

三.自定义控件 Pager.cs

 [ToolboxData("<{0}:Pager runat=server></{0}:Pager>")]
    public class Pager : CompositeControl, INamingContainer
    {
        #region 公共屬性
        [Browsable(true), DefaultValue("0"), Localizable(true), Category("分頁"), Description("當前頁碼")]
        public int PageIndex
        {
            get
            {
                return ViewState["PageIndex"] == null ? 0 : Int32.Parse(ViewState["PageIndex"].ToString());
            }
            set
            {
                ViewState["PageIndex"] = value;
            }
        }

        [Browsable(true), DefaultValue("10"), Localizable(true), Category("分頁"), Description("每頁顯示的行數")]
        public int PageSize
        {
            get
            {
                return ViewState["PageSize"] == null ? 10 : Int32.Parse(ViewState["PageSize"].ToString());
            }
            set
            {
                ViewState["PageSize"] = value;
            }
        }

        [Browsable(true), DefaultValue("0"), Localizable(true), Category("數據"), Description("數據源中的總記錄數")]
        public int RecordCount
        {
            get
            {
                return ViewState["RecordCount"] == null ? 0 : Int32.Parse(ViewState["RecordCount"].ToString());
            }
            set
            {
                ViewState["RecordCount"] = value;
            }
        }

        [Browsable(true), Category("分页"), PersistenceMode(PersistenceMode.InnerProperty), Description("控制与控件关联的分页UI设置"),
        DesignerSerializationVisibility(DesignerSerializationVisibility.Content), NotifyParentProperty(true)]
        public PagerSettings PagerSetttings
        {
            get
            {
                if (this._pagerSettings == null)
                    this._pagerSettings = new PagerSettings();
                return this._pagerSettings;
            }
        }
        #endregion

        #region 私有屬性
        private int PageCount
        {
            get
            {
                if (this.RecordCount == 0 || this.PageSize == 0)
                    return 0;
                return (int)Math.Ceiling(this.RecordCount / (this.PageSize * 1.0));
            }
        }
        #endregion

        #region 私有變量
        private static readonly object EventPageIndexChanged = new object();
        private PagerSettings _pagerSettings = new PagerSettings();
        private LinkButton _lbtnFirst = new LinkButton();
        private LinkButton _lbtnPre = new LinkButton();
        private LinkButton _lbtnNext = new LinkButton();
        private LinkButton _lbtnLast = new LinkButton();
        private TextBox txtPageIndex = new TextBox();
        #endregion

        #region 事件相關
        [Description("Pager_OnPageIndexChanged"), Category("事件")]
        public event EventHandler PageIndexChanged
        {
            add
            {
                base.Events.AddHandler(EventPageIndexChanged, value);
            }
            remove
            {
                base.Events.RemoveHandler(EventPageIndexChanged, value);
            }
        }

        protected void OnPageChanged(EventArgs e)
        {
            EventHandler handler = (EventHandler)Events[EventPageIndexChanged];
            if (handler != null)
                handler(this, e);
        }
        #endregion

        #region 重寫基類
        protected override void CreateChildControls()
        {
            this.Controls.Clear();

            this._lbtnFirst.Text = "首頁";
            this._lbtnFirst.CommandName = "First";
            this._lbtnFirst.CausesValidation = false;
            this._lbtnFirst.Click += new EventHandler(LinkButton_Click);

            this._lbtnPre.Text = "上一頁";
            this._lbtnPre.CommandName = "Prev";
            this._lbtnPre.CausesValidation = false;
            this._lbtnPre.Click += new EventHandler(LinkButton_Click);

            this._lbtnNext.Text = "下一頁";
            this._lbtnNext.CommandName = "Next";
            this._lbtnNext.CausesValidation = false;
            this._lbtnNext.Click += new EventHandler(LinkButton_Click);

            this._lbtnLast.Text = "未頁";
            this._lbtnLast.CommandName = "Last";
            this._lbtnLast.CausesValidation = false;
            this._lbtnLast.Click += new EventHandler(LinkButton_Click);

            this.txtPageIndex.Width = 20;
            this.txtPageIndex.AutoPostBack = true;
            this.txtPageIndex.TextChanged += new EventHandler(txtPageIndex_TextChanged);

            this.Controls.Add(this._lbtnFirst);
            this.Controls.Add(this._lbtnLast);
            this.Controls.Add(txtPageIndex);
            this.Controls.Add(this._lbtnNext);
            this.Controls.Add(this._lbtnPre);
            base.CreateChildControls();
        }

        protected override void Render(HtmlTextWriter writer)
        {
            //如果只有一頁,則不顯示分頁控件
            //if (this.PageCount == 1)
            //    return;
            if (this.CssClass != "")
                writer.Write("<div class=\"{0}\">", this.CssClass);
            else
                writer.Write("<div>");
            //如果沒有數據
            if (this.RecordCount == 0)
            {
                writer.Write("對不起,沒有查詢到相關記錄!</div>");
                return;
            }
            writer.Write("<table align=\"right\"><tr><td>總記錄：<span style=\"color:red\">{0}</span>條，每頁<span>{1}</span>條，當前頁：<span style=\"color:red\">{2}</span>/<span>{3}</span></td>",
                this.RecordCount, this.PageSize.ToString(), this.PageIndex + 1, this.PageCount);

            #region 顯示首頁,上一頁
            writer.Write("<td>");
            if (this.PageIndex == 0 || this.PageCount == 1)
            {
                this._lbtnFirst.Visible = false;
                this._lbtnPre.Visible = false;
            }
            this._lbtnFirst.RenderControl(writer);
            writer.Write("</td><td>");
            this._lbtnPre.RenderControl(writer);
            writer.Write("</td>");

            #endregion

            #region 顯示下一頁,未頁
            writer.Write("<td>");
            if (this.PageIndex == this.PageCount - 1 || this.PageCount == 1)
            {
                this._lbtnNext.Visible = false;
                this._lbtnLast.Visible = false;
            }
            this._lbtnNext.RenderControl(writer);
            writer.Write("</td><td>");
            this._lbtnLast.RenderControl(writer);
            writer.Write("</td>");
            #endregion

            #region 跳轉至
            writer.Write("<td>跳轉至</td><td style=\"20px\">");
            txtPageIndex.RenderControl(writer);
            writer.Write("</td></tr></table></div>");
            #endregion

        }
        #endregion

        #region 子控件的事件
        void LinkButton_Click(object sender, EventArgs e)
        {
            string commandName = (sender as LinkButton).CommandName;
            switch (commandName)
            {
                case "First": PageIndex = 0; break;
                case "Next": PageIndex++; break;
                case "Prev": PageIndex--; break;
                case "Last": PageIndex = PageCount - 1; break;
            }
            OnPageChanged(e);
        }

        void txtPageIndex_TextChanged(object sender, EventArgs e)
        {
            if (txtPageIndex.Text.Trim().Length == 0)
                return;
            int pageIndex = 0;
            if (int.TryParse(txtPageIndex.Text, out pageIndex))
            {
                pageIndex--;
                if (pageIndex < 1)
                    this.PageIndex = 0;
                else if (pageIndex > PageCount)
                    this.PageIndex = PageCount - 1;
                else
                    this.PageIndex = pageIndex;
            }
            OnPageChanged(e);
        }
        #endregion

四.调用代码段

 void BindRpt()
        {
            int i = 0;
            rptInfo.DataSource = DataAccess.Common.GetPagingRecord("Orders", "AuditingTime DESC", Pager1.PageSize, Pager1.PageIndex + 1,
                StrWhere, "*", out i);
　　　　　　　　// StrWhere 查询条件,可以在基类PageBase中写一个属性,然后继承PageBase,用ViewState保存查询条件
　　　　　　　　// 当进行多条件查询时,只需要对StrWhere赋予不同的值,然后执行BindRpt()方法即可
　　　　　　　　// 给StrWhere赋值时,需要注意SQL注入攻击!
            rptInfo.DataBind();
            Pager1.RecordCount = i;
        }

        protected void Pager1_PageIndexChanged(object sender, EventArgs e)
        {
            BindRpt();
        }