基于反射的ORM简单封装

准备实体模型

public class Company
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime CreateTime { get; set; }
    public int CreatorId { get; set; }
    public int LastModifiedId { get; set; }
    public DateTime LastModifiedTime { get; set; }
}

自定义特性指定主键

[AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = false)]
public class KeyAttribute : Attribute
{
    public string Key { get; set; }
}

实体模型映射到数据库

public int EntityMapToTable<T>() where T : class
{
	// 通过ConfigurationManager获取连接字符串
    string conStr = ConfigurationManager.AppSettings["TestORM"];
    Type type = typeof(T);
    PropertyInfo[] properties = type.GetProperties();
    // 主键只能有一个
    PropertyInfo keyProperty = properties.FirstOrDefault(r => r.GetCustomAttributes(typeof(KeyAttribute), true)[0] != null);
    // 组装sql语句
    StringBuilder sb = new StringBuilder($"CREATE TABLE {type.Name}(");
    foreach (PropertyInfo prop in properties)
    {
        string propName = prop.Name;
        string propType = prop.PropertyType.Name;
        Type tempType = prop.PropertyType;
        // 这个函数其实就是映射，将C#类型映射为sql类型，需要自己建立映射关系
        SqlDbType sqlDbType = ConvertTypeToSqlDbType(tempType);
        var maxAttr = (prop.GetCustomAttribute(typeof(MaxLengthAttribute)) as MaxLengthAttribute);
        int length = maxAttr == null ? 100 : maxAttr.Length;

        if (propName == keyProperty.Name)
        {
            // todo 这里应该自定映射
            sb.Append($"{propName} {sqlDbType} PRIMARY KEY NOT NULL,");
        }
        else
        {
            // 这里也应该通过attr设置长度
            string len = tempType.ToString() == "System.String" ? $"({length})" : string.Empty;
            sb.Append($"{propName} {sqlDbType}{len} NOT NULL,");
        }
    }
    sb.Remove(sb.ToString().Length - 1, 1);
    sb.Append(")");

    using SqlConnection conneciton = new SqlConnection(conStr);
    using SqlCommand sqlCommand = new SqlCommand() { CommandText = sb.ToString(), Connection = conneciton };
    conneciton.Open();

    return sqlCommand.ExecuteNonQuery();
}

批量添加实体数据到数据库

public int MapEntities<T>(params T[] ts) where T : class
{
    Type type = typeof(T);
    PropertyInfo[] properties = type.GetProperties();
    StringBuilder sb = new StringBuilder($"INSERT INTO [{type.Name}] (");
    List<string> propArr = new List<string>();
    foreach (PropertyInfo prop in properties)
    {
        propArr.Add(prop.Name);
    }
    // 这里拼接字符串使用stringbuilder
    sb
        .Append(string.Join(',', propArr.ToArray()))
        .Append(")")
        .Append("VALUES");
    foreach (T t in ts)
    {
        List<object> valueArr = new List<object>();
        foreach (PropertyInfo prop in properties)
        {
            string partialSql = prop.PropertyType.Name == "Int32" ? $"{prop.GetValue(t)}" : $"'{prop.GetValue(t)}'";
            valueArr.Add(partialSql);
        }
        sb
          .Append("(")
          .Append(string.Join(',', valueArr.ToArray()))
          .Append("),");
    }
    string sql = sb.ToString()[0..^1];
    using SqlConnection conneciton = new SqlConnection(conStr);
    using SqlCommand sqlCommand = new SqlCommand() { CommandText = sql, Connection = conneciton };
    conneciton.Open();
    return sqlCommand.ExecuteNonQuery();
}

批量删除实体数据

public int DeleteById<T>(params int[] ids) where T : class
{
    if (ids == null || ids.Length <= 0)
    {
        throw new Exception("删除操作时主键为空!");
    }
    Type type = typeof(T);
    PropertyInfo[] properties = type.GetProperties();
    // 获得主键信息
    PropertyInfo keyProperty = properties.FirstOrDefault(r => r.GetCustomAttributes(typeof(KeyAttribute), true)[0] != null);
    string idsString = string.Join(',', ids);
    string realIds = idsString.Substring(0, idsString.Length);
    // 批量删除
    string sql = $"DELETE FROM {type.Name} WHERE {keyProperty.Name} in ({idsString})";

    using SqlConnection conneciton = new SqlConnection(conStr);
    using SqlCommand sqlCommand = new SqlCommand() { CommandText = sql, Connection = conneciton };
    conneciton.Open();
    return sqlCommand.ExecuteNonQuery();
}

批量更新实体数据

public int UpdateEntity<T>(T t) where T : class
{
    Type type = typeof(T);
    PropertyInfo[] properties = type.GetProperties();
    PropertyInfo keyProperty = properties.FirstOrDefault(r =>r.GetCustomAttributes(typeof(KeyAttribute), true)[0] != null);
    
    // 这里keyValue在实例中默认为0，排除这种情况
    int keyValue = Convert.ToInt32(type.GetProperty(keyProperty.Name).GetValue(t));
    if (keyValue == 0)
    {
        throw new Exception("Key is not provided");
    }
    StringBuilder sb = new StringBuilder($"UPDATE [{type.Name}] SET ");

    List<string> updateColumns = new List<string>();
    foreach (PropertyInfo property in properties)
    {
        object propValue = property.GetValue(t);
        // 有这个属性对应的属性值
        if (propValue != null)
        {
            object realPropValue = property.PropertyType.Name == "Int32" ? $"{propValue}" : $"'{propValue}'";
            string tempSql = $"{property.Name} = {realPropValue}";
            updateColumns.Add(tempSql);
        }
    }
    sb
        .Append(string.Join(',', updateColumns.ToArray()))
        .Append($" WHERE {keyProperty.Name} = {keyValue}");

    using SqlConnection conneciton = new SqlConnection(conStr);
    using SqlCommand sqlCommand = new SqlCommand() { CommandText = sb.ToString(), Connection = conneciton };
    conneciton.Open();
    return sqlCommand.ExecuteNonQuery();
}

数据库映射为实体

public List<T> QueryEntities<T>(int? id = null) where T : class
{
    Type type = typeof(T);
    PropertyInfo[] properties = type.GetProperties();
    IEnumerable<string> propNameList = from p in properties select p.Name;
    string selectColumns = string.Join(", ", propNameList);
    StringBuilder sb = new StringBuilder($@"SELECT {selectColumns} FROM [{type.Name}] ");
    // 通过id查询时
    if (id != null)
    {
        PropertyInfo keyProperty = properties.FirstOrDefault(r => r.GetCustomAttributes(typeof(KeyAttribute), true)[0] != null);
        sb.Append($@" WHERE {keyProperty.Name} = {id}");
    }
    List<T> entityList = new List<T>();
    using SqlConnection conneciton = new SqlConnection(conStr);
    using SqlCommand sqlCommand = new SqlCommand() { CommandText = sb.ToString(), Connection = conneciton };
    conneciton.Open();
    SqlDataReader reader = sqlCommand.ExecuteReader();
    do
    {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                PropertyInfo[] props = properties;
                T t = Activator.CreateInstance(type) as T;
                foreach (PropertyInfo prop in props)
                {
                    string propName = prop.Name;
                    if (prop.CanWrite)
                    {
                    	// 设置实体模型的值
                        prop.SetValue(t, reader[propName]);
                    }
                }
                entityList.Add(t);
            }
        }
    } while (reader.NextResult());

    return entityList;
}