XdCxRhDW/XdCxRhDW.Repostory/EFContext/RHDWContext.cs

using SQLite.CodeFirst;
using System;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Core.Common;
using System.Data.Entity.Infrastructure.Interception;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Data.SQLite;
using System.Data.SQLite.EF6;
using System.IO;
using System.Text.RegularExpressions;
using XdCxRhDW.Entity;

namespace XdCxRhDW.Repostory
{

    /// <summary>
    /// 基础表上下文(id为int)
    /// </summary>
    [DbConfigurationType(typeof(SqliteConfiguration))]
    public class RHDWContext : DbContext
    {
        public RHDWContext() : base("DbCon") //配置使用的连接名
        {

        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            this.Database.Log = msg =>
            {
            };
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            //modelBuilder.Configurations.Add<RHDWContext>(new EntityTypeConfiguration<RHDWContext>());//new SqliteConfiguration()
            modelBuilder.Configurations.AddFromAssembly(typeof(RHDWContext).Assembly);//自动加载SqliteConfiguration

            var sqliteConnectionInitializer = new SqliteCreateDatabaseIfNotExists<RHDWContext>(modelBuilder);
            Database.SetInitializer(sqliteConnectionInitializer);
            DbInterception.Add(new SqliteInterceptor());//拦截器
            base.OnModelCreating(modelBuilder);
        }
        public DbSet<XlInfo> XlInfos { set; get; }

        public DbSet<TaskInfo> TaskInfos { set; get; }

        public DbSet<TxInfo> TxInfos { get; set; }

        public DbSet<SatInfo> SatInfos { get; set; }

        public DbSet<SigInfo> SigInfos { get; set; }

        public DbSet<SigDelay> SigDelays { get; set; }

        public DbSet<TargetInfo> TargetInfos { get; set; }

        public DbSet<SysSetings> SysSetings { get; set; }

    }

    /// <summary>
    /// 分区表上下文(id为long)
    /// </summary>
    [DbConfigurationType(typeof(SqliteConfiguration))]
    public class RHDWPartContext : DbContext
    {
        public bool DatabaseExist = false;
        public static RHDWPartContext GetContext(string dbFile)
        {
            var connectionString = $@"Data Source={dbFile}";
            SQLiteConnection con = new SQLiteConnection(connectionString);
            bool databaseExist = File.Exists(dbFile);
            return new RHDWPartContext(con) { DatabaseExist = databaseExist };
        }

        public static RHDWPartContext GetContext(DateTime partTime, bool isCreateDb = false, string prefix = "")
        {
            var dir = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "DbPart");
            var dayFile = Path.Combine(dir, $@"{partTime.Year}\{prefix}{partTime:MMdd}.db");
            bool databaseExist = File.Exists(dayFile);
            if (isCreateDb)
            {
                databaseExist = true;
            }
            var connectionString = $@"Data Source=|DataDirectory|\DbPart\{partTime.Year}\{prefix}{partTime:MMdd}.db";
            SQLiteConnection con = new SQLiteConnection(connectionString);
            return new RHDWPartContext(con) { DatabaseExist = databaseExist };
        }

