利用反射、泛型、特性手写ORM

 

1.基本准备工作，三层基本框架。

2.ORM Find 和 FindAll 方法

  public T Find<T>(int id)
        {
            Type tp = typeof(T);
            string columnStrings = string.Join(",", tp.GetProperties().Select(p => $"{p.Name}"));
            string sql = $@"select {columnStrings}
                            from[{tp.Name}]
                            where ID = {id}";
            using(SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(sql, conn);
                conn.Open();
                var reader = command.ExecuteReader();
                if(reader.Read())
                {
                    T t = (T)Activator.CreateInstance(tp);
                    foreach (var prop in tp.GetProperties())
                    {
                        prop.SetValue(t, reader[prop.Name]??null);
                    }
                    return t;
                }
                else
                {
                    return default(T);
                }
            }
        }

 public List<T> FindAll<T>()
        {
            Type tp = typeof(T);
            string columnStrings = string.Join(",", tp.GetProperties().Select(p => $"{p.Name}"));
            string sql = $@"select {columnStrings}
                            from[{tp.Name}]";
            List<T> list = new List<T>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(sql, conn);
                conn.Open();
                var reader = command.ExecuteReader();
                while (reader.Read())
                {
                    T t = (T)Activator.CreateInstance(tp);
                    foreach (var prop in tp.GetProperties())
                    {
                        prop.SetValue(t, reader[prop.Name] ?? null);
                    }
                    list.Add(t);
                }
            }
            return list;

        }

一般情况下，基本的查询操作已经支持了，但实际项目中难免会遇到实体类名和实体字段名跟数据库的表明及字段名对不上的情况，这个时候使用上面的方法就会报错，那么怎么解决呢，这里采用特性的方式来解决。

首先自定义一个特性类MyAttribute用来获取表名

 public class MyAttribute:Attribute
    {
        private string _RealName = null;
        public  MyAttribute(string name)
        {
            this._RealName = name;
        }
        public string GetRealName()
        {
            return _RealName;
        }
    }

然后再自定义一个特性类MyColumnAttribute用来获取列名

 private string _ColumnName = null;
        public MyColumnAttribute(string name)
        {
            this._ColumnName = name;
        }
        public string GetColumnName()
        {
            return _ColumnName;
        }

最后写一个特性映射扩展方法来获取表名和列名AttributeMappingExtend

 public static string GetTableName(this Type type)
        {
            if (type.IsDefined(typeof(MyAttribute), true))
            {
                MyAttribute myAttribute = type.GetCustomAttribute<MyAttribute>();
                return myAttribute.GetRealName();
            }
            else
            {
                return type.Name;
            }
        }
        public static string GetColumnName(this PropertyInfo prop)
        {
            if (prop.IsDefined(typeof(MyColumnAttribute), true))
            {
                MyColumnAttribute myAttribute = prop.GetCustomAttribute<MyColumnAttribute>();
                return myAttribute.GetColumnName();
            }
            else
            {
                return prop.Name;
            }
        }

此时Find和FindAll的方法里需要一点点小改动，

 public T Find<T>(int id)
        {
            Type tp = typeof(T);
            string columnStrings = string.Join(",", tp.GetProperties().Select(p => $"{p.GetColumnName()}"));
            string sql = $@"select {columnStrings}
                            from[{tp.GetTableName()}]
                            where ID = {id}";
            using(SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(sql, conn);
                conn.Open();
                var reader = command.ExecuteReader();
                if(reader.Read())
                {
                    T t = (T)Activator.CreateInstance(tp);
                    foreach (var prop in tp.GetProperties())
                    {
                        string propName = prop.GetColumnName();
                        prop.SetValue(t, reader[propName] ??null);
                    }
                    return t;
                }
                else
                {
                    return default(T);
                }
            }
        }

FindAll方法改动同上。

此时已经基本完成我们所要的功能。但是在获取表名和列名时我们可以看到有许多重复代码，可以进一步优化一下。

首先看MyAttribute和MyColumnAttribute，两个特性类几乎一样，所以定义一个抽象类AbstractMappingAttribute作为父类，然后由两个特性类来继承，之所以定义为抽象类，是防止其被实例化。

 public abstract class AbstractMappingAttribute:Attribute
    {
        private string _RealName = null;
        public AbstractMappingAttribute(string name)
        {
            this._RealName = name;
        }
        public string GetRealName()
        {
            return _RealName;
        }
    }

修改两个特性类继承父类

public class MyAttribute:AbstractMappingAttribute
    {
        public MyAttribute(string name):base(name)
        {

        }
    }

public  class MyColumnAttribute:AbstractMappingAttribute
    {
        public MyColumnAttribute(string name):base(name)
        {

        }
    }

同样特性映射扩展方法也有很多重复的地方

F12我们可以看到Type和PropertyInfo都继承MemberInfo,优化代码

 public static string GetMappingName(this MemberInfo memberInfo)
        {
            if (memberInfo.IsDefined(typeof(AbstractMappingAttribute), true))
            {
                AbstractMappingAttribute myAttribute = memberInfo.GetCustomAttribute<AbstractMappingAttribute>();
                return myAttribute.GetRealName();
            }
            else
            {
                return memberInfo.Name;
            }
        }

3.Insert插入方法。

   public bool Insert<T>(T t)
        {
            Type type = typeof(T);
            string columnString = string.Join(",", type.GetProperties().Select(p => $"[{p.GetMappingName()}]"));
            string valueString = string.Join(",", type.GetProperties().Select(p => $"'{p.GetValue(t)}'"));
            string sql = $@"insert into [dbo].[{type.GetMappingName()}]
                                        ({columnString})
                                        values
                                         ({valueString})
                                        ";
            using(SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(sql,conn);
                conn.Open();
                int rlt = command.ExecuteNonQuery();
                return rlt > 0;
            }
        }

有前面的基础，一个简单的插入就写出来了，但这个方法只适合有主键插入值得，对于一些自增的表就会报错了，那么如何去掉ID，这里还是用特性。

新建一个空的特性KeyAttribute

  public class KeyAttribute:Attribute
    {

    }

将实体中为主键的字段加上特性标识。

[MyAttribute("User")]
    public class UserModel
    {
        [KeyAttribute]
        public int ID { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }

下面在特性映射扩展类中添加一个过滤主键的方法，当然这个功能也可以直接写在columnString，valueString，但是为了看起来整洁美观，所以另写了一个方法：

  /// <summary>
        /// 排除特性中标记的主键
        /// </summary>
        /// <param name="propertyInfos"></param>
        /// <returns></returns>
        public static IEnumerable<PropertyInfo> FilterKey(this IEnumerable<PropertyInfo> propertyInfos)
        {
            return propertyInfos.Where(p => !p.IsDefined(typeof(KeyAttribute), true));
        }

然后修改Insert方法

 public bool Insert<T>(T t)
        {
            Type type = typeof(T);
            string columnString = string.Join(",", type.GetProperties().FilterKey().Select(p => $"[{p.GetMappingName()}]"));
            string valueString = string.Join(",", type.GetProperties().FilterKey().Select(p => $"'{p.GetValue(t)}'"));
            string sql = $@"insert into [dbo].[{type.GetMappingName()}]
                                        ({columnString})
                                        values
                                         ({valueString})
                                        ";
            using(SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(sql,conn);
                conn.Open();
                int rlt = command.ExecuteNonQuery();
                return rlt > 0;
            }
        }

这样就可以过滤掉主键，插入数据库了。

但这里还有一个bug，那就是sql注入。

在

string valueString = string.Join(",", type.GetProperties().FilterKey().Select(p => $"'{p.GetValue(t)}'"));

这里，为了保证value值的插入，我们加入了‘’，但是如果数据的值中包含有‘，就会生成错误的sql，程序也就执行不过去了。

另外如果别人摸清了你的sql组成特点，比如下面的语句

INSERT INTO kf_Test.dbo.BAS_HECM_BUILDING1
        ( BUILDNAME ,
          COMMUNITYID ,
          DISTANCE
        )
VALUES  ( '测试', 'dd' , '' );DELETE kf_Test.dbo.BAS_HECM_BUILDING1; --, 'dd' , '' );

'测试', 'dd' , '' );DELETE kf_Test.dbo.BAS_HECM_BUILDING1; --这些占一个字段的值，后面真实的值被注释掉，替换为 别人的sql，严重的delete就删除整个表。

为了防止sql注入，需要继续优化代码

采用parameters传值的方法：

