private void btnExportAllSQL_Click(object sender, EventArgs e) { SaveFileDialog file = new SaveFileDialog(); file.ShowDialog(); if (!string.IsNullOrEmpty(file.FileName)) { using (FileStream fs = (FileStream)file.OpenFile()) { foreach (ListViewItem item in listView1.CheckedItems) { TableDiffInfo diff = item.Tag as TableDiffInfo; if (diff == null || diff.UpdateSql == null || diff.UpdateSql.Count == 0) { continue; } // byte[] bts1 = System.Text.Encoding.UTF8.GetBytes(System.Environment.NewLine); byte[] bts = System.Text.Encoding.UTF8.GetBytes(string.Join(System.Environment.NewLine, diff.UpdateSql)); byte[] bts1 = System.Text.Encoding.UTF8.GetBytes(System.Environment.NewLine + "/" + System.Environment.NewLine); fs.Write(bts, 0, bts.Length); fs.Write(bts1, 0, bts1.Length); } } } }
private void AnalyseTableDiff(EntityCollection <OracleTableList> table1, EntityCollection <OracleTableList> table2) { tableDiff = new List <TableDiffInfo>(); inTable1NotInTable2 = table1.Except(table2, new OracleTableListComparer()).ToList(); bothIn2Table = table1.Intersect(table2, new OracleTableListComparer()).ToList(); inTable2NotInTable1 = table2.Except(table1, new OracleTableListComparer()).ToList(); foreach (OracleTableList table in inTable1NotInTable2) { TableDiffInfo diff = new TableDiffInfo(); diff.TableName = table.Owner + "." + table.TableName; //diff.DiffReason = "表在A库中存在但在B库中不存在"; diff.DiffReason = "老库存在新库中没有的表"; if (diff.UpdateSql == null) { diff.UpdateSql = new List <string>(); } diff.UpdateSql.Add(GetDDLForTable(table.TableName, table.Owner)); tableDiff.Add(diff); } //foreach (OracleTableList table in inTable2NotInTable1) //{ // TableDiffInfo diff = new TableDiffInfo(); // diff.TableName = table.Owner + "." + table.TableName; // diff.DiffReason = "表在B库中存在但在A库中不存在"; // tableDiff.Add(diff); //} }
private void btnAnalyse_Click(object sender, EventArgs e) { // StopWatcher strCon1 = string.Format("User ID={0};Data Source={1};Password={2}", txtUserName1.Text, txtSid1.Text, txtPassword1.Text); strCon2 = string.Format("User ID={0};Data Source={1};Password={2}", txtUserName2.Text, txtSid2.Text, txtPassword2.Text); EntityCollection <OracleTableList> tableList1 = GetTableListByName(txtDBUser.Text.ToUpper(), strCon1); EntityCollection <OracleTableList> tableList2 = GetTableListByName(txtDBUser.Text.ToUpper(), strCon2); EntityCollection <OracleTableColumnList> tableColumn1 = GetTableColumnListByName(txtDBUser.Text, strCon1); EntityCollection <OracleTableColumnList> tableColumn2 = GetTableColumnListByName(txtDBUser.Text, strCon2); AnalyseTableDiff(tableList1, tableList2); if (bothIn2Table.Count > 0) { AnalyseTableColumnDiff(bothIn2Table, tableColumn1.ToList(), tableColumn2.ToList()); } listView1.Columns.Clear(); this.listView1.GridLines = true; //显示表格线 this.listView1.View = View.Details; //显示表格细节 this.listView1.Scrollable = true; //有滚动条 this.listView1.HeaderStyle = ColumnHeaderStyle.Clickable; //对表头进行设置 this.listView1.FullRowSelect = true; //是否可以选择行 this.listView1.Columns.Add("表名"); this.listView1.Columns.Add("差异"); listView1.CheckBoxes = true; //this.listView1.AutoResizeColumns(ColumnHeaderAutoResizeStyle.ColumnContent); listView2.Columns.Clear(); this.listView2.GridLines = true; //显示表格线 this.listView2.View = View.Details; //显示表格细节 this.listView2.Scrollable = true; //有滚动条 this.listView2.HeaderStyle = ColumnHeaderStyle.Clickable; //对表头进行设置 this.listView2.FullRowSelect = true; //是否可以选择行 this.listView2.Columns.Add("列名"); this.listView2.Columns.Add("差异"); //this.listView2.AutoResizeColumns(ColumnHeaderAutoResizeStyle.ColumnContent); foreach (TableDiffInfo diff in tableDiff) { ListViewItem item = new ListViewItem(); item.SubItems[0].Text = diff.TableName; item.Tag = diff; item.SubItems.Add(diff.DiffReason); // List<TableColumnDiffInfo> columnDiff = tableColumnDiff.FindAll(new Predicate<TableColumnDiffInfo>((obj) => { return obj.TableName == diff.TableName; })); listView1.Items.Add(item); } this.listView1.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize); listView1.ItemSelectionChanged += (object sender1, ListViewItemSelectionChangedEventArgs e1) => { listView2.Items.Clear(); string tableName = e1.Item.Text; TableDiffInfo diff = tableDiff.Find(new Predicate <TableDiffInfo>((obj) => { return(obj.TableName == tableName); })); List <TableColumnDiffInfo> columnDiff = tableColumnDiff.FindAll(new Predicate <TableColumnDiffInfo>((obj) => { return(obj.TableName == tableName); })); foreach (TableColumnDiffInfo subdiff in columnDiff) { ListViewItem item = new ListViewItem(); item.SubItems[0].Text = subdiff.ColumName; item.SubItems.Add(subdiff.DiffReason); listView2.Items.Add(item); } this.listView2.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize); richTextBox1.Clear(); if (diff.UpdateSql != null) { foreach (string s in diff.UpdateSql) { richTextBox1.AppendText(s); } } }; }
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); } } }