• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    迪恩网络公众号
登陆 注册
描述文字

C# 反射详解二

原作者: [db:作者] 来自: [db:来源] 收藏 邀请

一些常用的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 查询

鲜花
握手
雷人
路过
鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
More+
上一篇：
Why Java Sucks and C# Rocks（1）：比较的意义与目的发布时间：2022-07-13
下一篇：
c#获取安装的软件和路径（注册表）发布时间：2022-07-13
热门推荐
More+
热门话题
More+
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服（服务时间 9:00～18:00）

在线QQ客服
地址：深圳市南山区西丽大学城创智工业园
电邮：jeky_zhao#qq.com
移动电话：139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap