C#.Net如何手写ORM
快速认识ORM
对象-关系映射,即Object/Relation Mapping,主要实现程序对象到关系数据库的映射。现在.Net比较流行的ORM框架有:EF、SqlSugar、Dapper、FreeSql、Nhibernate、IBatis.Net等。
O/RM只是一层代码的封装,底层还是基于ADO.NET完成对数据库的访问。
一般写法
如果我们要写一个查询,用ADO.NET就会如下这样写。
private static string ConnectionStringCustomers = "Data Source=.;Database=Customers;" + "User ID=sa;Password=123456;MultipleActiveResultSets=True"; public Company FindCompany(int id) { string sql = $@" SELECT [Id],[Name],[CreateTime],[CreatorId], [LastModifierId],[LastModifyTime] FROM [dbo].[Company] WHERE ID = {id}"; using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers)) { SqlCommand command = new SqlCommand(sql, conn); conn.Open(); var reader = command.ExecuteReader(); if (reader.Read()) { Company company = new Company() { Id = (int)reader["Id"], Name = reader["Name"].ToString() }; return company; } else { return null; } } }
public abstract class BaseModel { public int Id { set; get; } } public class Company : BaseModel { public string Name { get; set; } public DateTime CreateTime { get; set; } public int CreatorId { get; set; } public int? LastModifierId { get; set; } public DateTime? LastModifyTime { get; set; } }
但这样的写法是写死了的,我们能不能写一个通用查询,不管他是哪张表。
通用查询
既然要通用,那就不能写死类型,我们想到了使用泛型。泛型是任何类型都可以用,为了保证类型正确,我们再加泛型约束。
为了得到属性,我们要使用反射获取。
public T Find<T>(int id) where T : BaseModel // 泛型约束,必须继承自BaseModel { string colums = string.Join(",", typeof(T).GetProperties().Select(p => $"[{p.Name}]").ToArray()); string sql = $"SELECT {colums} FROM [{typeof(T).Name}] WHERE Id={id}"; using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers)) { SqlCommand command = new SqlCommand(sql, conn); conn.Open(); var reader = command.ExecuteReader(); if (reader.Read()) { // 反射实例化 T t = Activator.CreateInstance<T>(); foreach (var property in typeof(T).GetProperties()) { property.SetValue(t, reader[property.Name] is DBNull ? null : reader[property.Name]); } return t; } else { return null; } } return default(T); }
上述的方法,使用泛型和反射使我们的查询可以通用。
然后使用Company Company = sqlHelper.Find<Company>(1);
来调用得到实体。
但是,我们还有一个问题,如果我们的表名和实体类名称不一样,或字段名不一样,比如:表名为Sys_Company而实体名为Company,那我们该如何映射?
这里我们打算用C#的特性来解决这一问题。
首先,创建用来映射的特性类。
public class AbstractMappingAttribute : Attribute { public string MappingName = null; public AbstractMappingAttribute(string mappingName) { this.MappingName = mappingName; } }
映射表名。
[AttributeUsage(AttributeTargets.Class)] public class DBProxyTableAttribute: AbstractMappingAttribute { public DBProxyTableAttribute(string tableName) : base(tableName){} }
映射列名。
[AttributeUsage(AttributeTargets.Property)] public class DBProxyColumnAttribute : AbstractMappingAttribute { public DBProxyColumnAttribute(string columnName):base(columnName) {} }
在类名上添加特性。
[DBProxyTable("Sys_Company")] public class Company : BaseModel { [DBProxyColumn("Company_Name")] public string Name { get; set; } ...... }
获取实体类名或属性上的特性值来映射数据库的方法。
public static class DBProxyMappingExtension { public static string GetMappingName(this MemberInfo member) { string name = null; if (member.IsDefined(typeof(AbstractMappingAttribute), true)) { var attribute = member.GetCustomAttribute<AbstractMappingAttribute>(); name = attribute.MappingName; } else { name = member.Name; } return name; } }
最后,重新修改通用方法。
public T Find<T>(int id) where T : BaseModel // 泛型约束,必须继承自BaseModel { //string colums = string.Join(",", typeof(T).GetProperties().Select(p => $"[{p.Name}]").ToArray()); string colums = string.Join(",", typeof(T).GetProperties().Select(p => $"[{p.GetMappingName()}]").ToArray()); //string sql = $"SELECT {colums} FROM [{typeof(T).Name}] WHERE Id={id}"; string sql = $"SELECT {colums} FROM [{typeof(T).GetMappingName()}] WHERE Id={id}"; using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers)) { SqlCommand command = new SqlCommand(sql, conn); conn.Open(); var reader = command.ExecuteReader(); if (reader.Read()) { // 反射实例化 T t = Activator.CreateInstance<T>(); foreach (var property in typeof(T).GetProperties()) { //property.SetValue(t, reader[property.Name] is DBNull ? null : reader[property.Name]); property.SetValue(t, reader[property.GetMappingName()] is DBNull ? null : reader[property.GetMappingName()]); } return t; } else { return null; } } return default(T); }
通用插入
我们先写一个泛型缓存类:
public class SQLCacheBuilder<T> where T : BaseModel { private static string InsertSQL = ""; /// <summary> /// 静态构造函数,由CLR保障,在第一次使用该类之前,完成调用且只调用一次 /// </summary> static SQLCacheBuilder() { Console.WriteLine("SQLCacheBuilder 静态ctor。。。。。。。"); string columns = string.Join(",", typeof(T).GetPropertiesNoKey() .Select(p => $"[{p.GetMappingName()}]")); string values = string.Join(",", typeof(T).GetPropertiesNoKey() .Select(p => $"@{p.GetMappingName()}")); InsertSQL = $"INSERT INTO [{typeof(T).GetMappingName()}] " + $"({columns}) VALUES ({values})"; } public static string GetInsertSQL() { return InsertSQL; } }
当第一次调用SQLCacheBuilder方法对InsertSQL赋值,那么再次调用就会直接取缓存中的InsertSQL。但如果调用SQLCacheBuilder<T>
类,传来的泛型T不同,则缓存的InsertSQL是不同的。InsertSQL就是我们要执行的sql语句。
我们数据库表设置的id是自增的,为了在插入的SQL中过滤掉Id字段,我们打算用特性过滤。
[AttributeUsage(AttributeTargets.Property)] public class DBProxyKeyAttribute: Attribute { } public static class DBProxyFilterAttributeExtension { public static IEnumerable<PropertyInfo> GetPropertiesNoKey(this Type type) { return type.GetProperties() .Where(p => !p.IsDefined(typeof(DBProxyKeyAttribute), true)); } }
然后在实体属性id上加上此特性:
[DBProxyKey] public int Id { set; get; }
这样只要调用了GetPropertiesNoKey方法,遇见属性上加有DBProxyKey特性则会过滤掉。
最后,就是我们的通用的插入数据的方法:
// 写一个通用插入方法 public bool Insert<T>(T t) where T : BaseModel { // 调用SQLCacheBuilder方法获得拼接的sql string sql = SQLCacheBuilder<T>.GetInsertSQL(); // 为了防止拼接的有sql注入,使用参数parameters var parameters = typeof(T).GetPropertiesNoKey() .Select(p => new SqlParameter($"@{p.GetMappingName()}", p.GetValue(t) is null ? DBNull.Value : p.GetValue(t))); return this.ExecuteSQL<bool>(sql, parameters.ToArray(), command => { int iResult = command.ExecuteNonQuery(); return iResult == 1; }); } private T ExecuteSQL<T>(string sql, SqlParameter[] parameters, Func<SqlCommand, T> func) { using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers)) { SqlCommand command = new SqlCommand(sql, conn); command.Parameters.AddRange(parameters); conn.Open(); return func.Invoke(command); } }
通用删除
通过id删除
SQLCacheBuilder类中加入删除的泛型缓存。
public class SQLCacheBuilder<T> where T : BaseModel { private static string InsertSQL = ""; private static string UpdateSQL = ""; private static string DeleteSQL = ""; /// <summary> /// 静态构造函数,由CLR保障,在第一次使用该类之前,完成调用且只调用一次 /// </summary> static SQLCacheBuilder() { Console.WriteLine("SQLCacheBuilder 静态ctor。。。。。。。"); // 添加 string columns = string.Join(",", typeof(T).GetPropertiesNoKey() .Select(p => $"[{p.GetMappingName()}]")); string values = string.Join(",", typeof(T).GetPropertiesNoKey() .Select(p => $"@{p.GetMappingName()}")); InsertSQL = $"INSERT INTO [{typeof(T).GetMappingName()}] " + $"({columns}) VALUES ({values})"; // 更新 string colums = string.Join(",", typeof(T).GetPropertiesNoKey() .Select(p => $"[{p.GetMappingName()}]=@{p.Name}")); UpdateSQL = $"UPDATE [{typeof(T).GetMappingName()}] SET {colums}" + $" WHERE Id = @Id"; // 删除 DeleteSQL = $"DELETE FROM [{typeof(T).GetMappingName()}] WHERE Id=@Id"; } public static string GetInsertSQL() { return InsertSQL; } public static string GetUpdateSQL() { return UpdateSQL; } public static string GetDeleteSQL() { return DeleteSQL; } }
删除方法:
public bool Delete<T>(int id) where T : BaseModel { // 准备sql string sql = SQLCacheBuilder<T>.GetDeleteSQL(); SqlParameter[] parameters = new SqlParameter[] {new SqlParameter("@Id", id)}; // 执行ado return this.ExecuteSQL<bool>(sql, parameters.ToArray(), command => { int iResult = command.ExecuteNonQuery(); return iResult == 1; }); }
通过实体删除
public bool Delete<T>(T t) where T : BaseModel { return this.Delete<T>(t.Id); }
通过条件删除
条件删除写通用方法是有难度的,我们要使用到表达式目录树。
sqlHelper.Delete<Company>(x => x.Id > 5); sqlHelper.Delete<Company>(c => c.Id > 5 && c.CreatorId == 1 && c.Name.StartsWith("m") && c.Name.EndsWith("g"));
像这样的代码我们必须将条件一一解析出来,然后转换成sql写法。
于是,我们创建了CustomExpressionVisitor类,用来解析访问表达式目录树条件。
/// <summary> /// Expression二叉树---ExpressionVisitor提供了访问方式 /// </summary> public class CustomExpressionVisitor : ExpressionVisitor { private Stack<string> ConditionStack = new Stack<string>(); public string GetWhere() { string where = string.Concat(this.ConditionStack.ToArray()); this.ConditionStack.Clear(); return where; } public override Expression? Visit(Expression? node) { Console.WriteLine($"Visit入口:{node.NodeType} {node.Type} {node.ToString()}"); return base.Visit(node);//会做分发 } protected override Expression VisitBinary(BinaryExpression node) { Console.WriteLine($"VisitBinary:{node.NodeType} {node.Type} {node.ToString()}"); this.ConditionStack.Push(" ) "); base.Visit(node.Right);//5 this.ConditionStack.Push(node.NodeType.ToSqlOperator()); //翻译成> base.Visit(node.Left);//Age this.ConditionStack.Push(" ( "); return node; } protected override Expression VisitConstant(ConstantExpression node) { Console.WriteLine($"VisitConstant:{node.NodeType} {node.Type} {node.ToString()}"); //node.Value; this.ConditionStack.Push($"'{node.Value.ToString()}'"); return node; } protected override Expression VisitMember(MemberExpression node) { Console.WriteLine($"VisitMember:{node.NodeType} {node.Type} {node.ToString()}"); this.ConditionStack.Push($"{node.Member.Name}"); return node; } protected override Expression VisitMethodCall(MethodCallExpression m) { if (m == null) throw new ArgumentNullException("MethodCallExpression"); string format; switch (m.Method.Name) { case "StartsWith": format = "({0} LIKE {1}+'%')"; break; case "Contains": format = "({0} LIKE '%'+{1}+'%')"; break; case "EndsWith": format = "({0} LIKE '%'+{1})"; break; default: throw new NotSupportedException(m.NodeType + " is not supported!"); } this.Visit(m.Object); this.Visit(m.Arguments[0]); string right = this.ConditionStack.Pop(); string left = this.ConditionStack.Pop(); this.ConditionStack.Push(String.Format(format, left, right)); return m; } } internal static class SqlOperator { internal static string ToSqlOperator(this ExpressionType type) { switch (type) { case (ExpressionType.AndAlso): case (ExpressionType.And): return "AND"; case (ExpressionType.OrElse): case (ExpressionType.Or): return "OR"; case (ExpressionType.Not): return "NOT"; case (ExpressionType.NotEqual): return "<>"; case ExpressionType.GreaterThan: return ">"; case ExpressionType.GreaterThanOrEqual: return ">="; case ExpressionType.LessThan: return "<"; case ExpressionType.LessThanOrEqual: return "<="; case (ExpressionType.Equal): return "="; default: throw new Exception("不支持该方法"); } } }
条件删除方法:
public bool Delete<T>(Expression<Func<T,bool>> expression) where T : BaseModel { CustomExpressionVisitor visitor = new CustomExpressionVisitor(); visitor.Visit(expression); string where = visitor.GetWhere();//@"Id > 5"; //准备SQL string sql = $"DELETE FROM [{typeof(T).GetMappingName()}] WHERE {where}"; //执行ADO using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers)) { SqlCommand command = new SqlCommand(sql, conn); conn.Open(); int iResult = command.ExecuteNonQuery(); return iResult > 0; } }
本文来自博客园,作者:一纸年华,转载请注明原文链接:https://www.cnblogs.com/nullcodeworld/p/16620088.html
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)