XdCxRhDW/XdCxRhDW.Repostory/EFContext/RHDWContext.cs

using SQLite.CodeFirst;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Core.Common;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Infrastructure.Interception;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Data.Entity.Validation;
using System.Data.SQLite;
using System.Data.SQLite.EF6;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using XdCxRhDW.Entity;

namespace XdCxRhDW.Repostory
{
    class DbTableColumnInfo
    {
        public string name { get; set; }

        public string type { get; set; }

        public int notnull { get; set; }

        public int pk { get; set; }
    }
    class DbTableForeignKeyInfo
    {
        public string from { get; set; }
    }
    public class RHDWLogContext : DbContext
    {
        public string DbFile;
        public RHDWLogContext() : base("LogDbCon") //配置使用的连接名
        {
            //|DataDirectory|在mvc等程序中代表了App_Data,在普通程序中代表程序根目录
            var dbFile = Database.Connection.ConnectionString.Replace("Data Source=", "").Replace("|DataDirectory|\\", "");
            this.DbFile = dbFile;
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            this.Database.Log = msg =>
            {
            };
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            modelBuilder.Configurations.AddFromAssembly(typeof(RHDWLogContext).Assembly);//自动加载Entity-Type
            var sqliteConnectionInitializer = new SqliteCreateDatabaseIfNotExists<RHDWLogContext>(modelBuilder);
            Database.SetInitializer(sqliteConnectionInitializer);
            base.OnModelCreating(modelBuilder);
        }
        public DbSet<LogRes> LogRes { set; get; }
    }
    /// <summary>
    /// 基础表上下文(id为int)
    /// </summary>
    public class RHDWContext : DbContext
    {
        public string DbFile;
        public RHDWContext() : base("DbCon") //配置使用的连接名
        {
            //|DataDirectory|在mvc等程序中代表了App_Data,在普通程序中代表程序根目录
            this.DbFile = Database.Connection.ConnectionString.Replace("Data Source=", "").Replace("|DataDirectory|\\", "");
        }

        public Task<List<T>> SqlQueryAsync<T>(string sql)
        {
            return this.Database.SqlQuery<T>(sql).ToListAsync();
        }
        public Task<T> SqlQueryOneAsync<T>(string sql)
        {
            return this.Database.SqlQuery<T>(sql).FirstOrDefaultAsync();

        }
        //检查数据库表是否缺失
        public string CheckTableExist()
        {
            var tables = this.Database.SqlQuery<string>("select name from sqlite_master where type='table' and  name not like 'sqlite%'").ToList();
            var props = this.GetType().GetProperties();
            List<string> list = new List<string>();
            foreach (var prop in props)
            {
                bool isDbSet = prop.PropertyType.IsGenericType && typeof(DbSet<>) == prop.PropertyType.GetGenericTypeDefinition();
                if (isDbSet)
                {
                    var entityType = prop.PropertyType.GenericTypeArguments[0];
                    var name = entityType.GetCustomAttribute<TableAttribute>()?.Name;
                    if (name == null)
                    {
                        name = entityType.Name;
                    }
                    list.Add(name);
                }
            }
            foreach (var item in list)
            {
                if (!tables.Contains(item))
                {
                    return item;
                }
            }
            return "";
        }
        public string CheckTableField()
        {
            var tables = this.Database.SqlQuery<string>("select name from sqlite_master where type='table' and  name not like 'sqlite%'").ToList();
            var props = this.GetType().GetProperties();
            List<Type> entityTypes = new List<Type>();
            foreach (var prop in props)
            {
                bool isDbSet = prop.PropertyType.IsGenericType && typeof(DbSet<>) == prop.PropertyType.GetGenericTypeDefinition();
                if (isDbSet)
                {
                    var entityType = prop.PropertyType.GenericTypeArguments[0];
                    entityTypes.Add(entityType);
                }
            }
            foreach (var table in tables)
            {
                var res = this.Database.SqlQuery<DbTableColumnInfo>($"PRAGMA table_info([{table}])").ToList();
                var entityType = entityTypes.First(p => p.Name == table);
                var entityProps = entityType.GetProperties().Where(p =>
                    p.CanRead
                    && p.CanWrite
                    && !p.GetMethod.IsVirtual
                    && p.GetCustomAttribute<NotMappedAttribute>() == null);
                foreach (var prop in entityProps)
                {
                    var find = res.Find(p => p.name == prop.Name);
                    if (find == null)
                    {
                        string typeStr = "";
                        var type = prop.PropertyType;
                        if (prop.PropertyType.IsGenericType)
                        {
                            type = prop.PropertyType.GenericTypeArguments[0];
                        }
                        if (type == typeof(string))
                        {
                            typeStr = "nvarchar";
                        }
                        else if (type == typeof(int) || type == typeof(long))
                        {
                            typeStr = "int";
                        }
                        else if (type == typeof(double) || type == typeof(float))
                        {
                            typeStr = "float";
                        }
                        else if (type == typeof(DateTime))
                        {
                            typeStr = "datetime";
                        }
                        return $"{DbFile}数据库表{table}缺少{prop.Name}字段,类型={typeStr}";
                    }
                    else
                    {

                        if (prop.PropertyType != typeof(string) && find.pk == 0 && !prop.PropertyType.IsGenericType && find.notnull == 0)
                        {
                            var foreignInfo = this.Database.SqlQuery<DbTableForeignKeyInfo>($"PRAGMA foreign_key_list({table})").ToList();
                            if (!foreignInfo.Any(p => p.from == prop.Name))
                            {
                                return $"{DbFile}数据库表{table}中{prop.Name}字段不允许为空";
                            }
                        }
                    }
                }
            }
            return "";
        }
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            this.Database.Log = msg =>
            {
            };
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            modelBuilder.Configurations.AddFromAssembly(typeof(RHDWContext).Assembly);//自动加载Entity-Type
            var sqliteConnectionInitializer = new SqliteCreateDatabaseIfNotExists<RHDWContext>(modelBuilder);
            Database.SetInitializer(sqliteConnectionInitializer);
            base.OnModelCreating(modelBuilder);
        }
        public DbSet<XlInfo> XlInfos { set; get; }

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

