ASP.NET数据分页探索之二：使用自定义控件

最新推荐文章于 2024-05-19 17:03:37 发布

aecte40684

最新推荐文章于 2024-05-19 17:03:37 发布

阅读量75

点赞数

文章标签：数据库 ui

原文链接：http://www.cnblogs.com/jf_dai/archive/2009/11/22/1608208.html

版权

上次使用了PagedDataSource控件实现了数据分页，然而由于其本质原因，不能实现“要多少取多少”的高效分页。高效分页的关键是SQL的构建和数据库服务器的支持。Microsoft的SQL Server和Access数据库支持TOP N子句，SQL Server 2005支持RowNumber（）函数，它们是分页SQL语句的基础。使用TOP N子句大概如下：SELECT TOP N * FROM [tablename] WHERE [keyfield] NOT IN(SELECT TOP xN [keyfield] FROM [tablename] WHERE whereExpression ORDER BY orderbyExpression) AND whereExpression ORDER BY orderbyExpression.自定义控件也是使用这个关键的SQL语句实现按需取数。本文创建了一个数据分页控件PagedAccessDataSource控件，该控件最初为Access数据库设计而得此名，但是也能够用于SQL Server。该控件继承自WebControl来输出页面导航，实现IEnumerable接口以实现为数据使用控件的DataSource属性提供访问接口，实现IDataSource接口以实现ASP.NET 2.0的数据空间选择数据源，即指定DataSourceID属性。后台代码中使用可以用类似的方式：

Code
PagedAceessDataSource pads = new PagedAccessDataSource(10);//初始化PagedAccessDataSource控件，指定每页显示10条记录
pads.Fields = new string[]{"field1","field2","field3"}//控件声明中使用"field1,field2,field3"
pads.KeyFields = "ID";
pads.TableName = "myTable";
pads.WhereExp = "field1>XX,field2<YY";
pads.OrderByExp = "field1 desc,field2 asc";

pads.ListPagerStyle = ListPagerStyle.Numeric;
this.gridview1.DataSource = pads;
//this.gridview1.DataSourceID = pads.ID;
this.gridview1.DataBind();

而在HTML标记中可以指定所有需要的属性，后台不需要一行代码。

设计时外观：

下面就是自定义数据分页控件PagedAccessDataSource的主实现：

Code
using System;
using System.Collections;
using System.ComponentModel;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
[assembly: TagPrefix("FDesign.WebControls", "asp")]
namespace FDesign.WebControls
{

    [DefaultProperty("MdbFile")]
    [ToolboxData("<{0}:PagedAccessDataControl runat=server></{0}:PagedAccessDataControl>")]
    public class PagedAccessDataControl : WebControl, IEnumerable, IDataSource
    {
        /// <summary>
        /// 设置当前页索引
        /// </summary>
        private int curPageIndex = 1;
        /// <summary>
        /// 设置对应于分页参数的查询字符串名称，默认为pageIndex
        /// </summary>
        private string queryStringName = "pageIndex";
        /// <summary>
        /// 设置对应于分页参数的查询字符串名称，默认为pageIndex
        /// </summary>
        [Category("Appearance")]
        [DefaultValue("pageIndex")]
        [Description("设置或获取当前页索引")]
        public string QueryStringName
        {
            get { return this.queryStringName; }
            set { this.queryStringName = value; }
        }
        private PagedAccessDataControlView _view = null;
        private string[] _viewNames = null;
        private static readonly object EventDataSourceChanged = new object();
        /// <summary>
        /// Access数据库连接字符串
        /// </summary>
        private string connString;
        /// <summary>
        /// 每页包含记录数
        /// </summary>
        private int pageSize = 10;
        /// <summary>
        /// 要查询的字段数
        /// </summary>
        private string[] fields;
        /// <summary>
        /// 查询的表名
        /// </summary>
        string tableName;
        /// <summary>
        /// WHERE表达式
        /// </summary>
        string whereExp;
        /// <summary>
        ///ORDER BY表达式
        /// </summary>
        string orderByExp;
        /// <summary>
        /// 主键字段
        /// </summary>
        string keyFiled;
        /// <summary>
        /// 页面数
        /// </summary>
        int pageCount = 0;
        /// <summary>
        /// 是否允许分页
        /// </summary>
        bool allowPaging = true;
        /// <summary>
        /// Access文件名
        /// </summary>
        string mdbFile;
        [Category("Data")]
        [Description("设置或者获取Access数据库文件名")]
        public string MdbFile
        {
            get
            {
                return this.mdbFile;
            }
            set
            {
                this.mdbFile = value;
            }
        }
        /// <summary>
        /// 保存数据库查询结果的DataView
        /// </summary>
        DataView dv;
        /// <summary>
        /// 要查询的字段数
        /// </summary>

