Oracle根据实体类比对2个数据库结构差异（demo）

源起

在公司做项目时 经常出现 实体结构和线上的数据结构以及公司开发库数据结构不匹配的问题 但是又不能直接把开发库导入到生产库因为生产库已经有实际数据了 所以弄了一个小工具

此处只做记录用 demo级 未经过优化

依赖包

DapperExtensions.NetCore

Newtonsoft.Json

Oracle.ManagedDataAccess.Core

代码

新建一个名为 DBHelper 的 core 2.1 控制台程序 加入如下代码：（ps：实体类自备  本例中的实体类通过sqlsugar生成）

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using CommonHelper;
using Dapper;
using DapperExtensions;
using Newtonsoft.Json;

namespace DBHelper
{
    class Program
    {
        //实体模型路径
        public static string modelpath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "DAO");
        //实际数据结构获取源
        public const string sourcedbconnStr = "Data Source=192.168.200.151/orcl;User ID=BG;Password=1;";
        //要比较的数据库连接串
        public const string todbconnStr = "Data Source=192.168.200.151/orcl;User ID=BG2019;Password=1;";
        //缓存数据结构对象  如果存在 则不再去sourcedb中获取结构
        public static string SaveFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "sourceList.txt");
        //生成的添加字段语句
        public static string SqlSaveFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "sql.txt");
        public static List<string> GetAllTableName()
        {
            DirectoryInfo di = new DirectoryInfo(modelpath);
            var res =new  List<string>();
            foreach (var item in di.GetFiles("*.cs"))
            {
                if (!item.Name.ToLower().StartsWith("base"))
                    res.Add(item.Name.Replace(item.Extension,""));
            }
            return res;
        } 

        static void Main(string[] args)
        {
            //记录所有的源数据库 数据结构
            List<SourceSaveList> sl = new List<SourceSaveList>();
            var tableNames = GetAllTableName();
            if (File.Exists(SaveFilePath))
            {
                using (FileStream fs = new FileStream(SaveFilePath, FileMode.Open))
                {
                    using (StreamReader sw = new StreamReader(fs))
                    {
                        var ss = sw.ReadToEnd();
                        sl = JsonConvert.DeserializeObject<List<SourceSaveList>>(ss);
                    }
                }
            }
            else
            {
                var sourceDB = new DapperHelper(sourcedbconnStr);
                foreach (var item in tableNames)
                {
                    var source =sourceDB.Conn.Query<TableColumns>(
                                $"select column_name,data_type,data_length from user_tab_cols where table_name='{item}'")
                            .ToList();
                    sl.Add(new SourceSaveList() { cols = source, tableName = item });
                }
            }
            var toDB = new DapperHelper(todbconnStr);
            StringBuilder sb = new StringBuilder();
            foreach (var item in tableNames)
            {
                var source = sl.First(c => c.tableName == item).cols;
                var to=
                    toDB.Conn.Query<TableColumns>($"select column_name,data_type,data_length from user_tab_cols where table_name='{item}'")
                    .ToList();
                if (source.Count == to.Count) continue;
                foreach (var source_column in source)
                {
                    if (to.Any(c => c.COLUMN_NAME == source_column.COLUMN_NAME)) continue;
                    switch (source_column.DATA_TYPE)
                    {
                        
                        case "DATE":
                            sb.Append($"alter  table {item}  add  {source_column.COLUMN_NAME}  DATE;");
                            break;
                        case "TIMESTAMP(6)":
                            sb.Append($"alter  table {item}  add  {source_column.COLUMN_NAME}  TIMESTAMP(6);");
                            break;
                        case "TIMESTAMP":
                            sb.Append($"alter  table {item}  add  {source_column.COLUMN_NAME}  TIMESTAMP({source_column.DATA_LENGTH});");
                            break;
                        default:
                            sb.Append($"alter  table {item}  add  {source_column.COLUMN_NAME}  {source_column.DATA_TYPE}({source_column.DATA_LENGTH});");
                            break;
                    }
                    sb.Append("\r\n");
                }
            }

            using (FileStream fs = new FileStream(SaveFilePath, FileMode.Create))
            {
                using (StreamWriter sw = new StreamWriter(fs))
                {
                    sw.WriteLine(JsonConvert.SerializeObject(sl));
                }
            }
            using (FileStream fs = new FileStream(SqlSaveFilePath, FileMode.Create))
            {
                using (StreamWriter sw = new StreamWriter(fs))
                {
                    sw.WriteLine(sb.ToString());
                }
            }

        }
    }
}

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using DapperExtensions;
using DapperExtensions.Mapper;
using DapperExtensions.Sql;
using Oracle.ManagedDataAccess.Client;

namespace CommonHelper
{
    /// <summary>
    /// dapper 帮助类
    /// </summary>
    public class DapperHelper
    {
        private Database Connection = null;
        public DapperHelper(string conn)
        {
            var orcalConn = new OracleConnection(conn);
            var orcaleconfig = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new OracleDialect());
            var orcaleGenerator = new SqlGeneratorImpl(orcaleconfig);
            Connection = new Database(orcalConn, orcaleGenerator);
        }
        public IDbConnection Conn
        {
            get
            {
                return Connection.Connection;
            }
        }

    }
}

using System;
using System.Collections.Generic;
using System.Text;

namespace DBHelper
{
   public class SourceSaveList
    {
        public string tableName { get; set; }
        public List<TableColumns> cols { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.Text;

namespace DBHelper
{
    public class TableColumns
    {
        public string COLUMN_NAME { get; set; }
        public string DATA_TYPE { get; set; }
        public string DATA_LENGTH { get; set; }
    }
}

说明

1.根据本例子的配置 在bin 目录中 新建DAO目录并将具体的模型拷入

2.将代码中的sourcedbconnStr 改为开发库连接串

2.将代码中的todbconnStr 改为需要生产库连接串

3.运行后会生成 sourceList.txt 用来保存开发库数据结构 用来支持程序结构拷贝到网络无法连接的生产数据库中

如果该文件存在 则不会再去获取开发库数据结构

4.sql.txt 该文件即为缺失字段的添加语句  如果不存在该表 目前会将所有字段均生成添加语句 此情况目前需要手工处理