ReadonlyTableAccess<T> 源码

此源码为文章“.NET框架下ORM的一个轻量高效替代方案”的组成部分。

 

using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;

namespace FunTonn.MySqlDAL.SqlUtility {

    /// <summary>
    /// 数据表格访问抽象类，该类提供对只读数据表（如视图）的访问
    /// </summary>
    /// <typeparam name="T">数据实体</typeparam>
    public abstract class ReadonlyTableAccess<T> : IReadonlyTableAccess {

        //private TableColumnCollection _tableColumns = null;

        /// <summary>
        /// 获取数据表名称
        /// </summary>
        abstract public string TableName { get;}

        /// <summary>
        /// 设置数据表中需要执行查询和修改等动作的所有字段
        /// </summary>
        abstract public TableColumnCollection TableColumns { get;}

        /// <summary>
        /// 使用一个已经打开的DataReader，将数据表中的一行数据读取并填充到一个实体类
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="startIndex">索引偏移量，如果不是联表查询，请直接传入该参数</param>
        /// <returns></returns>
        abstract internal T BuildEntity(MySqlDataReader reader, int startIndex);

        /// <summary>
        /// 获取数据表的主键（默认为数据表的第一个字段），该值可能为null
        /// </summary>
        virtual public TableColumn TablePrimaryColumn { 
            get {
                try {
                    return TableColumns[0];
                } catch (Exception) {
                    return null;
                }
            } 
        }

        /// <summary>
        /// 获取排序表达式
        /// </summary>
        virtual protected string OrderExpress { get { return null; } }

        /// <summary>
        /// 获取一个值，该值指示是否缓存查询结果。
        /// 该设置只对 GetAll() 方法有效。
        /// </summary>
        virtual protected bool UseCache { get { return false; } }

        /// <summary>
        /// 设置缓存的存取KEY，只有当 UseCache 为 true 的时候该设置有效
        /// </summary>
        virtual protected string CacheKey { get { return TableName.ToUpper(); } }

        /// <summary>
        /// 生成SELECT语句WHERE之前的子句
        /// </summary>
        /// <returns></returns>
        virtual protected string BuildSelectString() {
            StringBuilder sb = new StringBuilder();
            sb.Append("select ");
            for (int i = 0; i < TableColumns.Count; i++) {
                if (i > 0)
                    sb.Append(',');
                sb.AppendFormat("{0}", TableColumns[i].Name);
            }
            sb.AppendFormat(
                " from {0} ", TableName
                );
            
            return sb.ToString();
        }

        /// <summary>
        /// 使用指定的SQL读取数据
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        virtual protected IList<T> ReadData(string cmd) {
            List<T> result = new List<T>();
            using (MySqlDataReader reader = DBUtility.ExecuteReader(CommandType.Text, cmd, null)) {
                while (reader.Read()) {
                    result.Add(BuildEntity(reader, 0));
                }
            }
            return result.AsReadOnly();
        }

        /// <summary>
        /// 根据主键值获取相应的记录行，
        /// 该值可能为 null
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        /// <exception cref="System.ArgumentNullException">当主键列未指定时引发该异常</exception>
        public T GetByPrimaryKey(object value) {
            if (TablePrimaryColumn == null)
                throw new NotSupportedException("当 TablePrimaryColumn 为 null 时该方法不再受支持。");  //ArgumentNullException("TablePrimaryColumn", "TablePrimaryColumn can not be null.");

            string paramName = "?keyValue";
            string sql = string.Format(
                "{0} where {1}={2}",
                BuildSelectString(),
                TablePrimaryColumn.Name,
                paramName
                );

            using (MySqlDataReader reader = DBUtility.ExecuteReader(CommandType.Text, sql, new MySqlParameter(paramName, value))) {
                if (reader.Read()) {
                    return BuildEntity(reader, 0);
                }
                return default(T);
            }
        }

        public IList<T> GetAll() { 
            // TODO: 考虑使用Cache的情况
            int itemCount = 0;
            return SearchTable(null, null, null, null, out itemCount);
        }

        public IList<T> Search(SearchConditionCollection filter) {
            int itemCount = 0;
            return SearchTable(filter, null, null, null, out itemCount);
        }

        public IList<T> Search(SearchConditionCollection filter, string order) {
            int itemCount = 0;
            return SearchTable(filter, order, null, null, out itemCount);
        }

        public IList<T> Search(SearchConditionCollection filter, int pageSize, int pageIndex, out int itemCount) {
            return SearchTable(filter, null, pageSize, pageIndex, out itemCount);
        }

        public IList<T> Search(SearchConditionCollection filter, string order, int pageSize, int pageIndex, out int itemCount) {
            return SearchTable(filter, order, pageSize, pageIndex, out itemCount);
        }

        // 读取数据
        private IList<T> SearchTable(SearchConditionCollection filter, string order, int? pageSize, int? pageNumber, out int itemCount) {
            itemCount = -1;
            bool paging = pageSize != null; // 是否分页
            string select = BuildSelectString();
            string where = filter == null ? string.Empty : filter.ToString(); // where子句
            string cmd = select; // 完整的查询命令，不包含分页和排序
            if (where != string.Empty) {
                cmd = select + " where " + where;
            }

            // 排序
            string userOrder = BuildOrderString(order);
            if (userOrder != null) {
                // 在完整的查询命令中附加排序命令
                cmd += userOrder;
            }

            // 如果分页
            if (paging) {
                // 计算当前查询在不分页的情况下的记录总数
                string sqlCount = string.Format(
                    "select count(*) from {0}{1}",
                    TableName, filter == null ? string.Empty : " where " + filter.ToString()
                    );
                itemCount = Convert.ToInt32(DBUtility.ExecuteScalar(CommandType.Text, sqlCount, null));
                // 在完整的查询命令中附加分页命令
                cmd += BuildPagingString(itemCount, (int)pageSize, (int)pageNumber);
            }

            //System.Diagnostics.Trace.WriteLine(cmd);
            // 读取数据
            return ReadData(cmd);
        }

        private string BuildPagingString(int itemCount, int pageSize, int pageNumber) {
            // 计算总页数
            double divide = itemCount / pageSize;
            double floor = System.Math.Floor(divide);
            if (itemCount % pageSize != 0)
                floor++;
            int pc = Convert.ToInt32(floor);
            int num = 0;
            
            if (pageNumber > pc)
                pageNumber = pc;
            if (pageNumber < 1)
                pageNumber = 1;

            num = (pageNumber - 1) * pageSize;

            return string.Format(
                " limit {0},{1} ",
                num, pageSize
                );
        }

        private string BuildOrderString(string userOrder) {
            string prefix = " order by ";
            if (userOrder == null) {
                if (OrderExpress != null)
                    return prefix + OrderExpress;
                else
                    return null;
            }
            return prefix + userOrder;
        }


    }
}