        public DbSet<TaskSig> TaskSigs { set; get; }

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

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

        public DbSet<FixedStation> FixedStation { 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>
    public class RHDWPartContext : DbContext
    {
        private string DbFile;
        public static RHDWPartContext GetContext(string dbFile, bool createDb = false)
        {
            if (!File.Exists(dbFile) && !createDb)
            {
                return null;
            }
            var connectionString = $@"Data Source={dbFile}";
            SQLiteConnection con = new SQLiteConnection(connectionString);
            return new RHDWPartContext(con);
        }
        public static RHDWPartContext GetContext(DateTime partTime, bool createDb = false, string prefix = "")
        {
            var dir = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "DbPart");
            var dayFile = Path.Combine(dir, $@"{partTime.Year}\{prefix}{partTime:MMdd}.db");
            if (!File.Exists(dayFile) && !createDb)
            {
                return null;
            }
            var connectionString = $@"Data Source=|DataDirectory|\DbPart\{partTime.Year}\{prefix}{partTime:MMdd}.db";
            SQLiteConnection con = new SQLiteConnection(connectionString);
            return new RHDWPartContext(con);
        }
        private RHDWPartContext(DbConnection con)
        : base(con, true)
        {
            var dbFile = Database.Connection.ConnectionString.Replace("Data Source=", "").Replace("|DataDirectory|\\", "");
            this.DbFile = dbFile;
        }

        public string CheckTableField()
        {
            var tables = this.Database.SqlQuery<string>("select name from sqlite_master where type='table' and  name not like 'sqlite%'").ToList();
            var props = this.GetType().GetProperties();
            List<Type> entityTypes = new List<Type>();
            foreach (var prop in props)
            {
                bool isDbSet = prop.PropertyType.IsGenericType && typeof(DbSet<>) == prop.PropertyType.GetGenericTypeDefinition();
                if (isDbSet)
                {
                    var entityType = prop.PropertyType.GenericTypeArguments[0];
                    entityTypes.Add(entityType);
                }
            }
            foreach (var table in tables)
            {
                var res = this.Database.SqlQuery<DbTableColumnInfo>($"PRAGMA table_info([{table}])").ToList();
                var entityType = entityTypes.First(p => p.Name == table);
                var entityProps = entityType.GetProperties().Where(p =>
                    p.CanRead
                    && p.CanWrite
                    && !p.GetMethod.IsVirtual
                    && p.GetCustomAttribute<NotMappedAttribute>() == null);
                foreach (var prop in entityProps)
                {
                    var find = res.Find(p => p.name == prop.Name);
                    if (find == null)
                    {
                        string typeStr = "";
                        var type = prop.PropertyType;
                        if (prop.PropertyType.IsGenericType)
                        {
                            type = prop.PropertyType.GenericTypeArguments[0];
                        }
                        if (type == typeof(string))
                        {
                            typeStr = "nvarchar";
                        }
                        else if (type == typeof(int) || type == typeof(long))
                        {
                            typeStr = "int";
                        }
                        else if (type == typeof(double) || type == typeof(float))
                        {
                            typeStr = "float";
                        }
                        else if (type == typeof(DateTime))
                        {
                            typeStr = "datetime";
                        }
                        return $"{DbFile}数据库表{table}缺少{prop.Name}字段,类型={typeStr}";
                    }
                    else
                    {

                        if (prop.PropertyType != typeof(string) && find.pk == 0 && !prop.PropertyType.IsGenericType && find.notnull == 0)
                        {
                            var foreignInfo = this.Database.SqlQuery<DbTableForeignKeyInfo>($"PRAGMA foreign_key_list({table})").ToList();
                            if (!foreignInfo.Any(p => p.from == prop.Name))
                            {
                                return $"{DbFile}数据库表{table}中{prop.Name}字段不允许为空";
                            }
                        }
                    }
                }
            }
            return "";
        }
        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);
            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; }
    }

    public class SqliteConfiguration : DbConfiguration
    {
        public SqliteConfiguration()
        {
            DbInterception.Add(new SqliteInterceptor());//拦截器
            SetProviderFactory("System.Data.SQLite", SQLiteFactory.Instance);
            SetProviderFactory("System.Data.SQLite.EF6", SQLiteProviderFactory.Instance);
            SetProviderServices("System.Data.SQLite", (DbProviderServices)SQLiteProviderFactory.Instance.GetService(typeof(DbProviderServices)));

        }
    }
}