C# 反射详解二

一些常用的ORM大家都应该知道,像微软的EF、国内的SqlSugar等......

其实他们的底层也都是基于ado.net，只不过在ado.net上加以封装。一般大公司都有自己的一套ORM,可以说这个东西咱不能不学,必须得造造轮子。😀

传统的ado.net查询数据,如下:根据id查询数据

public class Products
{
    public Guid Id { get; set; }
    public string ProductName { get; set; }
    public float ProductPrice { get; set; }
    public string Period { get; set; }
    public DateTime CreateDate { get; set; }
}

public static class config
{
    public const string SqlConnStr = "Data Source=DESKTOP-4TU9A6M;Initial Catalog=CoreFrame;User ID=sa;Password=123456";
}

public async Task<Products> FindProducts(Guid id)
{
    string sql = $@"SELECT [Id]
                   ,[ProductName]
                   ,[ProductPrice]
                   ,[Period]
                   ,[CreateDate]
               FROM [CoreFrame].[dbo].[Products]
               Where Id='{id}'";
    using (SqlConnection conn = new SqlConnection(config.SqlConnStr))
    {
        SqlCommand command = new SqlCommand(sql, conn);
        conn.Open();
        var reader = command.ExecuteReader();
        if (reader.Read())
        {
            Products products = new Products()
            {
                Id = (Guid)reader["Id"],
                ProductName = reader["ProductName"].ToString(),
                ProductPrice = (float)reader["ProductPrice"],
                Period = reader["Period"].ToString(),
                CreateDate = (DateTime)reader["CreateDate"]
            };
            return products;
        }
        else
        {
            return null;
        }
    }
}

可以加以封装,一个方法满足所有表的主键查询。

public async Task<T> Find<T>(Guid id)
{
    //不同的T代表不同的sql--反射拼装sql
    Type type = typeof(T);
    //将查询到的(数组列)每一列以逗号隔开拼成字符串
    string columnString = string.Join(",", type.GetProperties().Select(m => $"[{m.Name}]"));
    string sql = $@"SELECT {columnString} FROM [{type.Name}] Where Id='{id}'";
    using (SqlConnection conn = new SqlConnection(config.SqlConnStr))
    {
        SqlCommand command = new SqlCommand(sql, conn);
        conn.Open();
        var reader = command.ExecuteReader();
        if (reader.Read())
        {
            //创建对象
            T t = (T)Activator.CreateInstance(type);
            foreach (var item in type.GetProperties())
            {
                //给实体(t)的这个属性(item)设置为这个值reader[item.Name]
                //为nul就给null值,不为null就给查到的值
                item.SetValue(t, reader[item.Name] is DBNull ? null : reader[item.Name]);
            }
            return (T)t;
        }
        else
        {
            return default(T);
        }
    }
}

如果数据库表名称与后台对应的实体类名不一样就需要名称映射

[Table("Shops")]
public class Shops
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Remarks { get; set; }
    public DateTime Date { get; set; }
}

/// <summary>
/// 数据库映射特性
/// </summary>
[AttributeUsage(AttributeTargets.Class)]
public class TableAttribute : Attribute
{
    private string _Name = string.Empty;
    public TableAttribute(string name)
    {
        this._Name = name;
    }

    public string GetName()
    {
        return _Name;
    }
}

public static class DBAttributeExtend
{
    public static string GetMappingName(this Type type)
    {
        //是否有这个特性(TableAttribute)标识
        if (type.IsDefined(typeof(TableAttribute), true))
        {
            //用反射获取这个特性的实例对象
            TableAttribute attribute = (TableAttribute)type.GetCustomAttribute(typeof(TableAttribute), true);
            //调用特性中的方法
            return attribute.GetName();
        }
        else
            return type.Name;
    }
}

修改数据库查询字符串

//不同的T代表不同的sql--反射拼装sql
Type type = typeof(T);
//将查询到的(数组列)每一列以逗号隔开拼成字符串
string columnString = string.Join(",", type.GetProperties().Select(m => $"[{m.Name}]"));
//type.GetMappingName()=>得到特性上的参数
string sql = $@"SELECT {columnString} FROM [{type.GetMappingName()}] Where Id='{id}'";
using (SqlConnection conn = new SqlConnection(config.SqlConnStr))
{
//... ...
}
//... ...