public bool Insert<T>(T t)
        {
            Type type = typeof(T);
            string columnString = string.Join(",", type.GetProperties().FilterKey().Select(p => $"[{p.GetMappingName()}]"));
            string valueString = string.Join(",", type.GetProperties().FilterKey().Select(p => $"@{p.GetMappingName()}"));
            string sql = $@"insert into [dbo].[{type.GetMappingName()}]
                                        ({columnString})
                                        values
                                         ({valueString})
                                        ";
            var parameters = type.GetProperties().FilterKey().Select(p => new SqlParameter($"@{p.GetMappingName()}", p.GetValue(t)??DBNull.Value));
            using(SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(sql,conn);
                command.Parameters.AddRange(parameters.ToArray());
                conn.Open();
                int rlt = command.ExecuteNonQuery();
                return rlt > 0;
            }
        }

4.添加缓存

在上面查询和插入的sql语句生成中我们每次都要重新生成，如果对于同一个实体类同一个操作（查询、插入等），所生成的sql是一样的，这里建立一个缓存，提高效率。

private static string InsertSql = null;
        private static string FindSql = null;
        static SqlBuilder()
        {
            {
                Type type = typeof(T);
                string columnStrings = string.Join(",", type.GetProperties().Select(p => $"{p.GetMappingName()}"));
                string sql = $@"select {columnStrings}
                            from[{type.GetMappingName()}]
                            where ID = ";
            }

            {
                Type type = typeof(T);
                string columnString = string.Join(",", type.GetProperties().FilterKey().Select(p => $"[{p.GetMappingName()}]"));
                //string valueString = string.Join(",", type.GetProperties().FilterKey().Select(p => $"'{p.GetValue(t)}'"));
                string valueString = string.Join(",", type.GetProperties().FilterKey().Select(p => $"@{p.GetMappingName()}"));
                InsertSql = $@"insert into [dbo].[{type.GetMappingName()}]
                                        ({columnString})
                                        values
                                         ({valueString})
                                        ";
            }
        }
        public static string GetInsertSql()
        {
            return InsertSql;
        }
        public static string GetFindSql()
        {
            return FindSql;
        }

采用缓存之后，相应的查询，插入方法中的sql就可以进行优化了。

5.Update

 public bool Update<T>(T t)
        {
            Type type = typeof(T);
            string columnString = string.Join(",", type.GetProperties().FilterKey().Select(p => $"{p.GetMappingName()}=@{p.GetMappingName()}"));

            string sql = $@"update {type.GetMappingName()} 
                                set 
                                    {columnString} 
                                where {type.GetProperties().FindKey()} = @{type.GetProperties().FindKey()}";
            var parameters = type.GetProperties().Select(p => new SqlParameter($"@{p.GetMappingName()}", p.GetValue(t) ?? DBNull.Value));
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand sqlCommand = new SqlCommand(sql, conn);
                sqlCommand.Parameters.AddRange(parameters.ToArray());
                conn.Open();
                int rlt = sqlCommand.ExecuteNonQuery();
                return rlt > 0;
            }
        }

 

 public static string FindKey(this IEnumerable<PropertyInfo> propertyInfos)
        {
            return propertyInfos.Where(p => p.IsDefined(typeof(KeyAttribute), true)).Select(p => $"{ p.GetMappingName()}").FirstOrDefault();
        }

6.Delete

 public bool Delete<T>(T t)
        {
            Type type = typeof(T);
            string sql = $@"delete from 
                                        {type.GetMappingName()} 
                                   where 
                                        {type.GetProperties().FindKey()}=@{type.GetProperties().FindKey()}";
            var parameters = type.GetProperties().Select(p => new SqlParameter($"@{p.GetMappingName()}", p.GetValue(t) ?? DBNull.Value));
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(parameters.ToArray());
                conn.Open();
                int rlt = command.ExecuteNonQuery();
                return rlt > 0;
            }
        }

批量删除

 public bool Delete<T>(IEnumerable<T> list)
        {
            Type type = typeof(T);
            string idList = string.Join(",", list.Select(p => type.GetProperties().FindValue(p)));
               IEnumerable<SqlParameter> parameters = new List<SqlParameter>()
            {
                new SqlParameter($"@{type.GetProperties().FindKey()}",type.GetProperties().FindValue(t))
            };
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(sql, conn);
                conn.Open();
                int rlt = command.ExecuteNonQuery();
                return rlt > 0;
            }
        }

  /// <summary>
        /// 返回主键值
        /// </summary>
        /// <param name="propertyInfos"></param>
        /// <returns></returns>
        public static string FindValue<T>(this IEnumerable<PropertyInfo> propertyInfos ,T t)
        {
            return propertyInfos.Where(p => p.IsDefined(typeof(KeyAttribute), true)).Select(p => $"{ p.GetValue(t)}").FirstOrDefault();
        }

