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;
}
}
posted @   一纸年华  阅读(1904)  评论(3编辑  收藏  举报
相关博文:
阅读排行:
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)
点击右上角即可分享
微信分享提示

目录导航

快速认识ORM
一般写法
通用查询
通用插入
通用删除
通过id删除
通过实体删除
通过条件删除