        [Bindable(true)]
        [Category("Data")]
        [Description("设置查询要用到的字段,多个字段用逗号“,”隔开")]
        public string Fields
        {
            get
            {

                if (this.fields != null)
                {
                    StringBuilder sb = new StringBuilder();
                    for (int i = 0; i < this.fields.Length; i++)
                    {
                        sb.Append(this.fields[i]);
                        if (i != this.fields.Length - 1)
                            sb.Append(",");
                    }
                    return sb.ToString();
                }
                else
                    return null;

            }
            set
            {

                fields = value.Split(',');
            }
        }
        /// <summary>
        /// 从Div构建PagedAccessDataControl类
        /// </summary>
        public PagedAccessDataControl() : base(HtmlTextWriterTag.Div) { }
        /// <summary>
        /// 连接到Access数据库文件的连接字符串
        /// </summary>
        [Bindable(true)]
        [Category("Data")]
        [Description("设置控件的连接字符串，支持Access和SQL Server.")]
        public string ConnectionString
        {
            get
            {
                if (this.DesignMode)
                    return "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + this.mdbFile;
                if (string.IsNullOrEmpty(this.connString))
                {
                    if (string.IsNullOrEmpty(this.mdbFile))
                        throw new ArgumentNullException("MdbFile", "在未指定ConnectionString的时候必须指定文件名。默认放在app_data文件夹下。");
                    string physicalPath = this.Context.Request.MapPath(this.mdbFile);
                    if (!File.Exists(physicalPath))
                        physicalPath = this.Context.Request.MapPath("~\\app_data\\" + this.mdbFile);
                    if (!File.Exists(physicalPath))
                        throw new ArgumentNullException("MdbFile", "系统找不到该指定的文件。");
                    this.connString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + physicalPath;

                }

                return this.connString;
            }
            set { this.connString = value; }
        }
        /// <summary>
        /// 每页大小,默认为10
        /// </summary>
        [Category("Data")]
        [DefaultValue(10)]
        [Description("设置每页显示的记录条数。亦即分页大小。")]
        public int PageSize
        {
            get { return this.pageSize; }
            set { this.pageSize = value; }
        }
        /// <summary>
        /// Where判断表达式
        /// </summary>
        [Category("Data")]
        [Description("查询语句中的条件判断子句")]
        public string WhereExp
        {
            get { return this.whereExp; }
            set { this.whereExp = value; }
        }
        /// <summary>
        /// Order By子句表达式
        /// </summary>
        [Category("Data")]
        [Description("查询语句中的排序子句")]
        public string OrderByExp
        {
            get { return this.orderByExp; }
            set { this.orderByExp = value; }
        }
        /// <summary>
        /// 主键,为分页依据
        /// </summary>
        [Category("Data")]
        [Description("设置关键字字段")]
        public string KeyFiled
        {
            get { return this.keyFiled; }
            set { this.keyFiled = value; }
        }
        /// <summary>
        /// 要查询的表名
        /// </summary>
        [Category("Data")]
        [Description("设置查询的表名，亦可是一个Select语句的查询集合")]
        public string TableName
        {
            get { return this.tableName; }
            set { this.tableName = value; }
        }
        /// <summary>
        /// 是否允许分页,默认值true
        /// </summary>
        [Category("Data")]
        [DefaultValue(true)]
        [Description("是否允许分页，默认为是")]
        public bool AllowPaging
        {
            get { return this.allowPaging; }
            set { this.allowPaging = value; }
        }
        /// <summary>
        /// 当前页面索引
        /// </summary>
        [Category("Data")]
        [DefaultValue(1)]
        [Description("设置或获取当前的页面索引")]
        public int CurrentPageIndex
        {
            get
            {
                if (this.DesignMode)
                    return this.curPageIndex;
                if (this.Context.Request.Params[this.queryStringName] != null)
                {
                    int.TryParse(this.Context.Request.Params[this.queryStringName], out this.curPageIndex);
                }

                return this.curPageIndex;
            }
            set
            {

                this.curPageIndex = value;
            }
        }

