基于CodeSmith代码生成之数据访问层

 

 

     在以前的开发中，看到过很多的代码工具，在数据结构不变的情况下，用来生成代码是很方便的，但是在我们实际的开发过程，经常修改数据库的结构，这就造成了对代码的多个地方的更新操作，这样就给更新带来很的麻烦。我下面要说的就是通用数据访问层，最大的好处就是，在开发的过程中对数据库结构的变化时，我们只需要更新实体类，就可以做到，另外实体模型的生成使用了部份类的特性，如果对实体的修改也可以不变，同时也生成了视图实体，这样也补充了大部份查询操作要用多个表来连接查询的要求。

    我们首先来对实体层作一个抽象，如下：

实体抽象
 /**//// <summary>
    /// model的基类
    /// </summary>
    public abstract class EntityBase
    {
        /**//// <summary>
        /// 要更新的字段列表
        /// </summary>
        protected IList<string> _UpdateStatement = new List<string>();
        /**//// <summary>
        /// 在删除操作时，存在多个主键编号
        /// </summary>
        public object[] KeyValues;

        /**//// <summary>
        /// 构造函数
        /// </summary>
        protected EntityBase()
        {
        }
        /**//// <summary>
        /// 添加要更新的字段
        /// </summary>
        /// <param name="FieldName">字段名称</param>
        protected virtual void AddUpdateField(string FieldName)
        {
            if (string.IsNullOrEmpty(FieldName))
            {
                throw new ArgumentNullException(string.Format("输入添加字段[{0}]为空。", FieldName));
            }
            if (!this._UpdateStatement.Contains(FieldName))
            {
                this._UpdateStatement.Add(FieldName);
            }
        }

        /**//// <summary>
        /// 填充当前实体
        /// </summary>
        /// <param name="dr">数据行集</param>
        /// <returns>返回成功或失败</returns>
        public abstract bool Assign(DataRow dr);
        /**//// <summary>
        /// 填充当前实体
        /// </summary>
        /// <param name="ds">数据集，使用当前第一行</param>
        /// <returns>返回成功或失败</returns>
        public virtual bool Assign(DataSet ds)
        {
            return ((((ds != null) && (ds.Tables.Count > 0)) && (ds.Tables[0].Rows.Count == 1)) && this.Assign(ds.Tables[0].Rows[0]));
        }
        /**//// <summary>
        /// 创建编辑和更新参数
        /// </summary>
        /// <returns>参数列表</returns>
        public virtual IList<Parameter> BuildParams()
        {
            return BuildParams(true);
        }
        /**//// <summary>
        /// 创建编辑和更新参数
        /// </summary>
        /// <param name="IsAdd">是增加参数列表</param>
        /// <returns>参数列表</returns>
        protected abstract IList<Parameter> BuildParams(bool IsAdd);
        /**//// <summary>
        /// 创建编辑和更新参数
        /// </summary>
        /// <returns>参数列表</returns>
        public virtual IList<Parameter> BuildParamsEdit()
        {
            return this.BuildParams(false);
        }

        /**//// <summary>
        /// 复制当前实体
        /// </summary>
        /// <returns></returns>
        public abstract EntityBase Clone();
        /**//// <summary>
        /// 使用随机数据填充实例
        /// </summary>
        public abstract void Fill();
        /**//// <summary>
        /// 在设置唯一键重复时，出错消息
        /// </summary>
        /// <returns></returns>
        public abstract string GetDoubleKeyMessage();
        /**//// <summary>
        /// 根据外键名获取实例对象
        /// </summary>
        /// <param name="ForeignKeyName">外键名</param>
        /// <returns></returns>
        public abstract EntityBase GetEntityByForeignKey(string ForeignKeyName);
        /**//// <summary>
        /// 获取主键名
        /// </summary>
        /// <returns></returns>
        public abstract string GetKeyName();
        /**//// <summary>
        /// 主键值
        /// </summary>
        /// <returns></returns>
        public abstract object GetKeyValue();
        /**//// <summary>
        /// 获取实体表名或视图名
        /// </summary>
        /// <returns></returns>
        public abstract string GetTableName();
        /**//// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        protected abstract Hashtable GetUniques();
        /**//// <summary>
        /// 获取更新字段语句
        /// </summary>
        /// <param name="FieldName">字段名</param>
        /// <returns>返回SET设置</returns>
        protected abstract string GetUpdateStatement(string FieldName);
        /**//// <summary>
        /// 获取字段值
        /// </summary>
        /// <param name="Name">字段名</param>
        /// <returns>字段值</returns>
        public abstract object GetValueByName(string Name);

        /**//// <summary>
        /// 获取字段值
        /// </summary>
        /// <param name="FieldName">字段名</param>
        /// <returns>字段值</returns>
        public object this[string FieldName]
        {
            get
            {
                return this.GetValueByName(FieldName);
            }
        }
        /**//// <summary>
        /// 获取主键，以逗号分隔的字符串
        /// </summary>
        public string KeyValueString
        {
            get
            {
                string s = string.Empty;
                foreach (object o in this.KeyValues)
                {
                    if (o != null)
                    {
                        s = s + string.Format("{0},", o);
                    }
                }
                return s.TrimEnd(new char[] { ',' });
            }
        }
        /**//// <summary>
        /// 获取唯一设置的字段名
        /// </summary>
        public string UniqueName
        {
            get
            {
                string Result = string.Empty;
                if (null != this.Uniques)
                {
                    foreach (string Key in this.Uniques.Keys)
                    {
                        if (Key != null)
                        {
                            Result = Result + string.Format("{0},", Key);
                        }
                    }
                }
                return Result.TrimEnd(new char[] { ',' });
            }
        }
        /**//// <summary>
        /// 获取唯一值字段列表
        /// </summary>
        public Hashtable Uniques
        {
            get
            {
                return this.GetUniques();
            }
        }
        /**//// <summary>
        /// 获取唯一设置的值
        /// </summary>
        public string UniqueValue
        {
            get
            {
                string Result = string.Empty;
                if (null != this.Uniques)
                {
                    foreach (object Value in this.Uniques.Values)
                    {
                        if (Value != null)
                        {
                            Result = Result + string.Format("{0},", Value);
                        }
                    }
                }
                return Result.TrimEnd(new char[] { ',' });
            }
        }
        /**//// <summary>
        /// 获取更新语句
        /// </summary>
        public string UpdateStatement
        {
            get
            {
                if ((this._UpdateStatement == null) || (this._UpdateStatement.Count <= 0))
                {
                    throw new Exception("没有生成更新语句");
                }
                string tmpStatement = "";
                foreach (string str in this._UpdateStatement)
                {
                    tmpStatement = tmpStatement + this.GetUpdateStatement(str) + ",";
                }
                if (tmpStatement != "")
                {
                    tmpStatement = tmpStatement.TrimEnd(new char[] { ',' });
                }
                return tmpStatement;
            }
        }

        安全数据转换#region 安全数据转换
        /**//// <summary>
        /// 最小日期类型
        /// </summary>
        public readonly DateTime NullDateTime = DateTime.Parse("1900-01-01");
        /**//// <summary>
        /// 主键使用空类型
        /// </summary>
        public readonly int NullInt = 0;

        /**//// <summary>
        /// 转换对象到bool型
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        protected bool DbObjectTobool(object o)
        {
            if (o.GetType() == typeof(DBNull))
            {
                return false;
            }
            try
            {
                bool Result = false;
                if (bool.TryParse(o.ToString(), out Result))
                    return false;
                return Result;
            }
            catch
            {
                return false;
            }
        }

        /**//// <summary>
        /// 
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        protected byte DbObjectTobyte(object o)
        {
            if (DBNull.Value == o)
            {
                return 0;
            }
            try
            {
                return byte.Parse(o.ToString());
            }
            catch
            {
                return 0;
            }
        }
        /**//// <summary>
        /// 
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        protected DateTime DbObjectToDate(object o)
        {
            if (DBNull.Value == o)
            {
                return NullDateTime;
            }
            try
            {
                return DateTime.Parse(o.ToString());
            }
            catch
            {
                return NullDateTime;
            }
        }
        /**//// <summary>
        /// 
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        protected DateTime DbObjectToDateTime(object o)
        {
            if (DBNull.Value == o)
            {
                return NullDateTime;
            }
            try
            {
                return DateTime.Parse(o.ToString());
            }
            catch
            {
                return NullDateTime;
            }
        }
        /**//// <summary>
        /// 
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        protected decimal DbObjectTodecimal(object o)
        {
            if (DBNull.Value == o)
            {
                return 0M;
            }
            try
            {
                return decimal.Parse(o.ToString());
            }
            catch
            {
                return 0M;
            }
        }
        /**//// <summary>
        /// 
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        protected int DbObjectToint(object o)
        {
            if (DBNull.Value == o)
            {
                return 0;
            }
            int Result = 0;
            try
            {
                if ((o != DBNull.Value) && (o != null))
                {
                    Result = int.Parse(o.ToString().Trim());
                }
            }
            catch
            {
                Result = 0;
            }
            return Result;
        }
        /**//// <summary>
        /// 
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        protected Guid DbObjectToGuid(object o)
        {
            if (DBNull.Value == o)
            {
                return Guid.Empty;
            }
            Guid Result = Guid.Empty;
            try
            {

                Result = new Guid(o.ToString().Trim());

            }
            catch
            {
                Result = Guid.Empty;
            }
            return Result;
        }
        /**//// <summary>
        /// 
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        protected short DbObjectToshort(object o)
        {
            if (DBNull.Value == o)
            {
                return 0;
            }
            try
            {
                return short.Parse(o.ToString().Trim());
            }
            catch
            {
                return 0;
            }
        }
        /**//// <summary>
        /// 
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        protected string DbObjectTostring(object o)
        {
            if (DBNull.Value == o)
            {
                return "";
            }
            string Result = "";
            try
            {
                if ((o != DBNull.Value) && (o != null))
                {
                    Result = (string)o;
                }
            }
            catch
            {
                Result = null;
            }
            return Result;
        }
        #endregion

    }

 

 

