Example #1
0
        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);
            }
        }
Example #2
0
        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));
                        }

                    }

                }
        }