记录EntityFramework增删改产生的SQL语句
项目里遇到一个数据交换的需求，申报端和审批端的系统和数据库都不在同一个网段；甲方提供一个msmq队列；我们把申报和审批产生的变化数据用sql记录到xml报文中，通过交换xml文件再解析出sql语句来实现数据同步。
我们用的数据库是sqlserver,orm框架是EntityFramework。这就需要在每次提交增删改数据的时候都记录下操作数据库的sql。起初想用DbCommandInterceptor来拦截每个DbCommand中的CommandText，但是测试下来有个问题没能解决。业务中的数据基本都是批量提交的，而DbCommandInterceptor每次拦截到的都是单个命令，这样我就不能识别哪些CommandText是在同一个提交中产生的，这样就没法保证批量提交、事务提交中的若干条sql在交换到另外一端的时候也是批量执行的。
另一种尝试。后来在上下文对象DbContext中找到了ChangeTracker，ChangeTracker里面的Entries是一个集合数据类型，每次Commit时有变化的数据都在这个Entries里面，遍历Entries时发现有State这个字段，State的值是一种枚举类型，Detached、Unchanged、Added、Deleted、Modified增删改都在里面了，于是有了办法。每次commit数据前，遍历context.ChangeTracker.Entries()，判断每个实体的变化状态按insert，delete，update不同分别生成sql。生成sql需要反射拿到每个实体的表名，字段名，还有数据类型，在ColumnAttribute类型里面都有。
DbUtils类的重要方法说明:
核心类：
public class DbUtils
{
/// <summary>
/// 生成context里做了增删改操作的sql
/// </summary>
public List<string> CreateEditSql(DbContext context)
{
if (context == null) throw new ArgumentNullException("参数context为null");
List<string> list = new List<string>();
var ents = context.ChangeTracker.Entries();
if (ents != null)
{
foreach (var ent in ents)
{
string sql = null;
if (ent.State == EntityState.Added)
{
Type type = ent.Entity.GetType();
sql = CreateInsertSql(type, ent.Entity);
}
else if (ent.State == EntityState.Modified)
{
//如果dbcontext的ProxyCreationEnabled属性为true,则这里取到的type可能是System.Data.Entity.DynamicProxies.xxx类型的，要再取BaseType才是对应的实体的类型
//Type type = ent.Entity.GetType().BaseType;
//关闭ProxyCreationEnabled后这里取到的type就是实体本身的类型
//关闭ProxyCreationEnabled可在构造中设置Configuration.ProxyCreationEnabled = false;
Type type = ent.Entity.GetType();
sql = CreateUpdateSql(type, ent.Entity);
}
else if (ent.State == EntityState.Deleted)
{
//如果dbcontext的ProxyCreationEnabled属性为true,则这里取到的type可能是System.Data.Entity.DynamicProxies.xxx类型的，要再取BaseType才是对应的实体的类型
//Type type = ent.Entity.GetType().BaseType;
//关闭ProxyCreationEnabled后这里取到的type就是实体本身的类型
//关闭ProxyCreationEnabled可在构造中设置Configuration.ProxyCreationEnabled = false;
Type type = ent.Entity.GetType();
sql = CreateDeleteSql(type, ent.Entity);
}
if (sql.IsNotNullOrEmpty()) list.Add(sql);
}
}
return list;
}
private string CreateInsertSql(Type type, object ent)
{
if (ent == null) return null;
string sql = null;
//判断正在操作的数据是不是从项目的基础类型继承的
if (typeof(BaseEntity).IsAssignableFrom(type))
{
List<AttrInfo> list = GetAttrInfos(ent);
var attrs = list.Where(l => l.AttrValue != null);
var kv = attrs.Select(a => new SqlKeyValue { FieldName = a.AttrName, FieldValue = GetSqlCodeValue(a.DbType, a.AttrValue) });
string names = kv.Select(k => k.FieldName).Aggregate((a, b) => $"{a},{b}");
string values = kv.Select(k => k.FieldValue).Aggregate((a, b) => $"{a},{b}");
sql = $"insert into {GetTableName(type)}({names}) values({values})";
}
return sql;
}
private string CreateUpdateSql(Type type, object ent)
{
if (ent == null) return null;
string sql = null;
//判断正在操作的数据是不是从项目的基础类型继承的
if (typeof(BaseEntity).IsAssignableFrom(type))
{
string guid = GetPk(ent);
if (guid.IsNotNullOrEmpty())
{
List<AttrInfo> list = GetAttrInfos(ent);
var attrs = list.Where(l => !l.AttrName.Equals("Guid"));
//var kv = attrs.Select(a => new SqlKeyValue { FieldName = a.AttrName, FieldValue = GetSqlCodeValue(a.DbType, a.AttrValue) }).Select(s => $"{s.FieldName}={s.FieldValue}").Aggregate((a, b) => $"{a},{b}");
//优化，只更新不是null的属性值
var kv = attrs.Where(a => a.AttrValue != null).Select(a => new SqlKeyValue { FieldName = a.AttrName, FieldValue = GetSqlCodeValue(a.DbType, a.AttrValue) }).Select(s => $"{s.FieldName}={s.FieldValue}").Aggregate((a, b) => $"{a},{b}");
if (kv.IsNotNullOrEmpty())
sql = $"update {GetTableName(type)} set {kv} where guid = '{guid}'";
}
}
return sql;
}
private string CreateDeleteSql(Type type, object ent)
{
if (ent == null) return null;
string sql = null;
//判断正在操作的数据是不是从项目的基础类型继承的
if (typeof(BaseEntity).IsAssignableFrom(type))
{
string guid = GetPk(ent);
if (guid.IsNotNullOrEmpty())
{
sql = $"delete from {GetTableName(type)} where guid = '{guid}'";
}
}
return sql;
}
//根据不同数据类型拼sql的value值
private string GetSqlCodeValue(DbType dt, object value)
{
string rtn = "";
switch (dt)
{
case DbType.AnsiString: rtn = value == null ? "null" : $"'{value.ToString().Replace("'", "''")}'"; break;
case DbType.DateTime:
rtn = value == null ? "null" : $"'{Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss:fff")}'"; break;
case DbType.Int16:
case DbType.Int32:
case DbType.Int64:
case DbType.Decimal:
case DbType.Double: rtn = value == null ? "null" : value.ToString(); break;
case DbType.Boolean: rtn = value == null ? "null" : Convert.ToBoolean(value) ? "1" : "0"; break;
}
return rtn;
}
/// <summary>
/// 取实体对应的表名
/// </summary>
private string GetTableName(Type t)
{
object[] objs = t.GetCustomAttributes(typeof(TableAttribute), false);
if (objs == null || objs.Count() <= 0)
{
throw new Exception($"{t.Name}未指定表名");
}
else
{
return ((TableAttribute)objs[0]).Name;
}
}
/// <summary>
/// 获取实体主键值
/// </summary>
private string GetPk(object obj)
{
if (obj == null) return null;
PropertyInfo prop = obj.GetType().GetProperty("Guid");
if (prop == null) throw new Exception($"{obj.GetType()}没有主键字段");
object o = prop.GetValue(obj, null);
if (o != null) return Convert.ToString(o);
else return null;
}
/// <summary>
/// 字段：名称与值的对应关系
/// </summary>
private List<AttrInfo> GetAttrInfos(object ent)
{
List<AttrInfo> list = new List<AttrInfo>();
if (ent != null)
{
DataType dtc = new DataType();
PropertyInfo[] props = ent.GetType().GetProperties();
if (props != null && props.Count() > 0)
{
//排除关联属性
props = props.Where(p => dtc.IsNormalBaseType(p.PropertyType.ToString())).ToArray();
list.AddRange(props.Select(p => new AttrInfo { AttrName = GetFieldName(p), AttrValue = p.GetValue(ent, null), DbType = dtc.PropertyTypeToDbType(p.PropertyType.ToString()) }));
}
}
return list;
}
/// <summary>
/// 取属性对应的字段名，如果有Column标记就取Column标记里的字段名，否则取属性名
/// </summary>
private string GetFieldName(PropertyInfo prop)
{
string name = string.Empty;
if (prop != null)
{
var attr = prop.GetCustomAttribute<ColumnAttribute>();
if (attr != null)
{
name = attr.Name;
}
else
{
name = prop.Name;
}
}
return name;
}
/// <summary>
/// 分面查询方法
/// </summary>
public IQueryable<T> GetPagedData<T>(IQueryable<T> orderQuery, int pageIndex, int pageSize)
{
int skip = (pageIndex - 1) * pageSize;
return orderQuery.Skip(skip).Take(pageSize);
}
}
辅助类：
internal class AttrInfo
{
/// <summary>
/// 数据类型
/// </summary>
public DbType DbType { get; set; }
/// <summary>
/// 实体属性名
/// </summary>
public string AttrName { get; set; }
/// <summary>
/// 实体属性值
/// </summary>
public object AttrValue { get; set; }
}
internal class SqlKeyValue
{
public string FieldName { get; set; }
public string FieldValue { get; set; }
}
转自公众号：