属性也可以映射

/// <summary>
/// 实体类中的属性的映射
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public static string GetMappingName(this PropertyInfo prop)
{
    //是否有这个特性(ColumnAttribute)标识
    if (prop.IsDefined(typeof(ColumnAttribute), true))
    {
        //用反射获取这个特性的实例对象
        ColumnAttribute attribute = (ColumnAttribute)prop.GetCustomAttribute(typeof(ColumnAttribute), true);
        //调用特性中的方法
        return attribute.GetName();
    }
    else
        return prop.Name;
}

/// <summary>
/// 数据库映射特性
/// </summary>
[AttributeUsage(AttributeTargets.All)]
public class ColumnAttribute : Attribute
{
    private string _Name = string.Empty;
    public ColumnAttribute(string name)
    {
        this._Name = name;
    }

    public string GetName()
    {
        return _Name;
    }
}

[Table("Shops")]
public class Shops
{
    public Guid Id { get; set; }
    [Column("Name")]
    public string ShopName { get; set; }
    public string Remarks { get; set; }
    public DateTime Date { get; set; }
}

public async Task<T> Find<T>(Guid id)
{
    //不同的T代表不同的sql--反射拼装sql
    Type type = typeof(T);
    //将查询到的(数组列)每一列以逗号隔开拼成字符串
    string columnString = string.Join(",", type.GetProperties().Select(m => $"[{m.GetMappingName()}]"));
    //type.GetMappingName()=>得到特性上的参数
    string sql = $@"SELECT {columnString} FROM [{type.GetMappingName()}] Where Id='{id}'";
    using (SqlConnection conn = new SqlConnection(config.SqlConnStr))
    {
        SqlCommand command = new SqlCommand(sql, conn);
        conn.Open();
        var reader = command.ExecuteReader();
        if (reader.Read())
        {
            //创建对象
            T t = (T)Activator.CreateInstance(type);
            foreach (var item in type.GetProperties())
            {
                //给实体(t)的这个属性(item)设置为这个值reader[item.Name]
                //为nul就给null值,不为null就给查到的值
                item.SetValue(t, reader[item.GetMappingName()] is DBNull ? null : reader[item.GetMappingName()]);
            }
            return (T)t;
        }
        else
        {
            return default(T);
        }
    }
}

可以将GetMappingName方法整合为一个,Type和PropertyInfo都继承于MemberInfo,可以写个泛型方法再加以约束。

新建一个基类

/// <summary>
/// 数据库映射的特性基类
/// </summary>
public class ORMBaseAttribute : Attribute
{
    private string _Name = string.Empty;
    public ORMBaseAttribute(string name)
    {
        this._Name = name;
    }

    public virtual string GetName()
    {
        return _Name;
    }
}

修改TableAttribute和ColumnAttribute类

/// <summary>
/// 数据库字段映射特性
/// </summary>
[AttributeUsage(AttributeTargets.Property)]
public class ColumnAttribute : ORMBaseAttribute
{
    public ColumnAttribute(string name) : base(name)
    {
    }

}

/// <summary>
/// 数据库表映射特性
/// </summary>
[AttributeUsage(AttributeTargets.Class)]
public class TableAttribute : ORMBaseAttribute
{
    public TableAttribute(string name) : base(name)
    {

    }
}

/// <summary>
/// 数据库映射
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public static string GetMappingName<T>(this T type) where T : MemberInfo
{
    //是否有这个特性(ORMBaseAttribute)标识
    if (type.IsDefined(typeof(ORMBaseAttribute), true))
    {
        //用反射获取这个特性的实例对象
        ORMBaseAttribute attribute = (ORMBaseAttribute)type.GetCustomAttribute(typeof(ORMBaseAttribute), true);
        //调用特性中的方法
        return attribute.GetName();
    }
    else
        return type.Name;
}

添加数据

public class ShopType
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Remarks { get; set; }
    public DateTime Date { get; set; }
}

[AttributeUsage(AttributeTargets.Property)]
public class KeyAttribute : Attribute
{
    public KeyAttribute()
    {

    }
}

public static IEnumerable<PropertyInfo> GetPropertyWithoutKey(this Type type)
{
    //将类型传进来,过滤掉属性上有KeyAttribute的字段=>主键不能插入赋值
    return type.GetProperties().Where(m => !m.IsDefined(typeof(KeyAttribute), true));
}

