sql2005存储过程分页及ASP.NET分页类的实现

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› ASP.NET›ASP.NET编程经验

原作者: [db:作者] 来自: [db:来源] 收藏邀请

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using CerEgip.DAL;
namespace CerEgip.Common
{
    /// <summary>
    /// 分页类,通过存储过程进行分页
    /// </summary>
    public class Pager
    {
        #region 参数
        private int pageIndex = 1;
        private int recordCount = 0;
        private int pageSize = 20;
        private int pageCount = 0;
        private int rowCount = 0;
        private string tableName = "";
        private string whereCondition = "1=1";
        private string selectStr = "*";
        private string order = "";
        private string procedure = "pager";
        private bool orderType = true;
        private string _FirstStr = "";
        private string _PrevStr = "";
        private string _NextStr = "";
        private string _LastStr = "";
        private string _TurnUrlStr = "";
        private string _Options = "";
        private string strCountww = "";  //共N条信息
        private string strPageww = "";    //第N页/共N页
        private string strTurnww;  //跳转控件
        private string pageindexName = "page";

        /// <summary>
        /// 所要操作的存储过程名称,已有默认的分页存储过程
        /// </summary>
        public string Procedure
        {
            get
            {
                return this.procedure;
            }
            set
            {
                if (value == null || value.Length <= 0)
                {
                    this.procedure = "pager";
                }
                else
                {
                    this.procedure = value;
                }
            }
        }

        /// <summary>
        /// 当前所要显示的页面数
        /// </summary>
        public int PageIndex
        {
            get
            {
                return this.pageIndex;
            }
            set
            {
                this.pageIndex = value;
            }
        }

        /// <summary>
        /// 总的页面数
        /// </summary>
        public int PageCount
        {
            get
            {
                return this.pageCount;
            }
            set
            {
                this.pageCount = value;
            }
        }

        /// <summary>
        /// 总行数
        /// </summary>
        public int RecordCount
        {
            get
            {
                return this.recordCount;
            }
            set
            {
                this.recordCount = value;
            }
        }

        /// <summary>
        /// 每页条数
        /// </summary>
        public int PageSize
        {
            get
            {
                return this.pageSize;
            }
            set
            {
                this.pageSize = value;
            }
        }

        /// <summary>
        /// 表名称
        /// </summary>
        public string TableName
        {
            get
            {
                return tableName;
            }
            set
            {
                this.tableName = value;
            }
        }

        /// <summary>
        /// 条件查询
        /// </summary>
        public string WhereCondition
        {
            get
            {
                return whereCondition;
            }
            set
            {
                whereCondition = value;
            }
        }

        /// <summary>
        /// 查询目标(搜索目标),比如:AddTime AS 时间,ID AS 编号
        /// </summary>
        public string SelectStr
        {
            get
            {
                return selectStr;
            }
            set
            {
                selectStr = value;
            }
        }

        /// <summary>
        /// 排序表达式
        /// </summary>
        public string Order
        {
            get
            {
                return order;
            }
            set
            {
                order = value;
            }
        }
        /// <summary>
        /// 排序类型 true:asc false:desc
        /// </summary>
        public bool OrderType
        {
            get
            {
                return orderType;
            }
            set
            {
                orderType = value;
            }
        }
        /// <summary>
        /// 得到当前返回的数量
        /// </summary>
        public int RowCount
        {
            get
            {
                return this.rowCount;
            }
        }
        /// <summary>
        /// 首页显示样式
        /// </summary>
        public string FirstStr
        {
            get { return _FirstStr; }
            set { _FirstStr = value; }
        }

        /// <summary>
        /// 上一页显示样式
        /// </summary>
        public string PrevStr
        {
            get { return _PrevStr; }
            set { _PrevStr = value; }
        }