7用委托优化增删改查

在上面四个方法中每个的执行都要执行一次数据库连接，这部分代码基本类似，这里改进一下

 private T ExecuteSql<T>(string sql, IEnumerable<SqlParameter> parameters, Func<SqlCommand, T> func)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(parameters.ToArray());
                conn.Open();
                T t = func.Invoke(command);
                return t;
            }
        }

这里使用了委托。

修改四个方法

查询：

 public T Find<T>(int id)
        {
            Type tp = typeof(T);
            string columnStrings = string.Join(",", tp.GetProperties().Select(p => $"{p.GetMappingName()}"));
            string sql = $@"select {columnStrings}
                            from[{tp.GetMappingName()}]
                            where {tp.GetProperties().FindKey()} = @{tp.GetProperties().FindKey()}";
            IEnumerable<SqlParameter> parameters = new List<SqlParameter>()
            {
                new SqlParameter($"@{tp.GetProperties().FindKey()}",id)
            };
            T rlt = ExecuteSql(sql, parameters, command => {
                var reader = command.ExecuteReader();
                if (reader.Read())
                {
                    T t = (T)Activator.CreateInstance(tp);
                    foreach (var prop in tp.GetProperties())
                    {
                        string propName = prop.GetMappingName();
                        prop.SetValue(t, reader[propName] ?? null);
                    }
                    return t;
                }
                else
                {
                    return default(T);
                }
            });
            return rlt;
        }

插入：

public bool Insert<T>(T t)
        {
            Type type = typeof(T);
            string columnString = string.Join(",", type.GetProperties().FilterKey().Select(p => $"[{p.GetMappingName()}]"));
            //string valueString = string.Join(",", type.GetProperties().FilterKey().Select(p => $"'{p.GetValue(t)}'"));
            string valueString = string.Join(",", type.GetProperties().FilterKey().Select(p => $"@{p.GetMappingName()}"));
            string sql = $@"insert into [dbo].[{type.GetMappingName()}]
                                        ({columnString})
                                        values
                                         ({valueString})
                                        ";
            var parameters = type.GetProperties().FilterKey().Select(p => new SqlParameter($"@{p.GetMappingName()}", p.GetValue(t) ?? DBNull.Value));
            var rlt = ExecuteSql(sql,parameters,command=>command.ExecuteNonQuery());
            return rlt > 0;
          
        }

更新

 public bool Update<T>(T t)
        {
            Type type = typeof(T);
            string columnString = string.Join(",", type.GetProperties().FilterKey().Select(p => $"{p.GetMappingName()}=@{p.GetMappingName()}"));
            string sql = $@"update {type.GetMappingName()} 
                                set 
                                    {columnString} 
                                where {type.GetProperties().FindKey()} = @{type.GetProperties().FindKey()}";
            var parameters = type.GetProperties().Select(p => new SqlParameter($"@{p.GetMappingName()}", p.GetValue(t) ?? DBNull.Value));
            int rlt = ExecuteSql(sql, parameters, command => command.ExecuteNonQuery());
            return rlt > 0;
            //using (SqlConnection conn = new SqlConnection(connectionString))
            //{
            //    SqlCommand sqlCommand = new SqlCommand(sql, conn);
            //    sqlCommand.Parameters.AddRange(parameters.ToArray());
            //    conn.Open();
            //    int rlt = sqlCommand.ExecuteNonQuery();
            //    return rlt > 0;
            //}
        }

删除

  public bool Delete<T>(T t)
        {
            Type type = typeof(T);
            string sql = $@"delete from 
                                        {type.GetMappingName()} 
                                   where 
                                        {type.GetProperties().FindKey()}=@{type.GetProperties().FindKey()}";
            IEnumerable<SqlParameter> parameters = new List<SqlParameter>()
            {
                new SqlParameter($"@{type.GetProperties().FindKey()}",type.GetProperties().FindValue(t))
            };
            int rlt = ExecuteSql(sql, parameters, command => command.ExecuteNonQuery());
            return rlt > 0;
           
        }