/// <summary>
/// 数据插入
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public async Task<bool> Insert<T>(T t)
{

    Type type = typeof(T);
    //将查询到的(数组列)每一列以逗号隔开拼成字符串  主键是不能够赋值插入的所以会过滤掉主键这个列=>type.GetPropertyWithoutKey()
    string columnString = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"[{m.Name}]"));
    //获取值,拼接为字符串
    string valueString = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"'{m.GetValue(t)}'"));
    string sql = @$"INSERT INTO [{type.Name}] ({columnString}) Values({valueString})";
    using (SqlConnection conn = new SqlConnection(config.SqlConnStr))
    {
        SqlCommand command = new SqlCommand(sql, conn);
        conn.Open();
        int result = command.ExecuteNonQuery();
        return result == 1;
    }
}

防止sql注入,参数化拼装值。

public async Task<bool> Insert<T>(T t)
{

    Type type = typeof(T);
    //将查询到的(数组列)每一列以逗号隔开拼成字符串  主键是不能够赋值插入的所以会过滤掉主键这个列=>type.GetPropertyWithoutKey()  m.GetMappingName()=>获取映射的值
    string columnString = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"[{m.GetMappingName()}]"));
    //获取值,拼接为字符串
    string valueString = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"@{m.GetMappingName()}"));
    string sql = @$"INSERT INTO [{type.GetMappingName()}] ({columnString}) Values({valueString})";

    //转成参数列表  属性名称--值  Select=>Foreach
    IEnumerable<SqlParameter> parameters = type.GetPropertyWithoutKey().Select(m => new SqlParameter($"@{m.Name}", m.GetValue(t) ?? DBNull.Value));

    using (SqlConnection conn = new SqlConnection(config.SqlConnStr))
    {
        SqlCommand command = new SqlCommand(sql, conn);
        command.Parameters.AddRange(parameters.ToArray());
        conn.Open();
        int result = command.ExecuteNonQuery();
        return result == 1;
    }
}

性能调优:将拼接构造sql语句的过程,缓存下来。

泛型在运行时才会确定类型,JIT会为不同的T构造不同的类型副本,传不同的T都会产生一个全新的类,里面的字段也会重新初始化一份。

表字段新增、减少，程序会重新启动,然后会再加载缓存数据。

/// <summary>
/// sql生成+缓存
/// </summary>
/// <typeparam name="T"></typeparam>
public class SqlBuilder<T>
{
    private static string FindOneSql = string.Empty;
    private static string InsertSql = string.Empty;
    static SqlBuilder()
    {
        #region 添加
        Type type = typeof(T);
        //将查询到的(数组列)每一列以逗号隔开拼成字符串  主键是不能够赋值插入的所以会过滤掉主键这个列=>type.GetPropertyWithoutKey()
        string columnString = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"[{m.GetMappingName()}]"));
        //获取值,拼接为字符串
        string valueString = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"@{m.GetMappingName()}"));
        InsertSql = @$"INSERT INTO [{type.GetMappingName()}] ({columnString}) Values({valueString})";
        #endregion

        #region 查询
        //将查询到的(数组列)每一列以逗号隔开拼成字符串
        string columnStrings = string.Join(",", type.GetProperties().Select(m => $"[{m.GetMappingName()}]"));
        //type.GetMappingName()=>得到特性上的参数
        FindOneSql = $@"SELECT {columnStrings} FROM [{type.GetMappingName()}] Where Id=";
        #endregion
    }

    public static string GetSql(SqlType sqlType)
    {
        switch (sqlType)
        {
            case SqlType.FindOneSql:
                return FindOneSql;
            case SqlType.InsertSql:
                return InsertSql;
            default:
                throw new Exception("wrong SqlType");
        }
    }

    public enum SqlType
    {
        FindOneSql,
        InsertSql
    }
}

修改查询与新增的方法：

