反射+Dapper.NET搭建ORM框架

最新推荐文章于 2025-04-11 09:22:33 发布

原创最新推荐文章于 2025-04-11 09:22:33 发布 · 1k 阅读

7 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

C#反射的使用

本文介绍如何使用Dapper.NET结合反射技术，实现高度复用性的ORM框架，极大减少代码量，提升开发效率。通过示例展示了BLL和DAL层的通用CRUD操作，以及如何利用反射动态生成SQL语句。

最近在工作过程中发现，还有很多猿在使用动软代码生成器，或者自己手写DAL类，每个表一个DAL类，并且每个DAL都写一堆CRUD的方法，除了表名，字段名不一样，其他的代码基本都是重复的，这完全不符合面向对象编程的特性，并且会造成代码冗余，复用性极低，维护不便等等问题。

下面介绍一种复用性极高的ORM框架反射+Dapper.Net

关于反射的使用这里不再说了，大家可以去查看我的文章-> 对C# 反射使用的一些整理

Dapper.NET -轻量级ORM，有多轻呢，封装一个DapperHelper类，写好连接字符串就可以调用了，相比Entity Framework这种来处理关系映射的ORM，会发现Dapper.NET既省时又省力。

为什么选择Dapper.NET？

    1、轻量，可复用性极高
    2、速度快。Dapper的速度接近与IDataReader，取列表的数据超过了DataTable。
    3、支持多种数据库。Dapper可以在所有Ado.net Providers下工作，包括sqlite, sqlce, firebird, oracle, MySQL, PostgreSQL and SQL Server
    4、可以映射一对一，一对多，多对多等多种关系。
    5、性能高。通过Emit反射IDataReader的序列队列，来快速的得到和产生对象，性能不错。
    6、支持FrameWork2.0，3.0，3.5，4.0，4.5，4.6，4.7 .......

接下来我们看一下常见的使用Dapper.NET写的BLL和DAL层代码

BLL层新增Insert方法

       public bool Insert(User model)
        {
            try
            {
              return new UserDAL().Insert(model);

            }
            catch (Exception ex)
            {
              
            }
            return false;
        }

DAL层新增Insert方法

  public bool Insert(User model, string tableName)
        {
            string sql = "insert into User(name,sex) values(@name,@sex)";
            return dapper.Execute(sql, model);
        }

从上面的代码可以看出相对于传统的ORM框架，使用Dapper.NET后，代码量明显减少了很多，但是复用性太低，如果这么写，那每个数据库表都要建这么一个BLL和DAL类，并且还要把CRUD的方法在重新写一遍。

接下来我们把反射应用上去

BLL层新增Insert方法

        public bool Insert<T>(T model, string tableName)
        {

            try
            {
               return  _baseDAL.Insert<T>(model, tableName)

            }
            catch (Exception ex)
            {
            }
            return false;
        }

DAL层新增Insert方法

        public bool Insert<T>(T model, string tableName)
        {
            string sqls = $"insert into [{tableName}]({_dapper.GetParas<T>()}) values({_dapper.GetATParas<T>()})";
            return _dapper.Execute(sqls, model);
        }

DAL层中用到的两个方法

GetParas<T>(); 通过反射动态获取 T 对象的所有属性名，假如T为User，属性有Id,Name，Sex，则执行结果为Name,Sex

        public string GetParas<T>()
        {
            StringBuilder columns = new StringBuilder();
            //StringBuilder values = new StringBuilder();
            Type t = typeof(T);
            PropertyInfo[] properties = t.GetProperties();
            foreach (var p in properties)
            {
                if (p.PropertyType.Name == "IList`1" || p.PropertyType.Name.ToUpper().Contains("LIST") || p.Name.ToUpper() == "ID")
                {
                    continue;
                }
                columns.Append(p.Name).Append(",");
            }
            return columns.Remove(columns.Length - 1, 1).ToString();
        }

GetATParas<T>(); 通过反射动态获取 T 对象的所有@+属性名，假如T为User，属性有Id,Name，Sex，则执行结果为@Name,@Sex

        public string GetATParas<T>()
        {
            StringBuilder columns = new StringBuilder();
            //StringBuilder values = new StringBuilder();
            Type t = typeof(T);
            PropertyInfo[] properties = t.GetProperties();
            foreach (var p in properties)
            {
                if (p.PropertyType.Name == "IList`1" || p.PropertyType.Name.ToUpper().Contains("LIST") || p.Name.ToUpper() == "ID")
                {
                    continue;
                }
                columns.Append("@").Append(p.Name).Append(",");
            }
            return columns.Remove(columns.Length - 1, 1).ToString();
        }

最后DAL层Insert方法的sql值为 insert into User(Name,Sex) values(@Name,@Sex);

使用反射的好处显而易见，我们不需要知道传入的对象是谁，你随便传，我把对象拿过来直接解析出你所有的属性，然后拼接成sql执行就完事了。

接下来我们在扩展几个简单的CRUD方法，然后把封装成Base类，然后作为父类给其他子类使用，其他子类只需要继承Base类就可以了

BaseBLL类

        _baseDAL = new BaseDAL();

        /// <summary>
        /// 公共全部查询方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="where"></typeparam>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public virtual List<T> SelectAll<T>(string where, string tableName)
        {
            try
            {
                List<T> lists = _baseDAL.SelectAll<T>(where, tableName);
                return lists;
            }
            catch (Exception ex)
            {
               
            }
            return null;
        }

        /// <summary>
        /// 公共新增方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public bool Insert<T>(T model, string tableName)
        {

            try
            {
               return  _baseDAL.Insert<T>(model, tableName)

            }
            catch (Exception ex)
            {
            }
            return false;
        }

        /// <summary>
        /// 公共更新方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public virtual bool Update<T>(T model, string tableName)
        {
            try
            {
               return _baseDAL.Update<T>(model, tableName);
            }
            catch (Exception ex)
            {
                
            }
            return false;
        } 

        /// <summary>
        /// 公共删除方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public virtual bool Del<T>(T model, string tableName)
        {
            try
            {
               return _baseDAL.Del<T>(model, tableName);
            }
            catch (Exception ex)
            {
                
            }
            return false;
        }

BaseDAL类

        _dapper = new DapperHelper();

        /// <summary>
        /// 公共全部查询方法
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public List<T> SelectAll<T>(string where, string tableName)
        {
            string sql = $"select * from [{tableName}] where 1=1";
            if (!string.IsNullOrEmpty(where))
            {
                sql += $" and {where}";
            }
            return _dapper.Query<T>(sql).ToList();
        }

        /// <summary>
        /// 公共新增方法
        /// </summary>
        /// <typeparam name="T">表类</typeparam>
        /// <param name="model">对象</param>
        /// <param name="tableName">表名</param>
        /// <returns>插入成功true 否则false</returns>
        public bool Insert<T>(T model, string tableName)
        {
            string sqls = $"insert into [{tableName}]({_dapper.GetParas<T>()}) values({_dapper.GetATParas<T>()})";
            return _dapper.Execute(sqls, model);
        }

        /// <summary>
        /// 公共更新方法
        /// </summary>
        /// <typeparam name="T">表类</typeparam>
        /// <param name="model">对象</param>
        /// <param name="tableName">表明</param>
        /// <returns>插入成功true 否则false</returns>
        public bool Update<T>(T model, string tableName)
        {
            string sql = $"update [{tableName}] set {_dapper.GetParasToAT<T>()} where Id=@Id";
            return _dapper.Execute(sql, model);
        }
        
        /// <summary>
        /// 公共删除方法
        /// </summary>
        /// <typeparam name="T">表类</typeparam>
        /// <param name="model">对象</param>
        /// <param name="tableName">表明</param>
        /// <returns>插入成功true 否则false</returns>
        public bool Del<T>(T model, string tableName)
        {
            string sql = $"delete [{tableName}] where Id=@Id";
            return _dapper.Execute(sql, model);
        }

DapperHelper

