1.定义EF拦截器,截获执行命令前的操作。修改执行sql。还需要定义orcal序列,供自增使用
using System; using System.Collections.Generic; using System.Data.Common; using System.Data.Entity.Infrastructure.Interception; using System.Linq; using System.Text; using System.Text.RegularExpressions; using System.Threading.Tasks; /* * CREATE SEQUENCE "SQ_IHR_ID" INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE; */ namespace TechFancier.Data { public class NoLockInterceptor : DbCommandInterceptor { static bool isDebug = false; static NoLockInterceptor() { isDebug = System.Configuration.ConfigurationManager.AppSettings["DBDebug"] == "true"; } private static readonly Regex _tableAliasRegex = new Regex(@"(?<tableAlias>AS \[Extent\d+\](?! WITH \(NOLOCK\)))", RegexOptions.Multiline | RegexOptions.IgnoreCase); [ThreadStatic] public static bool SuppressNoLock; public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) { base.NonQueryExecuting(ReformerSql(command), interceptionContext); } public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { base.ScalarExecuting(ReformerSql(command), interceptionContext); } public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { base.ReaderExecuting(ReformerSql(command), interceptionContext); } //测试用 static System.IO.StreamWriter sw = new System.IO.StreamWriter("c.txt", true, Encoding.Default); static void writeLog(string msg) { if (isDebug) { sw.WriteLine("[{0}][{1}]:{2}", System.Threading.Thread.CurrentThread.ManagedThreadId, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), msg); sw.Flush(); } } protected DbCommand ReformerSql(DbCommand command) { writeLog("原始语句:" + command.CommandText); StringBuilder sb = new StringBuilder(); foreach (DbParameter item in command.Parameters) { if (item != null) sb.AppendFormat("key:{0} value:{1} valueType:{2} ", item.ParameterName, item.Value, item.Value == null ? "" : item.Value.GetType().Name); } writeLog("参数:" + sb); if (command.CommandText.Contains("create table")) {//处理C#与orcal字符串不兼容的问题。 command.CommandText = command.CommandText.Replace("\" nclob", "\" varchar2(1000)"); } //判断执行语句是否为插入语句(是) if (command.CommandText.IndexOf("insert into", StringComparison.InvariantCultureIgnoreCase) >= 0) { string match = @"insert into ""\w+"".""\w+""\("; string match2 = @""".""\w+""\("""; //判断插入语句中是否包括插入Id字段(否) if (command.CommandText.IndexOf("\"Id\",") < 0) { string val = Regex.Match(command.CommandText, match).Value; string obj = Regex.Match(command.CommandText, match2).Value; string table = obj.Substring(3, obj.Length - 6); //如果表名是Hrmanagers,FunMenu,则不需要做任何处理,因为它在插入的时候已经带上了User_Id if (table != "HrManagers" && !table.Contains("FunMenu")) { command.CommandText = command.CommandText.Replace(val, val + "\"Id\", "); command.CommandText = command.CommandText.Replace("values (", "values (SQ_IHR_ID.NEXTVAL, "); writeLog("修改后的语句:" + command.CommandText); } return command; } } return command; } } } // //command.CommandText = command.CommandText.Replace("values (", "values (\"SQ_" + table + "_Id\".NEXTVAL, ");
2.在合适的位置注入拦截器
System.Data.Entity.Infrastructure.Interception.DbInterception.Add(new NoLockInterceptor());