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

usingSystem;usingSystem.Collections.Generic;usingSystem.Data;usingSystem.IO;usingSystem.Linq;usingSystem.Text;usingCommonHelper;usingDapper;usingDapperExtensions;usingNewtonsoft.Json;namespaceDBHelper

{classProgram

{//实体模型路径

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 ListGetAllTableName()

{

DirectoryInfo di= newDirectoryInfo(modelpath);var res =new List();foreach (var item in di.GetFiles("*.cs"))

{if (!item.Name.ToLower().StartsWith("base"))

res.Add(item.Name.Replace(item.Extension,""));

}returnres;

}static void Main(string[] args)

{//记录所有的源数据库 数据结构

List sl = new List();var tableNames =GetAllTableName();if(File.Exists(SaveFilePath))

{using (FileStream fs = newFileStream(SaveFilePath, FileMode.Open))

{using (StreamReader sw = newStreamReader(fs))

{var ss =sw.ReadToEnd();

sl= JsonConvert.DeserializeObject>(ss);

}

}

}else{var sourceDB = newDapperHelper(sourcedbconnStr);foreach (var item intableNames)

{var source =sourceDB.Conn.Query(

$"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 = newDapperHelper(todbconnStr);

StringBuilder sb= newStringBuilder();foreach (var item intableNames)

{var source = sl.First(c => c.tableName ==item).cols;var to=toDB.Conn.Query($"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 insource)

{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 = newFileStream(SaveFilePath, FileMode.Create))

{using (StreamWriter sw = newStreamWriter(fs))

{

sw.WriteLine(JsonConvert.SerializeObject(sl));

}

}using (FileStream fs = newFileStream(SqlSaveFilePath, FileMode.Create))

{using (StreamWriter sw = newStreamWriter(fs))

{

sw.WriteLine(sb.ToString());

}

}

}

}

}