        /// <summary>
        /// 列表样式,数字式还是上一页、下一页按钮
        /// </summary>
        [Category("Appearance")]
        [DefaultValue(PagerStyle.Numeric)]
        [Description("设置分页索引显示模式：数字式、前后页式或者混合式")]
        public PagerStyle ListPagerStyle
        {
            get;
            set;
        }
        [EditorBrowsable(EditorBrowsableState.Never)]
        public override Control FindControl(string id)
        {
            return base.FindControl(this.ID);
        }
        // [EditorBrowsable(EditorBrowsableState.Never)]
        /// <summary>
        /// 获取控件的SELECT查询语句
        /// </summary>
        [Category("Data")]
        [EditorBrowsable(EditorBrowsableState.Never)]
        public string SelectCommand
        {
            get
            {
                if (this.fields != null && this.tableName != null)
                {
                    SQLTextBuilder stb = new SQLTextBuilder(SQLType.SELECT, this.fields, this.keyFiled, this.tableName, this.whereExp, this.orderByExp, this.allowPaging, this.pageSize, this.CurrentPageIndex);
                    return stb.ToString();
                }
                else
                {
                    return null;
                }

            }
        }
        protected override void RenderContents(HtmlTextWriter output)
        {
            if (this.DesignMode)
            {

                if (this.ListPagerStyle == PagerStyle.Numeric)
                    output.Write("1 2 3 4 5 6 7

");
if (this.ListPagerStyle == PagerStyle.Mixed)
output.Write("首页上一页 1 2 3 4 5 6 7

下一页末页");
                if (this.ListPagerStyle == PagerStyle.PreNext)
                    output.Write("首页上一页下一页末页");
                return;
            }

            output.Write(this.GeneratePagerText());

        }
        /// <summary>
        /// 创建分页导航文本。
        /// </summary>
        /// <returns>分页导航文本。</returns>
        protected virtual string GeneratePagerText()
        {
            StringBuilder sb = new StringBuilder();
            if (this.pageCount == 0)
                this.GetPageCount();
            this.pageCount = (this.pageCount % pageSize == 0) ? this.pageCount / this.pageSize : this.pageCount / this.pageSize + 1;
            if (this.CurrentPageIndex > this.pageCount)
                this.CurrentPageIndex = this.pageCount;
            if (this.CurrentPageIndex < 1)
                this.CurrentPageIndex = 1;

            if (this.ListPagerStyle == PagerStyle.Numeric)
            {
                this.NumericPagerStyle(sb);
            }
            else if (this.ListPagerStyle == PagerStyle.Mixed)
            {
                sb.Append(string.Format("<a href='?{0}=1'>首页</a>  ", this.queryStringName));
                if (this.CurrentPageIndex > 1)
                    sb.Append(string.Format("<a href='?{0}={1}' title='第{1}页'>上一页</a>  ", this.queryStringName, (this.CurrentPageIndex - 1).ToString()));
                this.NumericPagerStyle(sb);
                if (this.CurrentPageIndex < pageCount)
                    sb.Append(string.Format("<a href='?{1}={0}' title='第{0}页'>下一页</a>  ", (this.CurrentPageIndex + 1).ToString(), this.queryStringName));
                sb.Append(string.Format("<a href='?{1}={0}' title='第{0}页'>末页</a>  ", this.pageCount.ToString(), this.queryStringName));
            }
            else
            {
                this.PreNextPagerStyle(sb);
            }
            return sb.ToString();
        }
        protected virtual void PreNextPagerStyle(StringBuilder sb)
        {
            sb.Append(string.Format("<a href='?{0}=1'>首页</a>  ", this.queryStringName));
            if (this.CurrentPageIndex > 1)
                sb.Append(string.Format("<a href='?{1}={0}' title='第{0}页'>上一页</a>  ", (this.CurrentPageIndex - 1).ToString(), this.queryStringName));
            if (this.CurrentPageIndex < pageCount)
                sb.Append(string.Format("<a href='?{1}={0}' title='第{0}页'>下一页</a>  ", (this.CurrentPageIndex + 1).ToString(), this.queryStringName));
            sb.Append(string.Format("<a href='?{1}={0}' title='第{0}页'>末页</a>  ", this.pageCount.ToString(), this.queryStringName));
            sb.Append(string.Format("[{0} / {1}]", this.CurrentPageIndex.ToString(), this.pageCount.ToString()));
        }
        protected virtual void NumericPagerStyle(StringBuilder sb)
        {
            int startPage = this.CurrentPageIndex / 10;
            startPage = (this.CurrentPageIndex % 10 == 0) ? startPage - 1 : startPage;
            if (startPage > 0)
            {
                for (int j = 0; j < startPage; j++)
                {
                    sb.Append(string.Format("<a href='?{1}={0}' title='第{0}页'>{0}</a>  ", j * 10 + 1, this.queryStringName));
                }
            }

            if (this.CurrentPageIndex > 10 * startPage && this.CurrentPageIndex > 10)
                sb.Append(string.Format("<a title='第{0}页' href='?{1}={0}'>

</a>  ", startPage * 10, this.queryStringName));

            for (int i = startPage * 10 + 1; i <= this.pageCount; i++)
            {

                if (i <= startPage * 10 + 10 || i == this.pageCount)
                {
                    if (i == this.CurrentPageIndex)
                        sb.Append(string.Format("<a href='?{1}={0}' title='第{0}页'><b><u>{0}</u></b></a>  ", i, this.queryStringName));
                    else
                        sb.Append(string.Format("<a href='?{1}={0}' title='第{0}页'>{0}</a>  ", i, this.queryStringName));
                }
                else if (i == startPage * 10 + 11)
                    sb.Append(string.Format("<a href='?{1}={0}' title='第{0}页'>

</a> ", i, this.queryStringName));
            }
        }
        protected override void AddAttributesToRender(HtmlTextWriter writer)
        {
            writer.AddAttribute("class", "LISTPAGER");
            base.AddAttributesToRender(writer);
        }

        public IEnumerator GetEnumerator()
        {
            if (base.DesignMode)
                return null;
            GetData();
            return dv.GetEnumerator();
        }
        protected virtual void GetData()
        {
            DataSet ds = new DataSet();
            IDataAdapter oda = (this.ConnectionString.Contains("Provider")) ? (IDataAdapter)new OleDbDataAdapter(this.SelectCommand, this.ConnectionString) : (IDataAdapter)new SqlDataAdapter(this.SelectCommand, this.ConnectionString);
            oda.Fill(ds);
            this.dv = ds.Tables[0].DefaultView;
            ds.Dispose();

        }
        protected virtual void GetPageCount()
        {

            IDbConnection cn = (this.ConnectionString.Contains("Provider")) ? (IDbConnection)new OleDbConnection(this.ConnectionString) : (IDbConnection)new SqlConnection(this.connString);
            IDbCommand cmd = cn.CreateCommand();
            SQLTextBuilder stb = new SQLTextBuilder();
            stb.WhereExp = this.whereExp;
            stb.COUNTField = this.keyFiled;
            stb.QueryType = SQLType.SELECT;
            stb.TableName = this.tableName;
            cmd.CommandText = stb.ToString();
            try
            {
                cn.Open();
                IDataReader idr = cmd.ExecuteReader();
                while (idr.Read())
                {
                    this.pageCount++;
                }
                idr.Close();
                idr.Dispose();
            }
            finally
            {
                cn.Close();
                cn.Dispose();
            }
        }
        public DataSourceView GetView(string name)
        {
            if (this._view == null)
                this._view = this.CreateView("DefaultView");
            return this._view;
        }
        public ICollection GetViewNames()
        {
            if (this._viewNames == null)
                this._viewNames = new string[] { "DefaultView" };
            return null;
        }
        public PagedAccessDataControlView CreateView(string viewName)
        {
            return new PagedAccessDataControlView(this, viewName, base.Context);
        }
        event EventHandler IDataSource.DataSourceChanged
        {
            add
            {
                base.Events.AddHandler(EventDataSourceChanged, value);
            }
            remove
            {
                base.Events.RemoveHandler(EventDataSourceChanged, value);
            }
        }
        /// <summary>
        /// 索引页导航样式。数字式、按钮式和混合式。
        /// </summary>
        public enum PagerStyle
        {
            Numeric,
            PreNext,
            Mixed
        }
    }
}

下面是使用效果：

以上就是使用自定义控件分页的全过程。设计缺陷：控件的Fields复杂属性应该是字符串数组型的，但是最终为简单起见，设计为字符串型，以","分隔。还有很多小问题，但是还能用。

转载于:https://www.cnblogs.com/jf_dai/archive/2009/11/22/1608208.html

aecte40684

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
ASP.NET数据分页探索之二：使用自定义控件

上次使用了PagedDataSource控件实现了数据分页，然而由于其本质原因，不能实现“要多少取多少”的高效分页。高效分页的关键是SQL的构建和数据库服务器的支持。Microsoft的SQL Server和Access数据库支持TOP N子句，SQL Server 2005支持RowNumber（）函数，它们是分页SQL语句的基础。使用TOP N子句大概如下：SELECT TOP N ...
复制链接

扫一扫