private EntityCollection <OracleTableColumnList> GetTableColumnListByName(string name, string strConn) { DBHelperBase <OracleTableColumnList> helper = new DBHelperBase <OracleTableColumnList>(DataBaseType.Oracle9i, strConn); OracleTableColumnList searchCondition = helper.CreateSimpleSeachCondition(); searchCondition.Owner = name; EntityCollection <OracleTableColumnList> tableColumnList = helper.SelectDataByCondition(searchCondition, false); return(tableColumnList); }
private void AnalyseTableColumnDiff(List <OracleTableList> tables, List <OracleTableColumnList> relatedColumns1, List <OracleTableColumnList> relatedColumns2) { tableColumnDiff = new List <TableColumnDiffInfo>(); foreach (OracleTableList table in tables) { List <OracleTableColumnList> columns1 = relatedColumns1.FindAll(new Predicate <OracleTableColumnList>((obj) => { return(obj.Owner == table.Owner && obj.TableName == table.TableName); })); List <OracleTableColumnList> columns2 = relatedColumns2.FindAll(new Predicate <OracleTableColumnList>((obj) => { return(obj.Owner == table.Owner && obj.TableName == table.TableName); })); List <OracleTableColumnList> columns = columns1.Union(columns2, new OracleTableColumnListComparer()).ToList(); bool hasDiff = false; bool isCross = false; //含新库库中没有的列 bool hasNewField = false; //不含新库库中的列 bool hasAttrDiff = false; //列属性存在不同 List <string> sqls = new List <string>(); string[] dataBaseTypeWithoutLength = new string[] { "BLOB", "CLOB", "DATE", "LONG" }; foreach (OracleTableColumnList col in columns) { //修改由于A做基础库 //bool OracleTableColumnList colInTable1 = columns1.Find(new Predicate <OracleTableColumnList>((obj) => { return(obj.Owner == col.Owner && obj.TableName == col.TableName && obj.ColumnName == col.ColumnName); })); OracleTableColumnList colInTable2 = columns2.Find(new Predicate <OracleTableColumnList>((obj) => { return(obj.Owner == col.Owner && obj.TableName == col.TableName && obj.ColumnName == col.ColumnName); })); if (colInTable1 == null && colInTable2 == null) { continue; } else if (colInTable1 != null && colInTable2 == null) { TableColumnDiffInfo diff = new TableColumnDiffInfo(); diff.TableName = col.Owner + "." + col.TableName; diff.ColumName = col.ColumnName; diff.DiffReason = "列在A库的表中但不再B库的表中"; tableColumnDiff.Add(diff); hasNewField = true; hasDiff = true; string s = ""; if (colInTable1.DataType == "NUMBER") { if (colInTable1.DataScale != null && colInTable1.DataPrecision != null) { s = string.Format("alter table {4}.{0} add {1} {2}({5},{6}) {3};", colInTable1.TableName, colInTable1.ColumnName, colInTable1.DataType, colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner, colInTable1.DataPrecision, colInTable1.DataScale); } else if (colInTable1.DataScale == null && colInTable1.DataPrecision != null) { s = string.Format("alter table {4}.{0} add {1} {2} {3};", colInTable1.TableName, colInTable1.ColumnName, dataBaseTypeWithoutLength.Contains(colInTable1.DataType) ? "DATE" : colInTable1.DataType + "(" + colInTable1.DataPrecision + ")", colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner); } else { s = string.Format("alter table {4}.{0} add {1} {2} {3};", colInTable1.TableName, colInTable1.ColumnName, colInTable1.DataType, colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner); } } else { s = string.Format("alter table {4}.{0} add {1} {2} {3};", colInTable1.TableName, colInTable1.ColumnName, dataBaseTypeWithoutLength.Contains(colInTable1.DataType) ? "DATE" : colInTable1.DataType + "(" + colInTable1.DataLength + ")", colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner); } sqls.Add(s); } else if (colInTable1 == null && colInTable2 != null) { TableColumnDiffInfo diff = new TableColumnDiffInfo(); diff.TableName = col.Owner + "." + col.TableName; diff.ColumName = col.ColumnName; diff.DiffReason = "列在B库的表中但不再A库的表中"; tableColumnDiff.Add(diff); isCross = true; hasDiff = true; } else if (colInTable1.Nullable != colInTable2.Nullable) { TableColumnDiffInfo diff = new TableColumnDiffInfo(); diff.TableName = col.Owner + "." + col.TableName; diff.ColumName = col.ColumnName; diff.DiffReason = "列的是否可空不同"; tableColumnDiff.Add(diff); hasAttrDiff = true; hasDiff = true; string s = ""; if (colInTable1.DataType == "NUMBER") { if (colInTable1.DataScale != null && colInTable1.DataPrecision != null) { s = string.Format("alter table {4}.{0} modify {1} {2}({5},{6}) {3};", colInTable1.TableName, colInTable1.ColumnName, colInTable1.DataType, colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner, colInTable1.DataPrecision, colInTable1.DataScale); } else if (colInTable1.DataScale == null && colInTable1.DataPrecision != null) { s = string.Format("alter table {4}.{0} modify {1} {2} {3};", colInTable1.TableName, colInTable1.ColumnName, dataBaseTypeWithoutLength.Contains(colInTable1.DataType) ? "DATE" : colInTable1.DataType + "(" + colInTable1.DataPrecision + ")", colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner); } else { s = string.Format("alter table {4}.{0} modify {1} {2} {3};", colInTable1.TableName, colInTable1.ColumnName, colInTable1.DataType, colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner); } } else { s = string.Format("alter table {4}.{0} modify {1} {2} {3};", colInTable1.TableName, colInTable1.ColumnName, dataBaseTypeWithoutLength.Contains(colInTable1.DataType) ? "DATE" : colInTable1.DataType + "(" + colInTable1.DataLength + ")", colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner); } sqls.Add(s); } else if (colInTable1.DataType != colInTable2.DataType) { TableColumnDiffInfo diff = new TableColumnDiffInfo(); diff.TableName = col.Owner + "." + col.TableName; diff.ColumName = col.ColumnName; diff.DiffReason = "列的类型不同"; tableColumnDiff.Add(diff); hasAttrDiff = true; hasDiff = true; string s = ""; if (colInTable1.DataType == "NUMBER") { if (colInTable1.DataScale != null && colInTable1.DataPrecision != null) { s = string.Format("alter table {4}.{0} modify {1} {2}({5},{6}) ;", colInTable1.TableName, colInTable1.ColumnName, colInTable1.DataType, colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner, colInTable1.DataPrecision, colInTable1.DataScale); } else if (colInTable1.DataScale == null && colInTable1.DataPrecision != null) { s = string.Format("alter table {4}.{0} modify {1} {2} ;", colInTable1.TableName, colInTable1.ColumnName, dataBaseTypeWithoutLength.Contains(colInTable1.DataType) ? "DATE" : colInTable1.DataType + "(" + colInTable1.DataPrecision + ")", colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner); } else { s = string.Format("alter table {4}.{0} modify {1} {2} ;", colInTable1.TableName, colInTable1.ColumnName, colInTable1.DataType, colInTable1.Nullable == "N" ? " not null " : " null ", colInTable1.Owner); } } else { s = string.Format("alter table {4}.{0} modify {1} {2} ;", colInTable1.TableName, colInTable1.ColumnName, dataBaseTypeWithoutLength.Contains(colInTable1.DataType) ? "DATE" : colInTable1.DataType + "(" + colInTable1.DataLength + ")", "", colInTable1.Owner); } sqls.Add(s); } else if (colInTable1.DataLength != colInTable2.DataLength) { TableColumnDiffInfo diff = new TableColumnDiffInfo(); diff.TableName = col.Owner + "." + col.TableName; diff.ColumName = col.ColumnName; diff.DiffReason = "列的数据长度不同"; tableColumnDiff.Add(diff); hasAttrDiff = true; hasDiff = true; string s = ""; if (colInTable1.DataType == "NUMBER") { if (colInTable1.DataScale != null && colInTable1.DataPrecision != null) { s = string.Format("alter table {4}.{0} modify {1} {2}({5},{6}) ;", colInTable1.TableName, colInTable1.ColumnName, colInTable1.DataType, colInTable1.Nullable == "N" ? " not null " : "", colInTable1.Owner, colInTable1.DataPrecision, colInTable1.DataScale); } else if (colInTable1.DataScale == null && colInTable1.DataPrecision != null) { s = string.Format("alter table {4}.{0} modify {1} {2} ;", colInTable1.TableName, colInTable1.ColumnName, dataBaseTypeWithoutLength.Contains(colInTable1.DataType) ? "DATE" : colInTable1.DataType + "(" + colInTable1.DataPrecision + ")", colInTable1.Nullable == "N" ? " not null " : "", colInTable1.Owner); } else { s = string.Format("alter table {4}.{0} modify {1} {2} ;", colInTable1.TableName, colInTable1.ColumnName, colInTable1.DataType, colInTable1.Nullable == "N" ? " not null " : "", colInTable1.Owner); } } else { s = string.Format("alter table {4}.{0} modify {1} {2} ;", colInTable1.TableName, colInTable1.ColumnName, dataBaseTypeWithoutLength.Contains(colInTable1.DataType) ? "DATE" : colInTable1.DataType + "(" + colInTable1.DataLength + ")", "", colInTable1.Owner); } sqls.Add(s); } } if (hasDiff) { TableDiffInfo diff = new TableDiffInfo(); diff.TableName = table.Owner + "." + table.TableName; diff.DiffReason = ""; diff.UpdateSql = sqls; if (hasAttrDiff) { diff.DiffReason = diff.DiffReason + "|存在列属性不同"; } if (hasNewField) { diff.DiffReason = diff.DiffReason + "|缺少列"; } if (isCross) { diff.UpdateSql = null; diff.DiffReason = "|老库存在新库中没有的字段"; } tableDiff.Add(diff); } } }