记录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类的重要方法说明:

 

核心类：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197

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);       }   }

　　

辅助类：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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; }     }

　　

转自公众号：