public class DapperHelper
    {
        /// <summary>
        /// the dapper option
        /// </summary>
        private DapperOptions _dapperOptions;
        public DapperHelper(string key = "", DapperOptions dapper = null)
        {
            if (dapper == null)
            {
                dapper = new DapperOptions();
                dapper.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ToString();
            }
            this._dapperOptions = dapper;
        }

        /// <summary>
        /// get the connection
        /// </summary>
        /// <returns></returns>
        private SqlConnection OpenConnection()
        {
            SqlConnection conn = new SqlConnection(_dapperOptions.ConnectionString);
            conn.Open();
            return conn;
        }         

        /// <summary>
        /// 返回对象属性名称(name)去除标记id name,name,name
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public string GetParas<T>()
        {
            StringBuilder columns = new StringBuilder();
            //StringBuilder values = new StringBuilder();
            Type t = typeof(T);
            PropertyInfo[] properties = t.GetProperties();
            foreach (var p in properties)
            {
                if (p.PropertyType.Name == "IList`1" || p.PropertyType.Name.ToUpper().Contains("LIST") || p.Name.ToUpper() == "ID")
                {
                    continue;
                }
                columns.Append(p.Name).Append(",");
            }
            return columns.Remove(columns.Length - 1, 1).ToString();
        }

        /// <summary>
        /// 返回对象属性名称（@name） @name,@name,@name
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <returns></returns>
        public string GetATParas<T>()
        {
            StringBuilder columns = new StringBuilder();
            //StringBuilder values = new StringBuilder();
            Type t = typeof(T);
            PropertyInfo[] properties = t.GetProperties();
            foreach (var p in properties)
            {
                if (p.PropertyType.Name == "IList`1" || p.PropertyType.Name.ToUpper().Contains("LIST") || p.Name.ToUpper() == "ID")
                {
                    continue;
                }
                columns.Append("@").Append(p.Name).Append(",");
            }
            return columns.Remove(columns.Length - 1, 1).ToString();
        }
        /// <summary>
        /// 返回 name=@name, name=@name, name=@name
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public string GetParasToAT<T>()
        {
            StringBuilder columns = new StringBuilder();
            //StringBuilder values = new StringBuilder();
            Type t = typeof(T);
            PropertyInfo[] properties = t.GetProperties();
            foreach (var p in properties)
            {
                if (p.PropertyType.Name == "IList`1" || p.PropertyType.Name.ToUpper().Contains("LIST") || p.Name.ToUpper() == "ID")
                {
                    continue;
                }
                columns.Append(p.Name).Append("=@" + p.Name + ",");
            }
            return columns.Remove(columns.Length - 1, 1).ToString();
        }

        public string GetParasToAT<T>(T model)
        {
            StringBuilder columns = new StringBuilder();
            //StringBuilder values = new StringBuilder();
            Type t = typeof(T);
            PropertyInfo[] properties = t.GetProperties();
            foreach (var p in properties)
            {
                if (p.PropertyType.Name == "IList`1" || p.PropertyType.Name.ToUpper().Contains("LIST") || p.Name.ToUpper() == "ID")
                {
                    continue;
                }
                object value = p.GetValue(model, null);
                if (!string.IsNullOrEmpty(value?.ToString()))
                {
                    columns.Append(p.Name).Append("=@" + p.Name + ",");
                }
            }
            return columns.Remove(columns.Length - 1, 1).ToString();
        }        

        /// <summary>
        /// exec using T-SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public bool Execute(string sql, object param = null, IDbTransaction transaction = null,
            int? commandTimeout = null, CommandType commandType = CommandType.Text)
        {
            using (IDbConnection conn = OpenConnection())
            {
                int count = conn.Execute(sql, param, transaction, commandTimeout, commandType);
                return count > 0;
            }
        }

        public bool ExecuteTran(string sql, object param = null, IDbTransaction transaction = null,
            int? commandTimeout = null, CommandType commandType = CommandType.Text)
        {
            using (IDbConnection conn = OpenConnection())
            {
                transaction = conn.BeginTransaction();
                int count = conn.Execute(sql, param, transaction, commandTimeout, commandType);
                transaction.Commit();
                return count > 0;
            }
        }

        /// <summary>
        /// exec using stored procedure
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public bool ExecuteForProc(string storedProcedureName, object param = null, IDbTransaction transaction = null,
            int? commandTimeout = null, CommandType commandType = CommandType.StoredProcedure)
        {
            using (IDbConnection conn = OpenConnection())
            {
                int count = conn.Execute(storedProcedureName, param, transaction, commandTimeout, commandType);
                return count > 0;
            }
        }

        /// <summary>
        /// query using T-SQL 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="buffered"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public IList<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null,
            bool buffered = true, int? commandTimeout = null, CommandType commandType = CommandType.Text)
        {
            using (IDbConnection conn = OpenConnection())
            {
                return conn.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType).ToList();
            }
        }

        /// <summary>
        /// query using stored procedure
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="storedProcedureName"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="buffered"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public IList<T> QueryForProc<T>(string storedProcedureName, object param = null, IDbTransaction transaction = null,
            bool buffered = true, int? commandTimeout = null, CommandType commandType = CommandType.StoredProcedure)
        {
            using (IDbConnection conn = OpenConnection())
            {
                return conn.Query<T>(storedProcedureName, param, transaction, buffered, commandTimeout, commandType).ToList();
            }
        }

        /// <summary>
        /// dynamic query using sql 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="buffered"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public dynamic Query(string sql, object param = null, IDbTransaction transaction = null,
            bool buffered = true, int? commandTimeout = null, CommandType commandType = CommandType.Text)
        {
            using (IDbConnection conn = OpenConnection())
            {
                return conn.Query<dynamic>(sql, param, transaction, buffered, commandTimeout, commandType).ToList();
            }
        }

        /// <summary>
        /// dynamic query using stored procedure
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="buffered"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public dynamic QueryForProc(string storedProcedureName, object param = null, IDbTransaction transaction = null,
           bool buffered = true, int? commandTimeout = null, CommandType commandType = CommandType.StoredProcedure)
        {
            using (IDbConnection conn = OpenConnection())
            {
                return conn.Query<dynamic>(storedProcedureName, param, transaction, buffered, commandTimeout, commandType).ToList();
            }
        }

        /// <summary>
        /// 批量插入功能
        /// </summary>
        public void InsertBatch<T>(IEnumerable<T> entityList, string tblName, IDbTransaction transaction = null) where T : class
        {
            tblName = tblName ?? string.Format("dbo.{0}", typeof(T).Name);
            var tran = (SqlTransaction)transaction;
            using (var bulkCopy = new SqlBulkCopy(OpenConnection() as SqlConnection, SqlBulkCopyOptions.TableLock, tran))
            {
                bulkCopy.BatchSize = entityList.Count();
                bulkCopy.DestinationTableName = tblName;
                var table = new DataTable();
                ISqlGenerator sqlGenerator = new SqlGeneratorImpl(new DapperExtensionsConfiguration());
                var classMap = sqlGenerator.Configuration.GetMap<T>();
                var props = classMap.Properties.Where(x => x.Ignored == false).ToArray();
                foreach (var propertyInfo in props)
                {
                    bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
                    table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyInfo.PropertyType) ?? propertyInfo.PropertyInfo.PropertyType);
                }
                var values = new object[props.Count()];
                foreach (var itemm in entityList)
                {
                    for (var i = 0; i < values.Length; i++)
                    {
                        values[i] = props[i].PropertyInfo.GetValue(itemm, null);
                    }
                    table.Rows.Add(values);
                }
                bulkCopy.WriteToServer(table);
            }
        }

        /// <summary>
        /// 批量更新数据(每批次5000)
        /// </summary>
        /// <param name="connString">数据库链接字符串</param>
        /// <param name="table"></param>
        public bool MultiUpdateData<T>(List<T> list, string tableName)
        {
            DataTable table = ToDataTable(list);
            table.TableName = tableName;
            using (SqlConnection conn = OpenConnection())
            {
                SqlCommand comm = conn.CreateCommand();
                comm.CommandTimeout = 60;
                comm.CommandType = CommandType.Text;
                comm.CommandText = $"select top 1 * from {tableName}";
                SqlDataAdapter adapter = new SqlDataAdapter(comm);
                SqlCommandBuilder commandBulider = new SqlCommandBuilder(adapter);
                commandBulider.ConflictOption = ConflictOption.OverwriteChanges;
                commandBulider.SetAllValues = true;
                foreach (DataRow dr in table.Rows)
                {
                    if (dr.RowState == DataRowState.Unchanged)
                        dr.SetModified();
                }

                try
                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    //设置批量更新的每次处理条数
                    adapter.UpdateBatchSize = 5000;
                    adapter.SelectCommand.Transaction = conn.BeginTransaction();/////////////////开始事务
                    if (table.ExtendedProperties["SQL"] != null)
                    {
                        adapter.SelectCommand.CommandText = table.ExtendedProperties["SQL"].ToString();
                    }


                    int nCount = adapter.Update(table);
                    adapter.SelectCommand.Transaction.Commit();/////提交事务
                    return nCount > 0;
                }
                catch (Exception ex)
                {
                    if (adapter.SelectCommand != null && adapter.SelectCommand.Transaction != null)
                    {
                        adapter.SelectCommand.Transaction.Rollback();
                    }
                    LogManager.Error("更新失败", ex);
                    return false;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

        /// <summary>
        /// List To DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="propertyName"></param>
        /// <returns></returns>
        public static DataTable ToDataTable<T>(IList<T> list, params string[] propertyName)
        {
            List<string> propertyNameList = new List<string>();
            if (propertyName != null)
            {
                propertyNameList.AddRange(propertyName);
            }
            DataTable result = new DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    if (propertyNameList.Count == 0)
                    {
                        Type colType = pi.PropertyType;
                        if (colType.IsGenericType && colType.GetGenericTypeDefinition() == typeof(Nullable<>))
                        {
                            colType = colType.GetGenericArguments()[0];
                        }
                        result.Columns.Add(pi.Name, colType);
                    }
                    else
                    {
                        if (propertyNameList.Contains(pi.Name))
                        {
                            result.Columns.Add(pi.Name, pi.PropertyType);
                        }
                    }
                }
                for (int i = 0; i < list.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        if (propertyNameList.Count == 0)
                        {
                            object obj = pi.GetValue(list[i], null);
                            tempList.Add(obj);
                        }
                        else
                        {
                            if (propertyNameList.Contains(pi.Name))
                            {
                                object obj = pi.GetValue(list[i], null);
                                tempList.Add(obj);
                            }
                        }
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }
            return result;
        }
    }

DapperOptions类

    public class DapperOptions
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
        public string ConnectionString { get; set; }
    }

上面只是简单的CRUD方法，大家可以根据这个思路，自己探索吧！！

标签

#c# #反射 #Dapper.NET #ORM #面向对象编程