/// <summary>
/// 数据查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public async Task<T> Find<T>(Guid id)
{
    //不同的T代表不同的sql--反射拼装sql
    Type type = typeof(T);
    //将查询到的(数组列)每一列以逗号隔开拼成字符串
    //string columnString = string.Join(",", type.GetProperties().Select(m => $"[{m.GetMappingName()}]"));
    //type.GetMappingName()=>得到特性上的参数
    //string sql = $@"SELECT {columnString} FROM [{type.GetMappingName()}] Where Id='{id}'";

    string sql = $"{SqlBuilder<T>.GetSql(SqlBuilder<T>.SqlType.FindOneSql)}'{id}'";
    using (SqlConnection conn = new SqlConnection(config.SqlConnStr))
    {
        SqlCommand command = new SqlCommand(sql, conn);
        conn.Open();
        var reader = command.ExecuteReader();
        if (reader.Read())
        {
            //创建对象
            T t = (T)Activator.CreateInstance(type);
            foreach (var item in type.GetProperties())
            {
                //给实体(t)的这个属性(item)设置为这个值reader[item.Name]
                //为nul就给null值,不为null就给查到的值
                item.SetValue(t, reader[item.GetMappingName()] is DBNull ? null : reader[item.GetMappingName()]);
            }
            return (T)t;
        }
        else
        {
            return default(T);
        }
    }
}

/// <summary>
/// 数据插入
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public async Task<bool> Insert<T>(T t)
{

    Type type = typeof(T);
    //将查询到的(数组列)每一列以逗号隔开拼成字符串  主键是不能够赋值插入的所以会过滤掉主键这个列=>type.GetPropertyWithoutKey()
    //string columnString = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"[{m.GetMappingName()}]"));
    //获取值,拼接为字符串
    //string valueString = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"@{m.GetMappingName()}"));
    //string sql = @$"INSERT INTO [{type.GetMappingName()}] ({columnString}) Values({valueString})";

    string sql = SqlBuilder<T>.GetSql(SqlBuilder<T>.SqlType.InsertSql);
    //转成参数列表  属性名称--值  Select=>Foreach
    IEnumerable<SqlParameter> parameters = type.GetPropertyWithoutKey().Select(m => new SqlParameter($"@{m.Name}", m.GetValue(t) ?? DBNull.Value));

    using (SqlConnection conn = new SqlConnection(config.SqlConnStr))
    {
        SqlCommand command = new SqlCommand(sql, conn);
        command.Parameters.AddRange(parameters.ToArray());
        conn.Open();
        int result = command.ExecuteNonQuery();
        return result == 1;
    }
}

数据更新

/// <summary>
/// 数据修改
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public async Task<bool> Update<T>(T t) where T : BaseModel
{
    Type type = t.GetType();
    //type.GetPropertyWithoutKey()  过滤掉主键,主键不能更新,不然会报错
    //m.GetMappingName() 映射--解决数据库中名称与程序中名称不一致
    string updateStr = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"{m.GetMappingName()}='{m.GetValue(t)}'"));
    string sql = @$"update [{type.GetMappingName()}] set {updateStr} where id='{t.Id}'";
    using (SqlConnection conn = new SqlConnection(config.SqlConnStr))
    {
        SqlCommand command = new SqlCommand(sql, conn);
        conn.Open();
        int result = command.ExecuteNonQuery();
        return result == 1;
    }
}

问题:在要更新的数据前面加一个单引号.

可见,有sql注入的问题存在,对代码进行修改：

/// <summary>
/// 数据修改
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public async Task<bool> Update<T>(T t) where T : BaseModel
{
    Type type = t.GetType();
    //type.GetPropertyWithoutKey()  过滤掉主键,主键不能更新,不然会报错
    //m.GetMappingName() 映射--解决数据库中名称与程序中名称不一致
    string updateStr = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"{m.GetMappingName()}=@{m.GetMappingName()}"));
    //参数名称：m.GetMappingName()   值(null值需要换成DBNull.Value)：m.GetValue(t) ?? DBNull.Value
    var sqlParameterList = type.GetPropertyWithoutKey().Select(m => new SqlParameter(m.GetMappingName(), m.GetValue(t) ?? DBNull.Value)).ToArray();
    string sql = @$"update [{type.GetMappingName()}] set {updateStr} where id='{t.Id}'";
    using (SqlConnection conn = new SqlConnection(config.SqlConnStr))
    {
        SqlCommand command = new SqlCommand(sql, conn);
        //添加参数
        command.Parameters.AddRange(sqlParameterList);
        conn.Open();
        int result = command.ExecuteNonQuery();
        return result == 1;
    }
}

反射拼装sql影响性能，建缓存提升性能

