数据库ORM框架原理和实现

数据查询

传统的ado.net查询表

public List<User> FindAllUsers() {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSQL"].ConnectionString))
            {
                string sql = "SELECT * FROM USERS";
                SqlCommand cmd = new SqlCommand(sql,conn);
                conn.Open();
                var reader = cmd.ExecuteReader();
                List<User> list = new List<User>();
                while (reader.Read()) {
                    User item = new User()
                    {
                        Id = (int)reader["Id"],
                        NickName = reader["NickName"].ToString()
                    };
                    list.Add(item);
                }
                return list;
            }
        }

这种写法，数据库有多少表就得写多少个这样的方法，接下来我们使用泛型+反射就可以一个方法支持所有表的查询。

泛型+反射动态生成Sql语句

public List<T> FindAll<T>() {
            var type = typeof(T);
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSQL"].ConnectionString))
            {
                string columnStrings = string.Join(",", string.Join(",", type.GetProperties().Select(x => $"[{x.Name}]")));
                string sql = $"SELECT {columnStrings} FROM [{type.Name}]";
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                var reader = cmd.ExecuteReader();
                List<T> list = new List<T>();
                while (reader.Read())
                {
                    T item = (T)Activator.CreateInstance(type);
                    foreach (var prop in type.GetProperties()) {
                        prop.SetValue(item, reader[prop.Name] is DBNull ? null : reader[prop.Name]);
                    }
                    list.Add(item);
                }
                return list;
            }
        }

实体名称与数据库表名称不对应，实体属性名称与数据库表字段名称不对应的问题

通常情况下，数据库表名一般用复数，而程序种实体名称一般用单数，那么直接用type.Name生成的SQL语句执行就会报错。目前主流的解决方法是使用特性Attribute标注。

//表名特性
public class DBTableAttribute : Attribute
{
    public string Name { get; set; }
    public DBTableAttribute(string name) {
        this.Name = name;
    }
}
//字段特性
public class DBColumnAttribute:Attribute
{
    public string Name { get; set; }
  public DBColumnAttribute(string name)
  {
    this.Name = name;
  }

    public class BaseModel
    {
        public int? Id { get; set; }
    }
[DBTable("Users")]
    public class User:BaseModel
    {
        public string NickName { get; set; }
        [DBColumn("Lv")]
        public int? Level { get; set; }
    }

var type = typeof(T);
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSQL"].ConnectionString))
{
     //GetColumnName方法，先检查是否有标记特性，如果有就返回特性名称，没有就返回属性名称
     string columnStrings = string.Join(",", string.Join(",", type.GetProperties().Select(x => $"[{x.GetColumnName()}]")));
     //GetTableName方法，先检查是否有标记特性，如果有就返回特性名称，没有就返回属性名称
     string tableName = type.GetTableName();
     string sql = $"SELECT {columnStrings} FROM [{tableName}]";
     SqlCommand cmd = new SqlCommand(sql, conn);
     conn.Open();
     var reader = cmd.ExecuteReader();
     List<T> list = new List<T>();
     while (reader.Read())
     {
           T item = (T)Activator.CreateInstance(type);
           foreach (var prop in type.GetProperties()) {
                 prop.SetValue(item, reader[prop.GetColumnName()] is DBNull ? null : reader[prop.GetColumnName()]);
           }
           list.Add(item);
      }
      return list;
}

//扩展方法 先检查是否有标记特性，如果有就返回特性名称，没有就返回属性名称
public static string GetColumnName(this PropertyInfo prop) {
if (prop.IsDefined(typeof(DBColumnAttribute), true))
 {
   DBColumnAttributeattribute = prop.GetCustomAttribute<DBColumnAttribute>();
     return attribute.Name;
 }
 else {
     return prop.Name;
 }
}
public static string GetTableName(this Type type) {
    if (type.IsDefined(typeof(DBTableAttribute), true))
    {
        DBTableAttribute attribute = type.GetCustomAttribute<DBTableAttribute>();
        return attribute.Name;
    }
    else {
        return type.Name;
    }
}

这样最后生成的SQL语句就是SELECT [Id],[NickName],[Lv] FROM [Users]，完全和数据库匹配了。

这些基本功能就实现了，我们可以优化一下代码，抽象特性类，扩展方法也可以合并一下，代码如下：

public abstract class AbstracDBAttribute:Attribute
    {
        public string Name { get; set; }
        public AbstracDBAttribute(string name)
        {
            this.Name = name;
        }
    }
    public class DBTableAttribute : AbstracDBAttribute {
        public DBTableAttribute(string name) : base(name){
        }
    }
    public class DBColumnAttribute : AbstracDBAttribute {
        public DBColumnAttribute(string name) : base(name) { 
        }
    }
//GetTableName和GetColunmName可以合并成一个方法了
public static string GetAttributeName(this MemberInfo member) {
            if (member.IsDefined(typeof(AbstracDBAttribute), true))
            {
                AbstracDBAttribute attribute = member.GetCustomAttribute<AbstracDBAttribute>();
                return attribute.Name;
            }
            else
            {
                return member.Name;
            }
        }

以上是最简单的数据库ORM原理，后面还可以增加查询条件，支持分页，支持Lambda拉姆达表达式查询等，接着往下看。

插入数据

还是利用泛型完成通用，反射动态生成SQL语句。遍历对象的属性，拼接SQL语句时，要注意主键自增列不要出现再SQL语句中，可以增加特性标记主键和自增列，遍历属性的时候进行过滤。这里如果属性值为Null，我们就不生成到Sql语句中。代码如下：

    public class BaseModel
    {
        [PrimaryKey]
        [Identity]
        public int? Id { get; set; }
    }

/// <summary>
    /// 主键
    /// </summary>
    public class PrimaryKeyAttribute : Attribute {
        
    }
    /// <summary>
    /// 标识列
    /// </summary>
    public class IdentityAttribute : Attribute {
        
    }

public int Insert<T>(T t)
{
    Type type = typeof(T);
    var props = type.GetProperties().FilterKey();
    List<string> cloumnList = new List<string>();
    List<string> valueList = new List<string>();
    List<SqlParameter> parameters = new List<SqlParameter>();
    foreach (var prop in props) {
        var val = prop.GetValue(t);
        if (val != null) {
             cloumnList.Add($"[{prop.GetAttributeName()}]");
             valueList.Add($"@{prop.GetAttributeName()}");
             parameters.Add(new SqlParameter($"@{prop.GetAttributeName()}", val));
        }
    }
    string sql = $"INSERT INTO [{type.GetAttributeName()}] ({string.Join(",", cloumnList.ToArray())}) VALUES ({string.Join(",", valueList.ToArray())})";
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSQL"].ConnectionString))
    {
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddRange(parameters.ToArray());
        conn.Open();
        return cmd.ExecuteNonQuery();
    }
}

/// <summary>
/// 筛选非自增列
/// </summary>
/// <param name="props"></param>
/// <returns></returns>
public static IEnumerable<PropertyInfo> FilterKey(this IEnumerable<PropertyInfo> props) {
    return props.Where(x => !x.IsDefined(typeof(IdentityAttribute), true));
}

注意，这里VLUES的内容不能直接拼接字符串，会有SQL注入问题，这里使用参数化的方法。PS：添加参数的时候要注意，SqlParameter的值不能是Null，如果一定要传入一个Null参数，可以转化为DBNull.Value。