Orcal数据库,使用EF的自增处理

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 [Extentd+](?! 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());
原文地址:https://www.cnblogs.com/LittleJin/p/9950347.html