/// <summary>
/// sql生成+缓存
/// </summary>
/// <typeparam name="T"></typeparam>
public class SqlBuilder<T>
{
    private static string FindOneSql = string.Empty;
    private static string InsertSql = string.Empty;
    private static string UpdateSql = string.Empty;
    static SqlBuilder()
    {
        #region 添加
        Type type = typeof(T);
        //将查询到的(数组列)每一列以逗号隔开拼成字符串  主键是不能够赋值插入的所以会过滤掉主键这个列=>type.GetPropertyWithoutKey()
        string columnString = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"[{m.GetMappingName()}]"));
        //获取值,拼接为字符串
        string valueString = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"@{m.GetMappingName()}"));
        InsertSql = @$"INSERT INTO [{type.GetMappingName()}] ({columnString}) Values({valueString})";
        #endregion

        #region 查询
        //将查询到的(数组列)每一列以逗号隔开拼成字符串
        string columnStrings = string.Join(",", type.GetProperties().Select(m => $"[{m.GetMappingName()}]"));
        //type.GetMappingName()=>得到特性上的参数
        FindOneSql = $@"SELECT {columnStrings} FROM [{type.GetMappingName()}] Where Id=";
        #endregion

        #region 修改
        //type.GetPropertyWithoutKey()  过滤掉主键,主键不能更新,不然会报错
        //m.GetMappingName() 映射--解决数据库中名称与程序中名称不一致
        string updateStr = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"{m.GetMappingName()}=@{m.GetMappingName()}"));
        UpdateSql = @$"update [{type.GetMappingName()}] set {updateStr} where id=";
        #endregion
    }

    public static string GetSql(SqlType sqlType)
    {
        switch (sqlType)
        {
            case SqlType.FindOneSql:
                return FindOneSql;
            case SqlType.InsertSql:
                return InsertSql;
            case SqlType.UpdateSql:
                return UpdateSql;
            default:
                throw new Exception("wrong SqlType");
        }
    }

    public enum SqlType
    {
        FindOneSql,
        InsertSql,
        UpdateSql
    }
}

修改代码：

/// <summary>
/// 数据修改
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public async Task<bool> Update<T>(T t) where T : BaseModel
{
    Type type = t.GetType();
    string sql = $"{SqlBuilder<T>.GetSql(SqlBuilder<T>.SqlType.UpdateSql)}'{t.Id}'";
    //参数名称：m.GetMappingName()   值：m.GetValue(t) ?? DBNull.Value
    var sqlParameterList = type.GetPropertyWithoutKey().Select(m => new SqlParameter(m.GetMappingName(), m.GetValue(t) ?? DBNull.Value)).ToArray();
    using (SqlConnection conn = new SqlConnection(config.SqlConnStr))
    {
        SqlCommand command = new SqlCommand(sql, conn);
        //添加参数
        command.Parameters.AddRange(sqlParameterList);
        conn.Open();
        int result = command.ExecuteNonQuery();
        return result == 1;
    }
}

数据删除

/// <summary>
/// sql生成+缓存
/// </summary>
/// <typeparam name="T"></typeparam>
public class SqlBuilder<T>
{
    private static string FindOneSql = string.Empty;
    private static string InsertSql = string.Empty;
    private static string UpdateSql = string.Empty;
    private static string DeleteSql = string.Empty;
    static SqlBuilder()
    {
        #region 添加
        Type type = typeof(T);
        //将查询到的(数组列)每一列以逗号隔开拼成字符串  主键是不能够赋值插入的所以会过滤掉主键这个列=>type.GetPropertyWithoutKey()
        string columnString = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"[{m.GetMappingName()}]"));
        //获取值,拼接为字符串
        string valueString = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"@{m.GetMappingName()}"));
        InsertSql = @$"INSERT INTO [{type.GetMappingName()}] ({columnString}) Values({valueString})";
        #endregion

        #region 查询
        //将查询到的(数组列)每一列以逗号隔开拼成字符串
        string columnStrings = string.Join(",", type.GetProperties().Select(m => $"[{m.GetMappingName()}]"));
        //type.GetMappingName()=>得到特性上的参数
        FindOneSql = $@"SELECT {columnStrings} FROM [{type.GetMappingName()}] Where Id=";
        #endregion

        #region 修改
        //type.GetPropertyWithoutKey()  过滤掉主键,主键不能更新,不然会报错
        //m.GetMappingName() 映射--解决数据库中名称与程序中名称不一致
        string updateStr = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"{m.GetMappingName()}=@{m.GetMappingName()}"));
        UpdateSql = @$"update [{type.GetMappingName()}] set {updateStr} where id=";
        #endregion

        #region 删除
        DeleteSql = $"delete from {type.GetMappingName()} where id=";
        #endregion
    }

    public static string GetSql(SqlType sqlType)
    {
        switch (sqlType)
        {
            case SqlType.FindOneSql:
                return FindOneSql;
            case SqlType.InsertSql:
                return InsertSql;
            case SqlType.UpdateSql:
                return UpdateSql;
            case SqlType.DeleteSql:
                return DeleteSql;
            default:
                throw new Exception("wrong SqlType");
        }
    }

    public enum SqlType
    {
        FindOneSql,
        InsertSql,
        UpdateSql,
        DeleteSql
    }
}

