private void CompareViewCode(ConnectionType otherConn, List <TableType> thisObjs, List <TableType> otherObjs, StringBuilder builder) { try { var helper = new DbHelper(this.dataInfo); var otherHelper = new DbHelper(otherConn); var views = new List <string>(); foreach (var thisView in thisObjs) { if (otherObjs.FindTable(thisView.Name) != null) { views.Add(thisView.Name); continue; } } var v_str = ""; for (var index = 0; index < views.Count; index++) { if (index > 0) { v_str += ","; } v_str += string.Format("'{0}'", views[index]); } var sql = string.Format(@"SELECT obj.name,m.definition FROM sys.sql_modules m JOIN sys.objects obj ON m.object_id= obj.object_id and obj.type='v' and obj.name in ({0}) order by obj.name", v_str); var thisDs = helper.ExecuteDataSet(sql); var otherDs = otherHelper.ExecuteDataSet(sql); for (var index = 0; index < thisDs.Tables[0].Rows.Count; index++) { var thisRow = thisDs.Tables[0].Rows[index]; var otherRow = otherDs.Tables[0].Rows[index]; var thisSql = thisRow["definition"].ToString().Trim(); var otherSql = otherRow["definition"].ToString().Trim(); var thisReader = new System.IO.StringReader(thisSql); var otherReader = new System.IO.StringReader(otherSql); var thisFirstLine = thisReader.ReadLine().Replace("[", "").Replace("]", ""); var otherFirstLine = otherReader.ReadLine().Replace("[", "").Replace("]", ""); if (thisFirstLine != otherFirstLine) { builder.AppendFormat("View Difference {0}", thisRow["name"].ToString()); builder.AppendLine(); continue; } var this_ = thisReader.ReadToEnd(); var other_ = otherReader.ReadToEnd(); if (this_ != other_) { builder.AppendFormat("View Difference {0}", thisRow["name"].ToString()); builder.AppendLine(); continue; } //if (thisRow["definition"].ToString().Trim() != otherRow["definition"].ToString().Trim()) //{ // builder.AppendFormat("View Difference {0}", thisRow["name"].ToString()); // builder.AppendLine(); //} } helper.Close(); otherHelper.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void CompareViewCode(ConnectionType otherConn, List<TableType> thisObjs, List<TableType> otherObjs, StringBuilder builder) { try { var helper = new DbHelper(this.dataInfo); var otherHelper = new DbHelper(otherConn); var views = new List<string>(); foreach (var thisView in thisObjs) { if (otherObjs.FindTable(thisView.Name) != null) { views.Add(thisView.Name); continue; } } var v_str = ""; for (var index = 0; index < views.Count; index++) { if (index > 0) v_str += ","; v_str += string.Format("'{0}'", views[index]); } var sql = string.Format(@"SELECT obj.name,m.definition FROM sys.sql_modules m JOIN sys.objects obj ON m.object_id= obj.object_id and obj.type='v' and obj.name in ({0}) order by obj.name", v_str); var thisDs = helper.ExecuteDataSet(sql); var otherDs = otherHelper.ExecuteDataSet(sql); for (var index = 0; index < thisDs.Tables[0].Rows.Count; index++) { var thisRow = thisDs.Tables[0].Rows[index]; var otherRow = otherDs.Tables[0].Rows[index]; var thisSql = thisRow["definition"].ToString().Trim(); var otherSql = otherRow["definition"].ToString().Trim(); var thisReader = new System.IO.StringReader(thisSql); var otherReader = new System.IO.StringReader(otherSql); var thisFirstLine = thisReader.ReadLine().Replace("[", "").Replace("]",""); var otherFirstLine = otherReader.ReadLine().Replace("[", "").Replace("]", ""); if (thisFirstLine != otherFirstLine) { builder.AppendFormat("View Difference {0}", thisRow["name"].ToString()); builder.AppendLine(); continue; } var this_ = thisReader.ReadToEnd(); var other_ = otherReader.ReadToEnd(); if (this_ != other_) { builder.AppendFormat("View Difference {0}", thisRow["name"].ToString()); builder.AppendLine(); continue; } //if (thisRow["definition"].ToString().Trim() != otherRow["definition"].ToString().Trim()) //{ // builder.AppendFormat("View Difference {0}", thisRow["name"].ToString()); // builder.AppendLine(); //} } helper.Close(); otherHelper.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void CompareTable(bool isView, List <TableType> thisObjs, List <TableType> otherObjs, StringBuilder builder) { var objType = isView ? "视图" : "表"; List <string> newTables = new List <string>(); //提示db新增加的表 foreach (var table in thisObjs) { if (otherObjs.FindTable(table.Name) == null) { newTables.Add(table.Name); } } var index = 1; newTables.Sort(); foreach (var table in newTables) { builder.AppendLine( string.Format("数据库中新增的{0}:{1} {2}", objType, index++, table) ); } List <string> notExsitedTables = new List <string>(); //提示db不存在的表 foreach (var other in otherObjs) { if (thisObjs.FindTable(other.Name) == null) { notExsitedTables.Add(other.Name); } } index = 1; notExsitedTables.Sort(); foreach (var table in notExsitedTables) { builder.AppendLine( string.Format("数据库中不存在的{0}:{1} {2}", objType, index++, table) ); } //提示db更新的表 var sharedTable = new List <TableType>(); foreach (var table in thisObjs) { if (newTables.Contains(table.Name) || notExsitedTables.Contains(table.Name)) { continue; } sharedTable.Add(table); } sharedTable = sharedTable.SortAsc(); foreach (var table in sharedTable) { //var entityType = table.Value; var dbTable = table; var otherTable = otherObjs.FindTable(table.Name); //db多的字段 var newFields = new List <ColumnType>(); foreach (var column in dbTable.ColumnSet.Columns.ToList().SortAsc()) { if (otherTable.FindColumn(column.Name) == null) { newFields.Add(column); } } index = 0; foreach (var f in newFields) { builder.AppendLine( string.Format("数据{0}{1}新增的字段: {2} {3}", objType, table.Name, f.Name, f.DbType) ); } //db少的字段 var noFields = new List <ColumnType>(); foreach (var otherColumn in otherTable.ColumnSet.Columns.ToList().SortAsc()) { if (dbTable.FindColumn(otherColumn.Name) == null) { noFields.Add(otherColumn); } } foreach (var f in noFields) { builder.AppendLine( string.Format("数据{0}{1}缺少的字段: {2} {3}", objType, table.Name, f.Name, f.DbType) ); } //db更新的字段 foreach (var column in dbTable.ColumnSet.Columns.ToList().SortAsc()) { var otherColumn = otherTable.FindColumn(column.Name); if (otherColumn == null) { continue; } if (column.AllowDBNull != otherColumn.AllowDBNull) { builder.AppendLine( string.Format("数据{0}{1}更改的字段: {2} {3}", objType, table.Name, column.Name, column.AllowDBNull ? "数据库为可空" : "数据库为非空") ); } if (column.DbType != otherColumn.DbType) { builder.AppendLine( string.Format("数据{0}{1}更改的字段: {2} {3},数据库类型为{4} VS {5}", objType, table.Name, column.Name, column.SystemType, column.DbType, otherColumn.DbType)); } } } }
private void CompareTable(bool isView,List<TableType> thisObjs, List<TableType> otherObjs,StringBuilder builder) { var objType = isView ? "视图" : "表"; List<string> newTables = new List<string>(); //提示db新增加的表 foreach (var table in thisObjs) { if (otherObjs.FindTable(table.Name) == null) { newTables.Add(table.Name); } } var index = 1; newTables.Sort(); foreach (var table in newTables) { builder.AppendLine( string.Format("数据库中新增的{0}:{1} {2}",objType, index++, table) ); } List<string> notExsitedTables = new List<string>(); //提示db不存在的表 foreach (var other in otherObjs) { if (thisObjs.FindTable(other.Name) == null) { notExsitedTables.Add(other.Name); } } index = 1; notExsitedTables.Sort(); foreach (var table in notExsitedTables) { builder.AppendLine( string.Format("数据库中不存在的{0}:{1} {2}",objType, index++, table) ); } //提示db更新的表 var sharedTable = new List<TableType>(); foreach (var table in thisObjs) { if (newTables.Contains(table.Name) || notExsitedTables.Contains(table.Name)) continue; sharedTable.Add(table); } sharedTable= sharedTable.SortAsc(); foreach (var table in sharedTable) { //var entityType = table.Value; var dbTable = table; var otherTable = otherObjs.FindTable(table.Name); //db多的字段 var newFields = new List<ColumnType>(); foreach (var column in dbTable.ColumnSet.Columns.ToList().SortAsc()) { if (otherTable.FindColumn(column.Name) == null) newFields.Add(column); } index = 0; foreach (var f in newFields) { builder.AppendLine( string.Format("数据{0}{1}新增的字段: {2} {3}", objType, table.Name, f.Name, f.DbType) ); } //db少的字段 var noFields = new List<ColumnType>(); foreach (var otherColumn in otherTable.ColumnSet.Columns.ToList().SortAsc()) { if (dbTable.FindColumn(otherColumn.Name) == null) { noFields.Add(otherColumn); } } foreach (var f in noFields) { builder.AppendLine( string.Format("数据{0}{1}缺少的字段: {2} {3}", objType, table.Name, f.Name, f.DbType) ); } //db更新的字段 foreach (var column in dbTable.ColumnSet.Columns.ToList().SortAsc()) { var otherColumn = otherTable.FindColumn(column.Name); if (otherColumn == null) continue; if (column.AllowDBNull != otherColumn.AllowDBNull) { builder.AppendLine( string.Format("数据{0}{1}更改的字段: {2} {3}", objType, table.Name, column.Name, column.AllowDBNull ? "数据库为可空" : "数据库为非空") ); } if (column.DbType != otherColumn.DbType) { builder.AppendLine( string.Format("数据{0}{1}更改的字段: {2} {3},数据库类型为{4} VS {5}", objType, table.Name, column.Name, column.SystemType, column.DbType, otherColumn.DbType)); } } } }