XdCxRhDW/XdCxRhDW.Repostory/EFContext/MySqlSync.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
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; }
    }
    public class MySqlSync : MySqlContext
    {
        private string database;
        public MySqlSync()
        {
            database = this.Database.Connection.Database;
        }
        /// <summary>
        /// 同步数据库表、字段、索引
        /// </summary>
        public void SyncDb()
        {
            SyncTable();
            SyncTableField();
            SyncIndex();
        }
        void SyncTable()
        {
            var tables = this.Database.SqlQuery<string>($"select table_name from information_schema.tables where table_schema='{database}'").ToList();
            var props = this.GetType().GetProperties();
            Dictionary<string, Type> list = new Dictionary<string, Type>();
            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, entityType);
                }
            }
            foreach (var item in list)
            {
                if (!tables.Any(p => p.ToLower() == item.Key.ToLower()))
                {
                    var entityProps = item.Value.GetProperties().Where(p =>
                        p.CanRead
                        && p.CanWrite
                        && !p.GetMethod.IsVirtual
                        && p.GetCustomAttribute<NotMappedAttribute>() == null);
                    item.Value.GetProperties().Where(p => p.CanWrite);
                    StringBuilder sb = new StringBuilder();
                    sb.Append($"create table {item.Key}(ID integer primary key autoincrement");
                    foreach (var prop in entityProps)
                    {
                        if (prop.Name.ToLower() == "id") continue;
                        bool isNullable = prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>);
                        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) || prop.PropertyType.IsEnum)
                        {
                            typeStr = "int";
                        }
                        else if (type == typeof(double) || type == typeof(float))
                        {
                            typeStr = "float";
                        }
                        else if (type == typeof(DateTime))
                        {
                            typeStr = "datetime";
                        }
                        sb.Append($",{prop.Name} {typeStr}");
                        if (!isNullable)
                        {
                            sb.Append(" not null");
                        }
                    }
                    sb.Append(")");
                    string createTableSql = sb.ToString();
                    Database.ExecuteSqlCommand(createTableSql);
                }
            }
        }
        void SyncIndex()
        {
            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.FirstOrDefault(p => p.Name == table);
                if (entityType == null) continue;

                //创建具有Index特性的索引列
                var entityProps = entityType.GetProperties().Where(p =>
                    p.CanRead
                    && p.Name.ToUpper() != "ID"
                    && p.CanWrite
                    && !p.GetMethod.IsVirtual
                    && p.GetCustomAttribute<IndexAttribute>() != null
                    && p.GetCustomAttribute<NotMappedAttribute>() == null);

                foreach (var prop in entityProps)
                {
                    var index = this.Database.SqlQuery<string>($"SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='{table}' and name='IX_{table}_{prop.Name}'").FirstOrDefault();
                    if (index == null)
                    {
                        this.Database.ExecuteSqlCommand($"CREATE INDEX 'IX_{table}_{prop.Name}' ON {table} ('{prop.Name}')");
                    }
                }
            }
        }
        void SyncTableField()
        {
            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.FirstOrDefault(p => p.Name == table);
                if (entityType == null) continue;
                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.ToLower() == prop.Name.ToLower());
                    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) || prop.PropertyType.IsEnum)
                        {
                            typeStr = "int";
                        }
                        else if (type == typeof(double) || type == typeof(float))
                        {
                            typeStr = "float";
                        }
                        else if (type == typeof(DateTime))
                        {
                            typeStr = "datetime";
                        }
                        else if (type == typeof(bool))
                        {
                            typeStr = "int";
                        }
                        bool isNullable = prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>);

                        string updateTableSql;
                        if (isNullable)
                            updateTableSql = $"alter table {table} add column {prop.Name} {typeStr}";
                        else
                        {
                            if (typeStr == "int" || typeStr == "float")
                                updateTableSql = $"alter table {table} add column {prop.Name} {typeStr} not null default 0";
                            else if (typeStr == "nvarchar")
                                updateTableSql = $"alter table {table} add column {prop.Name} {typeStr} not null default ''";
                            else if (typeStr == "datetime")
                                updateTableSql = $"alter table {table} add column {prop.Name} {typeStr} not null default '{DateTime.Now:yyyy-MM-dd HH:mm:ss}'";
                            else
                                updateTableSql = $"alter table {table} add column {prop.Name} {typeStr}";
                        }
                        this.Database.ExecuteSqlCommand(updateTableSql);
                    }
                }
            }
        }
    }
}