/// <summary>
/// 数据删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public async Task<bool> Delete<T>(Guid id) where T : BaseModel
{
    Type type = typeof(T);
    string sql = $"{SqlBuilder<T>.GetSql(SqlBuilder<T>.SqlType.DeleteSql)}'{id}'";
    using (SqlConnection conn = new SqlConnection(config.SqlConnStr))
    {
        SqlCommand command = new SqlCommand(sql, conn);
        //添加参数
        //command.Parameters.AddRange(sqlParameterList);
        conn.Open();
        int result = command.ExecuteNonQuery();
        return result == 1;
    }
}

批量删除

/// <summary>
/// 批量删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <returns></returns>
public async Task<bool> Delete<T>(IEnumerable<T> list) where T : BaseModel
{
    Type type = typeof(T);
    string Ids = string.Join(",", list.Select(m => $"'{m.Id}'"));
    //一条sql,本来就带事务性质(可以不用再写批处理语句)
    string sql = $"delete from [{type.GetMappingName()}] where id in ({Ids})";
    using (SqlConnection conn = new SqlConnection(config.SqlConnStr))
    {
        SqlTransaction trans = null;
        try
        {
            conn.Open();
            //开启事务
            trans = conn.BeginTransaction();
            SqlCommand command = new SqlCommand(sql, conn, trans);
            int iResult = command.ExecuteNonQuery();
            if (iResult == list.Count())
            {
                trans.Commit();
                return true;
            }
            else
                throw new Exception("删除的数据量不对");
        }
        catch (Exception ex)
        {
            if (trans != null)
                trans.Rollback();
            Console.WriteLine(ex.Message);
            throw ex;
        }
    }
}

将ado.net代码进行封装进而可以重用

/// <summary>
/// 为了代码复用,可以用委托封装
/// 不同的逻辑,用委托传递
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
private T ExecuteSql<T>(string sql, IEnumerable<SqlParameter> parameters, Func<SqlCommand, T> func)
{
    using (SqlConnection conn = new SqlConnection(config.SqlConnStr))
    {
        SqlCommand command = new SqlCommand(sql, conn);
        //添加参数
        command.Parameters.AddRange(parameters.ToArray());
        conn.Open();
        T t = func.Invoke(command);
        return t;
    }
}

更改增删改查的代码