        /// <summary>
        /// 下一页显示样式
        /// </summary>
        public string NextStr
        {
            get { return _NextStr; }
            set { _NextStr = value; }
        }
        /// <summary>
        /// 尾页显示样式
        /// </summary>
        public string LastStr
        {
            get { return _LastStr; }
            set { _LastStr = value; }
        }
        /// <summary>
        /// 跳转的url链接
        /// </summary>
        public string TurnUrlStr
        {
            get { return _TurnUrlStr; }
            set { _TurnUrlStr = value; }
        }
        /// <summary>
        /// 跳转的url链接的参数前面不要加问号和与号
        /// </summary>
        public string Options
        {
            get { return _Options; }
            set { _Options = value; }
        }
        /// <summary>
        /// 分页参数名称
        /// </summary>
        public string PageIndexName
        {
            get { return pageindexName; }
            set { pageindexName = value; }
        }
        #endregion 参数
        /// <summary>
        /// 分页查寻结果
        /// </summary>
        public DataTable GetDatas(int pageIndex)
        {
            this.pageIndex = pageIndex;
            Pager pager = this;
            DataTable returnTb = Pagination(ref pager).Tables[0];
            this.rowCount = returnTb.Rows.Count;
            return returnTb;
        }

        /// <summary>
        /// 分页操作存储过程函数
        /// </summary>
        /// <param name="pager">Pager</param>
        /// <returns>返回DataSet</returns>
        private DataSet Pagination(ref Pager pager)
        {
            SqlParameter[] par = new SqlParameter[8];
            par[0] = new SqlParameter("@TableName",SqlDbType.NVarChar,200);
            par[0].Value = pager.TableName;
            par[1] = new SqlParameter("@orderBy", SqlDbType.NVarChar, 200);
            par[1].Value = pager.Order;
            par[2] = new SqlParameter("@fieldlist",SqlDbType.NVarChar,200);
            par[2].Value = pager.SelectStr;
            par[3] = new SqlParameter("@WhereCondition", SqlDbType.NVarChar, 200);
            par[3].Value = pager.WhereCondition;
            par[4] = new SqlParameter("@PageIndex",SqlDbType.Int);
            par[4].Value = pager.pageIndex;
            par[5] = new SqlParameter("@pageSize",SqlDbType.Int);
            par[5].Value = pager.PageSize;
            par[6] = new SqlParameter("@RecordCount",SqlDbType.Int);
            par[6].Direction = ParameterDirection.InputOutput;
            par[7] = new SqlParameter("@PageCount",SqlDbType.Int);
            par[7].Direction = ParameterDirection.InputOutput;
            DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure,pager.Procedure,par);
            pager.RecordCount = (int)par[6].Value;
            pager.pageCount = (int)par[7].Value;
            return ds;
        }
        #region 返回分页后的页码显示
        /// <summary>
        /// 返回分页后的页码显示
        /// </summary>
        /// <param name="bolCount">是否显示共N条信息</param>
        /// <param name="bolPage">是否显示第N页/共N页</param>
        /// <param name="bolFirst">是否显示首页</param>
        /// <param name="bolLast">是否显示尾页</param>
        /// <param name="bolTurn">是否显示跳转控件</param>
        /// <param name="IsChinese">是否用中文显示</param>
        /// <param name="intStyle">样式选择 1:字符 2:符号</param>
        /// <param name="intShowNum">每页显示多少个数字</param>
        /// <param name="isHtml">是否HTML分页</param>
        /// <param name="exName">如果为HTML分页,要输入HTML后缀名</param>
        /// <returns>返回分页后的页码显示</returns>
        public string GetShowPageStr(bool bolCount, bool bolPage, bool bolFirst, bool bolLast, bool bolTurn, bool IsChinese, int intStyle, int intShowNum,bool isHtml,string exName)
        {
            string strPageShowww = "";
            string _FirstStr2 = "";
            string _PrevStr2 = "";
            string _NextStr2 = "";
            string _LastStr2 = "";

             #region 公共处理
            //总页数
            pageCount = (recordCount + pageSize - 1) / pageSize;

            //超出最小页码
            if (pageIndex < 1)
            {
                pageIndex = 1;
            }

            //超出最大页码
            if (pageIndex > pageCount)
            {
                pageIndex = pageCount;
            }

            if (IsChinese)//中文分页
            {
                //跳转
                strTurnww = "<input value='" + pageIndex.ToString()