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));
}
}
}
}