/// <summary>
/// 数据查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public async Task<T> Find<T>(Guid id)
{
    //不同的T代表不同的sql--反射拼装sql
    Type type = typeof(T);
    //将查询到的(数组列)每一列以逗号隔开拼成字符串
    //string columnString = string.Join(",", type.GetProperties().Select(m => $"[{m.GetMappingName()}]"));
    //type.GetMappingName()=>得到特性上的参数
    //string sql = $@"SELECT {columnString} FROM [{type.GetMappingName()}] Where Id='{id}'";

    //string sql = $"{SqlBuilder<T>.GetSql(SqlBuilder<T>.SqlType.FindOneSql)}'{id}'";
    string sql = SqlBuilder<T>.GetSql(SqlBuilder<T>.SqlType.FindOneSql);
    IEnumerable<SqlParameter> parameters = new List<SqlParameter>()
    {
        new SqlParameter("@id",id)
    };
    T tResult = this.ExecuteSql<T>(sql, parameters, (command) =>
    {
        var reader = command.ExecuteReader();
        if (reader.Read())
        {
            //创建对象
            T t = (T)Activator.CreateInstance(type);
            foreach (var item in type.GetProperties())
            {
                //给实体(t)的这个属性(item)设置为这个值reader[item.Name]
                //为nul就给null值,不为null就给查到的值
                item.SetValue(t, reader[item.GetMappingName()] is DBNull ? null : reader[item.GetMappingName()]);
            }
            return t;
        }
        else
        {
            throw new Exception("主键查询,没有结果!");
        }
    });
    return tResult;
    //using (SqlConnection conn = new SqlConnection(config.SqlConnStr))
    //{
    //    SqlCommand command = new SqlCommand(sql, conn);
    //    conn.Open();
    //    var reader = command.ExecuteReader();
    //    if (reader.Read())
    //    {
    //        //创建对象
    //        T t = (T)Activator.CreateInstance(type);
    //        foreach (var item in type.GetProperties())
    //        {
    //            //给实体(t)的这个属性(item)设置为这个值reader[item.Name]
    //            //为nul就给null值,不为null就给查到的值
    //            item.SetValue(t, reader[item.GetMappingName()] is DBNull ? null : reader[item.GetMappingName()]);
    //        }
    //        return (T)t;
    //    }
    //    else
    //    {
    //        return default(T);
    //    }
    //}
}

/// <summary>
/// 数据插入
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public async Task<bool> Insert<T>(T t)
{

    Type type = typeof(T);
    //将查询到的(数组列)每一列以逗号隔开拼成字符串  主键是不能够赋值插入的所以会过滤掉主键这个列=>type.GetPropertyWithoutKey()
    //string columnString = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"[{m.GetMappingName()}]"));
    //获取值,拼接为字符串
    //string valueString = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"@{m.GetMappingName()}"));
    //string sql = @$"INSERT INTO [{type.GetMappingName()}] ({columnString}) Values({valueString})";

    string sql = SqlBuilder<T>.GetSql(SqlBuilder<T>.SqlType.InsertSql);
    //转成参数列表  属性名称--值  Select=>Foreach
    IEnumerable<SqlParameter> parameters = type.GetPropertyWithoutKey().Select(m => new SqlParameter($"@{m.Name}", m.GetValue(t) ?? DBNull.Value));

    //using (SqlConnection conn = new SqlConnection(config.SqlConnStr))
    //{
    //    SqlCommand command = new SqlCommand(sql, conn);
    //    command.Parameters.AddRange(parameters.ToArray());
    //    conn.Open();
    //    int result = command.ExecuteNonQuery();
    //    return result == 1;
    //}
    int iResult = this.ExecuteSql<int>(sql, parameters, (m) => m.ExecuteNonQuery());
    return iResult == 1;
}

/// <summary>
/// 数据修改
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public async Task<bool> Update<T>(T t) where T : BaseModel
{
    Type type = t.GetType();
    string sql = $"{SqlBuilder<T>.GetSql(SqlBuilder<T>.SqlType.UpdateSql)}'{t.Id}'";
    //参数名称：m.GetMappingName()   值：m.GetValue(t) ?? DBNull.Value
    var sqlParameterList = type.GetPropertyWithoutKey().Select(m => new SqlParameter(m.GetMappingName(), m.GetValue(t) ?? DBNull.Value)).ToArray();

    //using (SqlConnection conn = new SqlConnection(config.SqlConnStr))
    //{
    //    SqlCommand command = new SqlCommand(sql, conn);
    //    //添加参数
    //    command.Parameters.AddRange(sqlParameterList);
    //    conn.Open();
    //    int result = command.ExecuteNonQuery();
    //    return result == 1;
    //}

    int iResult = this.ExecuteSql<int>(sql, sqlParameterList, (m) => m.ExecuteNonQuery());
    return iResult == 1;
}


/// <summary>
/// 数据删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public async Task<bool> Delete<T>(Guid id) where T : BaseModel
{
    Type type = typeof(T);
    //string sql = $"{SqlBuilder<T>.GetSql(SqlBuilder<T>.SqlType.DeleteSql)}'{id}'";
    string sql = SqlBuilder<T>.GetSql(SqlBuilder<T>.SqlType.DeleteSql);
    //using (SqlConnection conn = new SqlConnection(config.SqlConnStr))
    //{
    //    SqlCommand command = new SqlCommand(sql, conn);
    //    //添加参数
    //    //command.Parameters.AddRange(sqlParameterList);
    //    conn.Open();
    //    int result = command.ExecuteNonQuery();
    //    return result == 1;
    //}
    IEnumerable<SqlParameter> parameters = new List<SqlParameter>()
    {
        new SqlParameter("@id",id)
    };
    int iResult = this.ExecuteSql<int>(sql, parameters, (m) => m.ExecuteNonQuery());
    return iResult == 1;
}