        private RHDWPartContext(DbConnection con)
        : base(con, true)
        {
        }


        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            this.Database.Log = msg =>
            {
            };

            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            modelBuilder.Configurations.AddFromAssembly(typeof(RHDWPartContext).Assembly);
            var sqliteConnectionInitializer = new SqliteCreateDatabaseIfNotExists<RHDWPartContext>(modelBuilder);
            Database.SetInitializer(sqliteConnectionInitializer);
            DbInterception.Add(new SqliteInterceptor());//拦截器
            base.OnModelCreating(modelBuilder);
        }

        public DbSet<StationRes> StationRes { get; set; }
        public DbSet<CxRes> CxRes { get; set; }
        public DbSet<CgRes> CgRes { get; set; }
        public DbSet<CgXgfRes> CgXgfRes { get; set; }
        public DbSet<PosRes> PosRes { get; set; }
        public DbSet<CheckRes> CheckRes { get; set; }
    }

    /// <summary>
    /// 分区表上下文(id为long)
    /// </summary>
    [DbConfigurationType(typeof(PartSqliteInterceptor))]
    public class RHDWPartReadContext : DbContext
    {
        public bool DatabaseExist = false;
        private string dbFile;
        public static RHDWPartReadContext GetContext(string dbFile)
        {
            var connectionString = $@"Data Source={dbFile}";
            SQLiteConnection con = new SQLiteConnection(connectionString);
            bool databaseExist = File.Exists(dbFile);
            return new RHDWPartReadContext(con) { DatabaseExist = databaseExist, dbFile = dbFile };
        }

        public static RHDWPartReadContext GetContext(DateTime partTime, bool isCreateDb = false, string prefix = "")
        {
            var dir = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "DbPart");
            var dayFile = Path.Combine(dir, $@"{partTime.Year}\{prefix}{partTime:MMdd}.db");
            bool databaseExist = File.Exists(dayFile);
            if (isCreateDb)
            {
                databaseExist = true;
            }
            var connectionString = $@"Data Source=|DataDirectory|\DbPart\{partTime.Year}\{prefix}{partTime:MMdd}.db";
            SQLiteConnection con = new SQLiteConnection(connectionString);
            return new RHDWPartReadContext(con) { DatabaseExist = databaseExist, dbFile= dayFile };
        }

        private RHDWPartReadContext(DbConnection con)
        : base(con, true)
        {
        }


        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            this.Database.Log = msg =>
            {
            };

            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            modelBuilder.Configurations.AddFromAssembly(typeof(RHDWPartReadContext).Assembly);
            Database.SetInitializer<RHDWPartReadContext>(null);
           // DbInterception.Add(new PartSqliteInterceptor(this.dbFile));//拦截器
            base.OnModelCreating(modelBuilder);
        }

        public DbSet<StationRes> StationRes { get; set; }
        public DbSet<CxRes> CxRes { get; set; }
        public DbSet<CgRes> CgRes { get; set; }
        public DbSet<CgXgfRes> CgXgfRes { get; set; }
        public DbSet<PosRes> PosRes { get; set; }
        public DbSet<CheckRes> CheckRes { get; set; }
    }
    /// <summary>
    /// Sqlite拦截器.
    /// contains或indexOf成的CHARINDEX函数在sqlite里面并不支持，需要拦截转换成LIKE语句
    /// </summary>
    public class PartSqliteInterceptor : IDbCommandInterceptor
    {
        private readonly string dbFile;
        public PartSqliteInterceptor(string dbFile)
        {
            this.dbFile = dbFile;
        }
        private static Regex replaceRegex = new Regex(@"\(CHARINDEX\((.*?),\s?(.*?)\)\)\s*?>\s*?0");

        public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
        }

        public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
        }

        public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
        }

        public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
            if (!File.Exists(dbFile))
                interceptionContext.Result = null;
            else
                ReplaceCharIndexFunc(command);
        }

        public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
        }

        public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
            if (!File.Exists(dbFile))
                interceptionContext.Result = null;
            else
                ReplaceCharIndexFunc(command);
        }

        private void ReplaceCharIndexFunc(DbCommand command)
        {
            bool isMatch = false;
            var text = replaceRegex.Replace(command.CommandText, (match) =>
            {
                if (match.Success)
                {
                    string paramsKey = match.Groups[1].Value;
                    string paramsColumnName = match.Groups[2].Value;
                    //replaceParams
                    foreach (DbParameter param in command.Parameters)
                    {
                        if (param.ParameterName == paramsKey.Substring(1))
                        {
                            param.Value = string.Format("%{0}%", param.Value);
                            break;
                        }
                    }
                    isMatch = true;
                    return string.Format("{0} LIKE {1}", paramsColumnName, paramsKey);
                }
                else
                    return match.Value;
            });
            if (isMatch)
                command.CommandText = text;
        }
    }
    public class SqliteConfiguration : DbConfiguration
    {
        public SqliteConfiguration()
        {
            SetProviderFactory("System.Data.SQLite", SQLiteFactory.Instance);
            SetProviderFactory("System.Data.SQLite.EF6", SQLiteProviderFactory.Instance);
            SetProviderServices("System.Data.SQLite", (DbProviderServices)SQLiteProviderFactory.Instance.GetService(typeof(DbProviderServices)));

        }
    }
    public class DbPartSqliteConfiguration : DbConfiguration
    {
        public DbPartSqliteConfiguration(IDbInterceptor dbInterceptor)
        {
            base.AddInterceptor(dbInterceptor);
        }
    }
}