通用数据访问层的代码如下：

 

dal接口
/**//// <summary>
    /// 数据访问层接口
    /// 为单元测试的mock作
    /// </summary>
    public interface IDABase : IDisposable
    {

        /**//// <summary>
        /// 检查实体中是否有关键字重复
        /// 关键字是And的关系
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns>返回检查结果，如果记录唯一，返回true,反之false</returns>
        bool CheckUnique(EntityBase e);
        /**//// <summary>
        /// 关闭当前连接或事务
        /// </summary>
        void Close();
        /**//// <summary>
        /// 提交当前事务
        /// </summary>
        void Commit();
        /**//// <summary>
        /// 根据条件删除记录
        /// 注此方法有注入的可能
        /// 在拼接条件语句时就注意防范
        /// </summary>
        /// <param name="e">实体表</param>
        /// <param name="WhereCondition">条件</param>
        /// <returns>成功返回更新的条数</returns>
        int Delete(EntityBase e, string WhereCondition);
        /**//// <summary>
        /// 删除实体
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns></returns>
        int Delete(EntityBase e);
        /**//// <summary>
        /// 删除多个实体
        /// 实体中用KeyValueString表示当前要删除的Key集
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns></returns>
        int Deletes(EntityBase e);

        /**//// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="StoreName">存储过程名</param>
        /// <param name="Parame">参数</param>
        /// <returns>返回out参数</returns>
        IDictionary<string, object> ExceStore(string StoreName, IList<KMTool.Common.Parameter> Parame);
        /**//// <summary>
        /// 执行存储过程，返回数据集
        /// </summary>
        /// <param name="StoreName">存储过程名</param>
        /// <param name="Parame">参数</param>
        /// <returns>返回数据集</returns>
        DataSet ExceStoreGetDataSet(string StoreName, IList<KMTool.Common.Parameter> Parame);
        /**//// <summary>
        /// 执行存储过程，返回单个变量
        /// </summary>
        /// <param name="StoreName">存储过程名</param>
        /// <param name="Parame">参数</param>
        /// <returns>返回对象</returns>
        object ExceStoreGetObject(string StoreName, IList<KMTool.Common.Parameter> Parame);
        /**//// <summary>
        /// 根据实体的外键名，查询指定外键的实体
        /// </summary>
        /// <param name="e">实体</param>
        /// <param name="ForeignKeyName">外键名</param>
        /// <returns>返回实体</returns>
        EntityBase GetEntityByForeignKey(EntityBase e, string ForeignKeyName);
        /**//// <summary>
        /// 通用分页查询函数
        /// </summary>
        /// <param name="TableName">表名，注前后不能有空格</param>
        /// <param name="FieldNames">字段列表</param>
        /// <param name="OrderName">排序名称</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="PageIndex">页面编号</param>
        /// <param name="IsReCount">是否返回记录数</param>
        /// <param name="OrderType">排序方式，1升序 0降序</param>
        /// <param name="strWhere">查询条件</param>
        /// <returns></returns>
        DataSet GetRecordByPageOrder(string TableName, string FieldNames, string OrderName, int PageSize, int PageIndex, bool IsReCount, bool OrderType, string strWhere);
        /**//// <summary>
        /// 插入记录
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns></returns>
        int Insert(EntityBase e);
        /**//// <summary>
        /// 插入并返回记录编号
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns></returns>
        object InsertReturnId(EntityBase e);
        /**//// <summary>
        /// 单元测试设置
        /// 如果为true，表示不用提交到数据库，并且所有操作都启用事务
        /// </summary>
        bool IsUnitTest { get; set; }
        /**//// <summary>
        /// 回滚当前务
        /// </summary>
        void Rollback();
        /**//// <summary>
        /// 查询记录
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns></returns>
        bool Select(EntityBase e);
        /**//// <summary>
        /// 根据实体外键名查询外键数据集
        /// </summary>
        /// <param name="e">实体</param>
        /// <param name="ForeignKeyName">外键名</param>
        /// <returns></returns>
        DataSet SelectByForeignKey(EntityBase e, string ForeignKeyName);
        /**//// <summary>
        /// 动态查询实体记录集
        /// 请注意此方法有注入可能
        /// </summary>
        /// <param name="e">实体</param>
        /// <param name="WhereCondition">条件语句</param>
        /// <param name="OrderByExpression">排序表达式</param>
        /// <returns></returns>
        DataSet SelectDynamic(EntityBase e, string WhereCondition, string OrderByExpression);
        /**//// <summary>
        /// 更新语句
        /// 请注意此方法有注入可能
        /// </summary>
        /// <param name="e">实体</param>
        /// <param name="UpdateStatement">更新字段</param>
        /// <param name="WhereCondition">条件语句</param>
        /// <returns></returns>
        int Update(EntityBase e, string UpdateStatement, string WhereCondition);
        /**//// <summary>
        /// 条件更新语句
        /// 请注意此方法有注入可能
        /// </summary>
        /// <param name="e">实体</param>
        /// <param name="WhereCondition">条件语句</param>
        /// <returns></returns>
        int Update(EntityBase e, string WhereCondition);
        /**//// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns></returns>
        int Update(EntityBase e);

        执行sql语句#region 执行sql语句
        /**//// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="Sql">语句</param>
        /// <returns>返回数据集</returns>
        DataSet GetData(string Sql);
        /**//// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="Sql">语句</param>
        void ExcuteSql(string Sql);
        /**//// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="Sql">语句</param>
        /// <returns>返回单个对象</returns>
        object GetSingle(string Sql);
        /**//// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="Sql">语句</param>
        /// <param name="Parame">参数</param>
        /// <returns>返回数据集</returns>
        DataSet GetData(string Sql, IList<KMTool.Common.Parameter> Parame);
        /**//// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="Sql">语句</param>
        /// <param name="Parame">参数</param>
        void ExcuteSql(string Sql, IList<KMTool.Common.Parameter> Parame);
        /**//// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="Sql">语句</param>
        /// <param name="Parame">参数</param>
        /// <returns>返回单个对象</returns>
        object GetSingle(string Sql, IList<KMTool.Common.Parameter> Parame);
        #endregion
    } 

 