/// <summary>
/// 批量删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <returns></returns>
public async Task<bool> Delete<T>(IEnumerable<T> list) where T : BaseModel
{
    Type type = typeof(T);
    string Ids = string.Join(",", list.Select(m => $"'{m.Id}'"));
    //一条sql,本来就带事务性质(可以不用再写批处理语句)
    string sql = $"delete from [{type.GetMappingName()}] where id in ({Ids})";
    using (SqlConnection conn = new SqlConnection(config.SqlConnStr))
    {
        SqlTransaction trans = null;
        try
        {
            conn.Open();
            //开启事务
            trans = conn.BeginTransaction();
            SqlCommand command = new SqlCommand(sql, conn, trans);
            int iResult = command.ExecuteNonQuery();
            if (iResult == list.Count())
            {
                trans.Commit();
                return true;
            }
            else
                throw new Exception("删除的数据量不对");
        }
        catch (Exception ex)
        {
            if (trans != null)
                trans.Rollback();
            Console.WriteLine(ex.Message);
            throw ex;
        }
    }
}

/// <summary>
/// sql生成+缓存
/// </summary>
/// <typeparam name="T"></typeparam>
public class SqlBuilder<T>
{
    private static string FindOneSql = string.Empty;
    private static string InsertSql = string.Empty;
    private static string UpdateSql = string.Empty;
    private static string DeleteSql = string.Empty;
    static SqlBuilder()
    {
        #region 添加
        Type type = typeof(T);
        //将查询到的(数组列)每一列以逗号隔开拼成字符串  主键是不能够赋值插入的所以会过滤掉主键这个列=>type.GetPropertyWithoutKey()
        string columnString = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"[{m.GetMappingName()}]"));
        //获取值,拼接为字符串
        string valueString = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"@{m.GetMappingName()}"));
        InsertSql = @$"INSERT INTO [{type.GetMappingName()}] ({columnString}) Values({valueString})";
        #endregion

        #region 查询
        //将查询到的(数组列)每一列以逗号隔开拼成字符串
        string columnStrings = string.Join(",", type.GetProperties().Select(m => $"[{m.GetMappingName()}]"));
        //type.GetMappingName()=>得到特性上的参数
        FindOneSql = $@"SELECT {columnStrings} FROM [{type.GetMappingName()}] Where Id=@id";
        #endregion

        #region 修改
        //type.GetPropertyWithoutKey()  过滤掉主键,主键不能更新,不然会报错
        //m.GetMappingName() 映射--解决数据库中名称与程序中名称不一致
        string updateStr = string.Join(",", type.GetPropertyWithoutKey().Select(m => $"{m.GetMappingName()}=@{m.GetMappingName()}"));
        UpdateSql = @$"update [{type.GetMappingName()}] set {updateStr} where id=";
        #endregion

        #region 删除
        DeleteSql = $"delete from {type.GetMappingName()} where id=@id";
        #endregion
    }

    public static string GetSql(SqlType sqlType)
    {
        switch (sqlType)
        {
            case SqlType.FindOneSql:
                return FindOneSql;
            case SqlType.InsertSql:
                return InsertSql;
            case SqlType.UpdateSql:
                return UpdateSql;
            case SqlType.DeleteSql:
                return DeleteSql;
            default:
                throw new Exception("wrong SqlType");
        }
    }

    public enum SqlType
    {
        FindOneSql,
        InsertSql,
        UpdateSql,
        DeleteSql
    }
}

有时间的话还可以进一步的扩展封装😊

会用和知道原理完全是两码事儿,再接再厉！

本篇博客代码：

https://github.com/Davenever/ORMBegin

posted @ 2020-07-04 16:09 江北、阅读(357) 评论(0) 编辑收藏举报

刷新页面返回顶部

江北的博客、

C# 反射详解二

公告