基于企业库实现
public abstract class DABase : IDABase
    {
        #region 连接属性
        /// <summary>
        /// 当前的事务级别
        /// </summary>
        protected IsolationLevel IsolationLevel = IsolationLevel.ReadCommitted;
        /// <summary>
        /// 事务对象
        /// </summary>
        protected DbTransaction sqlTran;
        /// <summary>
        /// 当前连接
        /// </summary>
        protected DbConnection conn;
        /// <summary>
        /// 当前是否启用事务
        /// </summary>
        protected bool _IsTransaction;
        #endregion

        #region ctor
        /// <summary>
        /// 在自定义了连接字符串的取用方式后，请修改之
        /// 默认启用事务
        /// </summary>
        protected DABase()
        {
            _IsTransaction = false;
            InitFunctionEx(IsolationLevel.ReadCommitted, null);
        }
        /// <summary>
        /// 在自定义了连接字符串的取用方式后，请修改之
        /// </summary>
        protected DABase(bool IsTransaction)
        {
            _IsTransaction = IsTransaction;
            InitFunctionEx(IsolationLevel.ReadCommitted, null);
        }
        /// <summary>
        /// 在自定义了连接字符串的取用方式后，请修改之
        /// </summary>
        protected DABase(bool IsTransaction, string ConnectionName)
        {
            _IsTransaction = IsTransaction;
            InitFunctionEx(IsolationLevel.ReadCommitted, ConnectionName);
        }
        /// <summary>
        /// 在自定义了连接字符串的取用方式后，请修改之
        /// </summary>
        protected DABase(IsolationLevel TranIsolationLevel)
        {
            _IsTransaction = true;
            InitFunctionEx(TranIsolationLevel, null);
        }
        /// <summary>
        /// 在自定义了连接字符串的取用方式后，请修改之
        /// </summary>
        protected DABase(IsolationLevel TranIsolationLevel, string ConnectionName)
        {
            _IsTransaction = true;
            InitFunctionEx(TranIsolationLevel, ConnectionName);
        }
        /// <summary>
        /// 初始化数据连接或事务
        /// </summary>
        /// <param name="IsolationLevel">事务级别</param>
        /// <param name="ConnectionName">连接字符串</param>
        private void InitFunctionEx(IsolationLevel IsolationLevel, string ConnectionName)
        {
            this.IsolationLevel = IsolationLevel;
            InitFunction(IsolationLevel, ConnectionName);
        }
        /// <summary>
        /// 初始化数据连接或事务
        /// </summary>
        /// <param name="IsolationLevel">事务级别</param>
        /// <param name="ConnectionName">连接字符串</param>
        protected abstract void InitFunction(IsolationLevel IsolationLevel, string ConnectionName);
        #endregion

        #region 测试设置
        protected bool _IsUnitTest;
        /// <summary>
        /// 测试设置
        /// </summary>
        public bool IsUnitTest
        {
            set
            {
                _IsUnitTest = value;
            }
            get
            {
                return _IsUnitTest;
            }
        }
        #endregion

        #region 事务相关
        /// <summary>
        /// 提交事务
        /// 如果设置为单元测试模式，就是回滚事务
        /// </summary>
        public void Commit()
        {
            if (!_IsTransaction)
                throw new NoDbTransactionException("提交事务。");
            if (sqlTran == null) return;
            if (IsUnitTest)
                sqlTran.Rollback();
            else
                sqlTran.Commit();
        }
        /// <summary>
        /// 回滚事务
        /// </summary>
        public void Rollback()
        {
            if (!_IsTransaction)
                throw new NoDbTransactionException("回滚事务。");
            if (sqlTran != null)
                sqlTran.Rollback();
        }

        #endregion

        #region 数据库连接
        /// <summary>
        /// 关闭链接或事务
        /// </summary>
        public void Close()
        {
            if (_IsTransaction)
            {
                if (sqlTran != null)
                {
                    DbConnection connection = sqlTran.Connection;
                    if (connection != null)
                        connection.Close();
                }
            }
            else
                if (conn != null)
                    conn.Close();
        }
        #endregion

        #region IDisposable 成员
        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            this.Close();
        }

        #endregion

        #region IDABase 成员

        /// <summary>
        /// 检查唯一性约束,所有关键字是与的关系
        /// 只要有一个相同就认为不唯一
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns></returns>
        public abstract bool CheckUnique(EntityBase e);
        /// <summary>
        /// 根据条件删除实体
        /// </summary>
        /// <param name="e">实体</param>
        /// <param name="WhereCondition">条件</param>
        /// <returns>返回删除记录的条数</returns>
        public abstract int Delete(EntityBase e, string WhereCondition);
        /// <summary>
        /// 根据主键删除实体
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns>返回删除记录的条数</returns>
        public abstract int Delete(EntityBase e);
        /// <summary>
        /// 根据主键列表删除实体
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns>返回删除记录的条数</returns>
        public abstract int Deletes(EntityBase e);
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="StoreName">存储过程名</param>
        /// <param name="Parame">参数列表</param>
        /// <returns>返回对应的输出和返回参数</returns>
        public abstract IDictionary<string, object> ExceStore(string StoreName, IList<Parameter> Parame);
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="StoreName">存储过程名</param>
        /// <param name="Parame">参数列表</param>
        /// <returns>返回数据集</returns>
        public abstract DataSet ExceStoreGetDataSet(string StoreName, IList<Parameter> Parame);
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="StoreName">存储过程名</param>
        /// <param name="Parame">参数列表</param>
        /// <returns>返回单个对象</returns>
        public abstract object ExceStoreGetObject(string StoreName, IList<Parameter> Parame);
        /// <summary>
        /// 根据实体外键查询外键实体
        /// </summary>
        /// <param name="e">实体</param>
        /// <param name="ForeignKeyName">外键名</param>
        /// <returns>返回实体</returns>
        public abstract EntityBase GetEntityByForeignKey(EntityBase e, string ForeignKeyName);
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="TableName">表名或视图名</param>
        /// <param name="FieldNames">字段列表</param>
        /// <param name="OrderName">排序字段</param>
        /// <param name="PageSize">每页大小</param>
        /// <param name="PageIndex">页索引</param>
        /// <param name="IsReCount">返回记录总数</param>
        /// <param name="OrderType">排序类型,0表示升序 1 表示降序 其它程序自动控制，仅仅使用OrderName字段的值，作为排序条件</param>
        /// <param name="strWhere">过滤条件</param>
        /// <returns>返回数据集</returns>
        public abstract DataSet GetRecordByPageOrder(string TableName, string FieldNames, string OrderName, int PageSize, int PageIndex, bool IsReCount, bool OrderType, string strWhere);
        /// <summary>
        /// 插入实体
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns></returns>
        public abstract int Insert(EntityBase e);
        /// <summary>
        /// 插入实体
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns></returns>
        public abstract object InsertReturnId(EntityBase e);
        /// <summary>
        /// 查询实体
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns></returns>
        public abstract bool Select(EntityBase e);
        /// <summary>
        /// 查询外键数据集
        /// </summary>
        /// <param name="e">实体</param>
        /// <param name="ForeignKeyName">外键名</param>
        /// <returns>数据集</returns>
        public abstract DataSet SelectByForeignKey(EntityBase e, string ForeignKeyName);
        /// <summary>
        /// 
        /// </summary>
        /// <param name="e">实体</param>
        /// <param name="WhereCondition"></param>
        /// <param name="OrderByExpression"></param>
        /// <returns></returns>
        public abstract DataSet SelectDynamic(EntityBase e, string WhereCondition, string OrderByExpression);
        /// <summary>
        /// 根据条件更新实体记录
        /// </summary>
        /// <param name="e">实体</param>
        /// <param name="UpdateStatement">更新语句</param>
        /// <param name="WhereCondition">条件语句</param>
        /// <returns>返回更新记录数</returns>
        public abstract int Update(EntityBase e, string UpdateStatement, string WhereCondition);
        /// <summary>
        /// 根据条件更新实体
        /// </summary>
        /// <param name="e">实体</param>
        /// <param name="WhereCondition">条件语句</param>
        /// <returns>条件语句</returns>
        public abstract int Update(EntityBase e, string WhereCondition);
        /// <summary>
        /// 更新实体
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns>条件语句</returns>
        public abstract int Update(EntityBase e);

        protected DbType GetKeyDBType(object ob)
        {
            Type type = ob.GetType();
            if (type == typeof(string))
                return DbType.AnsiString;
            if (type == typeof(int))
                return DbType.Int32;
            if (type == typeof(Guid))
                return DbType.Guid;
            return DbType.AnsiString;
        }

        //protected string CSharpType(ColumnSchema column)
        //{
        //    if (column.Name.EndsWith("TypeCode")) return column.Name;

        //    switch (column.DataType)
        //    {
        //        case DbType.AnsiString: return "string";
        //        case DbType.AnsiStringFixedLength: return "string";
        //        case DbType.Binary: return "byte[]";
        //        case DbType.Boolean: return "bool";
        //        case DbType.Byte: return "byte";
        //        case DbType.Currency: return "decimal";
        //        case DbType.Date: return "DateTime";
        //        case DbType.DateTime: return "DateTime";
        //        case DbType.Decimal: return "decimal";
        //        case DbType.Double: return "double";
        //        case DbType.Guid: return "Guid";
        //        case DbType.Int16: return "short";
        //        case DbType.Int32: return "int";
        //        case DbType.Int64: return "long";
        //        case DbType.Object: return "object";
        //        case DbType.SByte: return "sbyte";
        //        case DbType.Single: return "float";
        //        case DbType.String: return "string";
        //        case DbType.StringFixedLength: return "string";
        //        case DbType.Time: return "TimeSpan";
        //        case DbType.UInt16: return "ushort";
        //        case DbType.UInt32: return "uint";
        //        case DbType.UInt64: return "ulong";
        //        case DbType.VarNumeric: return "decimal";
        //        default:
        //            {
        //                return "__UNKNOWN__" + column.NativeType;
        //            }
        //    }
        //}

        #endregion

        #region IDABase Members


        public abstract DataSet GetData(string Sql);

        public abstract void ExcuteSql(string Sql);

        public abstract object GetSingle(string Sql);

        public abstract DataSet GetData(string Sql, IList<Parameter> Parame);

        public abstract void ExcuteSql(string Sql, IList<Parameter> Parame);

        public abstract object GetSingle(string Sql, IList<Parameter> Parame);

        #endregion
    }
 /// <summary>
    /// 基于企业库的数据访问层
    /// </summary>
    public sealed class EnterpriseLibraryDABase : KMTool.Common.Dal.DABase
    {
        #region ctor
        /// <summary>
        /// 数据库对象
        /// </summary>
        protected Database db;
        /// <summary>
        /// 在自定义了连接字符串的取用方式后，请修改之
        /// 默认启用事务
        /// </summary>
        public EnterpriseLibraryDABase():base()
        {
        }
        /// <summary>
        /// 在自定义了连接字符串的取用方式后，请修改之
        /// </summary>
        public EnterpriseLibraryDABase(bool IsTransaction)
            : base(IsTransaction)
        {
        }
        /// <summary>
        /// 在自定义了连接字符串的取用方式后，请修改之
        /// </summary>
        public EnterpriseLibraryDABase(bool IsTransaction, string ConnectionName)
            : base(IsTransaction,ConnectionName)
        {
        }
        /// <summary>
        /// 在自定义了连接字符串的取用方式后，请修改之
        /// </summary>
        public EnterpriseLibraryDABase(IsolationLevel TranIsolationLevel)
            : base(TranIsolationLevel)
        {
        }
        /// <summary>
        /// 在自定义了连接字符串的取用方式后，请修改之
        /// </summary>
        public EnterpriseLibraryDABase(IsolationLevel TranIsolationLevel, string ConnectionName)
            : base(TranIsolationLevel,ConnectionName)
        {
        }
        protected override void InitFunction(IsolationLevel TranIsolationLevel, string ConnectionName)
        {
            if (string.IsNullOrEmpty(ConnectionName))
                db = DatabaseFactory.CreateDatabase();
            else
                db = DatabaseFactory.CreateDatabase(ConnectionName);
            conn = db.CreateConnection();
            conn.Open();
            if (_IsTransaction)
                sqlTran = conn.BeginTransaction(TranIsolationLevel);
        }
        #endregion      

        #region 数据库方法

        protected void InitDbCommand(DbCommand cmd)
        {
            cmd.Connection = conn;
            if (_IsTransaction)
                cmd.Transaction = sqlTran;
            //else
                
        }

        #region 基本方法
        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns>返回影响的行数</returns>
        public override  int Insert(EntityBase e)
        {
            DbCommand cmd = db.GetStoredProcCommand(GetProcName(e, "Insert", null));
            IList<Parameter> list = e.BuildParams();
            AddFillParameter(cmd,list);
            return ExecuteNonQuery(cmd);
        }

        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns>返回自增长ID</returns>
        public override object InsertReturnId(EntityBase e)
        {
            DbCommand cmd = db.GetStoredProcCommand(GetProcName(e, "Insert", null));
            IList<Parameter> list = e.BuildParams();
            AddFillParameter(cmd,list);
            ExecuteNonQuery(cmd);
            return GetParamValue(cmd.Parameters, e.GetKeyName());
        }
        /// <summary>
        /// 修改记录
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns>返回影响的行数</returns>
        public override int Update(EntityBase e)
        {

            DbCommand cmd = db.GetStoredProcCommand(GetProcName(e, "Update", null));
            IList<Parameter> list = e.BuildParamsEdit();
            AddFillParameter(cmd,list);
            return ExecuteNonQuery(cmd);
        }

        /// <summary>
        /// 删除记录
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns>返回影响的行数</returns>
        public override int Delete(EntityBase e)
        {
            DbCommand cmd = db.GetStoredProcCommand(GetProcName(e, "Delete", null));
            db.AddInParameter(cmd, e.GetKeyName(), GetKeyDBType(e.GetKeyValue()), e.GetKeyValue());
            InitDbCommand(cmd);
            return ExecuteNonQuery(cmd);
        }

        /// <summary>
        /// 删除多条记录
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns>返回影响的行数</returns>
        public override int Deletes(EntityBase e)
        {
            DbCommand cmd = db.GetStoredProcCommand("pro_Comon_Delete");
            db.AddInParameter(cmd, "TableName", DbType.String, e.GetTableName());
            db.AddInParameter(cmd, "Ids", DbType.String, e.KeyValueString.Replace("'", "''"));
            return ExecuteNonQuery(cmd);
        }

        /// <summary>
        /// 读取记录
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns>返回是否成功</returns>
        public override Boolean Select(EntityBase e)
        {
            DbCommand cmd = db.GetStoredProcCommand(GetProcName(e, "Select", null));
            db.AddInParameter(cmd, e.GetKeyName(), GetKeyDBType(e.GetKeyValue()), e.GetKeyValue());
            DataSet ds = ExecuteDataSet(cmd);
            bool Result = (1 == ds.Tables[0].Rows.Count);
            if (Result)
                e.Assign(ds);
            return Result;
        }
        

        /// <summary>
        /// 检查惟一性

        /// </summary>
        /// <param name="e">实体</param>
        /// <returns>返回是否惟一</returns>
        public override bool CheckUnique(EntityBase e)
        {
            if (null == e.Uniques || e.Uniques.Count == 0)
                return true;

            DbCommand cmd = db.GetStoredProcCommand(string.Format("pro_Comon_CheckUnique{0}", e.Uniques.Count));
            db.AddInParameter(cmd, "TableName", DbType.String, e.GetTableName());
            int i = 0;
            foreach (string Key in e.Uniques.Keys)
            {
                db.AddInParameter(cmd, "FieldName" + (i + 1), DbType.String, Key);
                db.AddInParameter(cmd, "Value" + (i + 1), DbType.String, e.Uniques[Key].ToString().Replace("'", "''"));
                i++;
            }
            db.AddInParameter(cmd, "ExcludeKeyName", DbType.String, e.GetKeyName());
            db.AddInParameter(cmd, "ExcludeKeyValue", DbType.String, e.GetKeyValue().ToString());
            db.AddOutParameter(cmd, "IsNew", DbType.Boolean, 1);

            ExecuteNonQuery(cmd);
            return bool.Parse(GetParamValue(cmd.Parameters, "IsNew").ToString());
        }

        #endregion

        #region 外键相关
        /// <summary>
        /// 读取外键的列表
        /// </summary>
        /// <param name="e">实体</param>
        /// <param name="ForeignKeyName">外键名</param>
        /// <returns>返回是否成功</returns>
        public override DataSet SelectByForeignKey(EntityBase e, string ForeignKeyName)
        {
            EntityBase eForeign = e.GetEntityByForeignKey(ForeignKeyName);
            DbCommand cmd = db.GetStoredProcCommand(GetProcName(eForeign, "Select", "sAll"));
            return ExecuteDataSet(cmd);
        }
        /// <summary>
        /// 读取外键记录
        /// </summary>
        /// <param name="e">实体</param>
        /// <param name="ForeignKeyName">外键名称</param>
        /// <returns>成功返回外键实体，失败失败返回null</returns>
        public override EntityBase GetEntityByForeignKey(EntityBase e, string ForeignKeyName)
        {
            EntityBase eForeign = e.GetEntityByForeignKey(ForeignKeyName);
            DbCommand cmd = db.GetStoredProcCommand(GetProcName(eForeign, "Select", null));
            db.AddInParameter(cmd, eForeign.GetKeyName(), GetKeyDBType(e.GetValueByName(ForeignKeyName)), e.GetValueByName(ForeignKeyName));
            DataSet ds = ExecuteDataSet(cmd);
            bool Result = (1 == ds.Tables[0].Rows.Count);
            if (Result)
                eForeign.Assign(ds);
            else
                eForeign = null;
            return eForeign;
        }
        #endregion

        #region 有注入的方法,此类方法要控制注入

        /*
         * 请使用公共方法中的：SafeStr函数替换
         * zhoubin.Model.GlobalFunction 
        public static string SafeSql(string s)
         * */


        /// <summary>
        /// 动态更新表
        /// </summary>
        /// <param name="e">实体</param>
        /// <param name="WhereCondition">条件</param>
        /// <returns>返回是否成功</returns>
        public override int Update(EntityBase e, string WhereCondition)
        {
            return Update(e, e.UpdateStatement, WhereCondition);
        }

        /// <summary>
        /// 动态更新表
        /// 此方法有sql注入的可能，
        /// 请在参数中加入对对注入的控制
        /// </summary>
        /// <param name="e">实体</param>
        /// <param name="UpdateStatement">更新字段</param>        
        /// <param name="WhereCondition">条件</param>
        /// <returns>返回是否成功</returns>
        public override int Update(EntityBase e, string UpdateStatement, string WhereCondition)
        {
            DbCommand cmd = db.GetStoredProcCommand(GetProcName(e, "Update", "Dynamic"));
            db.AddInParameter(cmd, "UpdateStatement", DbType.String, UpdateStatement);
            db.AddInParameter(cmd, "WhereCondition", DbType.String, WhereCondition);
            return ExecuteNonQuery(cmd);
        }

        /// <summary>
        /// 删除记录多条记录
        /// 此方法有sql注入的可能，
        /// 请在参数中加入对对注入的控制
        /// </summary>
        /// <param name="e">实体</param>
        /// <param name="WhereCondition">条件</param>
        /// <returns>返回影响的行数</returns>
        public override int Delete(EntityBase e, string WhereCondition)
        {
            DbCommand cmd = db.GetStoredProcCommand(GetProcName(e, "Delete", "sDynamic"));
            db.AddInParameter(cmd, "WhereCondition", DbType.String, WhereCondition);
            return ExecuteNonQuery(cmd);
        }

        /// <summary>
        /// 读取记录集,
        /// 此方法有sql注入的可能，
        /// 请在参数中加入对对注入的控制
        /// </summary>
        /// <param name="e">实体</param>
        /// <param name="WhereCondition">条件</param>
        /// <param name="OrderByExpression">排序条件</param>
        /// <returns>返回记录集</returns>
        public override DataSet SelectDynamic(EntityBase e, string WhereCondition, string OrderByExpression)
        {
            DbCommand cmd = db.GetStoredProcCommand(GetProcName(e, "Select", "sDynamic"));
            db.AddInParameter(cmd, "WhereCondition", DbType.String, WhereCondition);
            db.AddInParameter(cmd, "OrderByExpression", DbType.String, OrderByExpression);
            return ExecuteDataSet(cmd);
        }
        /// <summary>
        /// 分页查询方法
        /// </summary>
        /// <param name="TableName">表名或视图名</param>
        /// <param name="FieldNames">选择的字段</param>
        /// <param name="OrderName">排序类型</param>
        /// <param name="PageSize">每页长度</param>
        /// <param name="PageIndex">获取页面编号</param>
        /// <param name="IsReCount">是否返回总记录数</param>
        /// <param name="OrderType">排序类型，1子降序，0升序</param>
        /// <param name="strWhere">过滤条件，注意注入攻击防范</param>
        /// <returns>Table 0 返回查询的数据集，Table 1 返回记录总数</returns>
        public override DataSet GetRecordByPageOrder(string TableName, string FieldNames, string OrderName,
                                            int PageSize, int PageIndex, bool IsReCount, bool OrderType, string strWhere)
        {
            DbCommand cmd = db.GetStoredProcCommand("pro_GetRecordByPageOrder");
            db.AddInParameter(cmd, "@tblName", DbType.String, GlobalFunction.SafeSql(TableName));
            db.AddInParameter(cmd, "@fldName", DbType.String, GlobalFunction.SafeSql(FieldNames));
            db.AddInParameter(cmd, "@OrderfldName", DbType.String, GlobalFunction.SafeSql(OrderName));
            db.AddInParameter(cmd, "@PageSize", DbType.Int32, PageSize);
            db.AddInParameter(cmd, "@PageIndex", DbType.Int32, PageIndex);
            db.AddInParameter(cmd, "@IsReCount", DbType.Boolean, IsReCount);
            db.AddInParameter(cmd, "@OrderType", DbType.Boolean, OrderType);
            db.AddInParameter(cmd, "@strWhere", DbType.String, strWhere);
            return ExecuteDataSet(cmd);
        }
        #endregion

        #region 辅助方法
        /// <summary>
        /// 获取指定的返回参数

        /// </summary>
        /// <param name="p">参数数组</param>
        /// <param name="Name">返回参数的名称</param>
        /// <returns>没有找到返回null</returns>
        private static object GetParamValue(DbParameterCollection p, string Name)
        {
            if (p == null) throw new ArgumentNullException("p");
            foreach (DbParameter sqlp in p)
            {
                if ((sqlp.Direction == ParameterDirection.Output || sqlp.Direction == ParameterDirection.InputOutput) && sqlp.ParameterName == Name)
                {
                    return sqlp.Value;
                }
            }
            return null;
        }
        ///// <summary>
        ///// 获得存储过程名称前缀
        ///// </summary>
        ///// <param name="e">实体</param>
        ///// <returns>返回存储过程名称前缀</returns>
        //private string GetProcNamePrefix(EntityBase e, string Operate)
        //{
        //    return "pro_" + Operate + e.GetTableName();
        //}
        /// <summary>
        /// 获得存储过程名称前缀
        /// </summary>
        /// <param name="e">实体</param>
        /// <param name="Operate">操作名称</param>
        /// <param name="Name">附加名称</param>
        /// <returns>返回存储过程名称前缀</returns>
        private static string GetProcName(EntityBase e, string Operate, string Name)
        {
            return string.IsNullOrEmpty(Name) ? string.Format("pro_{0}{1}", Operate, e.GetTableName()) : string.Format("pro_{0}{1}{2}", Operate, e.GetTableName(), Name);
            // return "pro_" + Operate + e.GetTableName();
        }
        private void AddFillParameter(DbCommand cmd, IList<Parameter> list)
        {
            if (list == null)
                return;
            foreach (Parameter p in list)
            {
                db.AddParameter(cmd, p.Name, p.dbType, p.Direction, null,DataRowVersion.Default, p.Value);
                
                //if (p.Direction)
                //    db.AddInParameter(cmd, p.Name, p.dbType, p.Value);
                //else
                //    db.AddOutParameter(cmd, p.Name, p.dbType, 300);
            }
        }
        private int ExecuteNonQuery(DbCommand cmd)
        {
            cmd.Connection = conn;
            return _IsTransaction?db.ExecuteNonQuery(cmd, sqlTran):db.ExecuteNonQuery(cmd);
        }
        private DataSet ExecuteDataSet(DbCommand cmd)
        {
            cmd.Connection = conn;
            return _IsTransaction ? db.ExecuteDataSet(cmd, sqlTran) : db.ExecuteDataSet(cmd);
        }
        private object ExecuteScalar(DbCommand cmd)
        {
            cmd.Connection = conn;
            return _IsTransaction ? db.ExecuteScalar(cmd, sqlTran) : db.ExecuteScalar(cmd);
        }
        #endregion

        #endregion

        #region 通用存储过程执行方法，为子类准备
        /// <summary>
        /// 通用存储过程执行方法
        /// </summary>
        /// <param name="StroreName">存储过程名</param>
        /// <param name="Parame">参数</param>
        /// <returns>返回out参数</returns>
        public override IDictionary<string, object> ExceStore(string StoreName, IList<Parameter> Parame)
        {
            IDictionary<string, object> dic = new Dictionary<string, object>();
            DbCommand cmd = db.GetStoredProcCommand(StoreName);            
            AddFillParameter(cmd, Parame);
            ExecuteNonQuery(cmd);
            foreach (Parameter p in Parame)
            {
                if (p.Direction == ParameterDirection.Input) 
                    continue;
                dic.Add(p.Name, GetParamValue(cmd.Parameters, p.Name));
            }
            return dic;
        }
        /// <summary>
        /// 通用执行方法，返回数据集
        /// 不支持out参数
        /// </summary>
        /// <param name="StroreName"></param>
        /// <param name="Parame"></param>
        /// <returns></returns>
        public override DataSet ExceStoreGetDataSet(string StoreName, IList<Parameter> Parame)
        {
            DbCommand cmd = db.GetStoredProcCommand(StoreName);
            AddFillParameter(cmd, Parame);
            return ExecuteDataSet(cmd);
        }
        public override object ExceStoreGetObject(string StoreName, IList<Parameter> Parame)
        {
            DbCommand cmd = db.GetStoredProcCommand(StoreName);
            AddFillParameter(cmd, Parame);
            return ExecuteScalar(cmd);
        }
        #endregion

        #region Sql执行
        public override DataSet GetData(string Sql)
        {
            return GetData(Sql,null);
        }

        public override void ExcuteSql(string Sql)
        {
            ExcuteSql(Sql, null);
        }

        public override object GetSingle(string Sql)
        {
            return GetSingle(Sql, null);
        }

        public override DataSet GetData(string Sql, IList<Parameter> Parame)
        {
            DbCommand cmd = db.GetSqlStringCommand(Sql);
            AddFillParameter(cmd, Parame);
            return ExecuteDataSet(cmd);
        }

        public override void ExcuteSql(string Sql, IList<Parameter> Parame)
        {
            DbCommand cmd = db.GetSqlStringCommand(Sql);
            AddFillParameter(cmd, Parame);
            ExecuteNonQuery(cmd);
        }

        public override object GetSingle(string Sql, IList<Parameter> Parame)
        {
            DbCommand cmd = db.GetSqlStringCommand(Sql);
            AddFillParameter(cmd, Parame);
            return ExecuteScalar(cmd);
        }
        #endregion
    }

 

通过以上的定义，就可以在修改数据结构时仅仅生成一下实体模型就可以了。

测试项目下载

测试项目数据库下载

转载于:https://www.cnblogs.com/LifelongLearning/archive/2009/10/03/1577795.html