protected void Application_Start()
{
DataBaseSchema.CreateTables();
DataBaseSchema.FillTables();
AreaRegistration.RegisterAllAreas();
FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
RouteConfig.RegisterRoutes(RouteTable.Routes);
BundleConfig.RegisterBundles(BundleTable.Bundles);
}
private IReadOnlyList <DataBaseSchema> BuildResult(SqlConnection cnx, List <DataBaseObject> dataBaseObjects)
{
var result = new List <DataBaseSchema>();
// Read Schema
foreach (var schema in dataBaseObjects.Select(c => c.Schema).Distinct())
{
var dbSchema = new DataBaseSchema(schema);
// Read Table
foreach (var table in dataBaseObjects.Where(c => c.Schema == schema).Select(c => c.TableName).Distinct())
{
var dbTable = new DataBaseTable(dbSchema, table);
// Read Columns
foreach (var column in dataBaseObjects.Where(c => c.Schema == schema && c.TableName == table))
{
dbTable.Columns.Add(new DataBaseColumn(dbSchema, dbTable, column.ColumnName)
{
ColumnComputed = column.ColumnComputed,
Description = column.Description,
Nullable = column.Nullable,
PK = column.PK,
Size = column.Size,
Type = column.Type,
TypeSql = column.TypeSql
});
}
// Read Relationships
foreach (var relationship in ReadSqlRelationships(cnx, dbTable))
{
dbTable.RelationShips.Add(new DataBaseRelationShip
{
ColumnFK = relationship.ColumnFK,
ColumnPK = relationship.ColumnPK,
Nullable = relationship.Nullable,
SchemaFK = relationship.SchemaFK,
SchemaPK = relationship.SchemaPK,
TableFK = relationship.TableFK,
TablePK = relationship.TablePK,
Type = relationship.Type
});
}
dbSchema.Tables.Add(dbTable);
}
result.Add(dbSchema);
}
return(result.AsReadOnly());
}
public override IList<DataBaseSchema> GetDataBases(System.Data.Common.DbConnectionStringBuilder connectionstr)
{
IList<DataBaseSchema> list = null;
using (MySqlConnection connection = new MySqlConnection(connectionstr.ConnectionString))
{
connection.Open();
DataTable databases = connection.GetSchema(SqlClientMetaDataCollectionNames.Databases, connectionstr.DataBase());
if (databases != null && databases.Rows.Count > 0)
{
list = new List<DataBaseSchema>();
foreach (DataRow database in databases.Rows)
{
string name = (string)database["database_name"];
DataBaseSchema db = new DataBaseSchema(name, name);
list.Add(db);
}
}
}
return list;
}
public override IList <DataBaseSchema> GetDataBases(System.Data.Common.DbConnectionStringBuilder connectionstr)
{
IList <DataBaseSchema> list = null;
using (MySqlConnection connection = new MySqlConnection(connectionstr.ConnectionString))
{
connection.Open();
DataTable databases = connection.GetSchema(SqlClientMetaDataCollectionNames.Databases, connectionstr.DataBase());
if (databases != null && databases.Rows.Count > 0)
{
list = new List <DataBaseSchema>();
foreach (DataRow database in databases.Rows)
{
string name = (string)database["database_name"];
DataBaseSchema db = new DataBaseSchema(name, name);
list.Add(db);
}
}
}
return(list);
}
private void btnCompared_Click(object sender, EventArgs e)
{
string dirName = "";
//連線資訊
string connStringInfoFilePath = "";
string impactFilePath = "diffFile.txt";
string recorderDirPath = RecorderDir;
try
{
for (int i = 0; i < chkListDir.Items.Count; i++)
{
if (chkListDir.GetItemChecked(i))
{
dirName = chkListDir.Items[i].ToString();
Logger.Info("準備比對::" + dirName);
connStringInfoFilePath = Path.Combine(CheckerDir, dirName, ConnectStringInfoFileName);
Logger.Info("連線資訊檔案位置::" + connStringInfoFilePath);
if (!File.Exists(connStringInfoFilePath))
{
Logger.Info("無連線資訊檔案");
continue;
}
using (StreamReader sr = new StreamReader(connStringInfoFilePath))
{
ConnString = sr.ReadLine();
}
recorderDirPath = Path.Combine(recorderDirPath, dirName);
var dir = (from d in new System.IO.DirectoryInfo(recorderDirPath).GetDirectories()
select d).ToList().OrderByDescending(d => d.CreationTime).Take(1).SingleOrDefault();
while (dir != null)
{
recorderDirPath = Path.Combine(recorderDirPath, dir.Name);
dir = (from d in new System.IO.DirectoryInfo(recorderDirPath).GetDirectories()
select d).ToList().OrderByDescending(d => d.CreationTime).Take(1).SingleOrDefault();
}
impactFilePath = Path.Combine(Path.Combine(CheckerDir, dirName, impactFilePath));
CreateFile(impactFilePath);
Logger.Info("比對位置::" + recorderDirPath);
dt_TableName = DBManager.ConnDB(ConnString, SQLManager.Select.GetAllTableName());
using (StreamWriter sw = new StreamWriter(impactFilePath))
{
//比對Table是否一致
//舊檔案名稱
List <string> oldTableName = new List <string>();
//目前檔案名稱
List <string> currentTableName = new List <string>();
string[] tmp_files = System.IO.Directory.GetFiles(recorderDirPath);
foreach (var o in tmp_files)
{
oldTableName.Add(o.Replace(recorderDirPath + "\\", "").Replace(".txt", ""));
}
for (int j = 0; j < dt_TableName.Rows.Count; j++)
{
currentTableName.Add(dt_TableName.Rows[j]["TABLE_NAME"].ToString());
}
var diff_delete_list = oldTableName.Except(currentTableName);
foreach (var item in diff_delete_list)
{
sw.WriteLine("已刪除或變更Table:" + item);
}
var diff_add_list = currentTableName.Except(oldTableName);
foreach (var item in diff_add_list)
{
sw.WriteLine("新增Table:" + item);
}
var intersect_tablename = oldTableName.Intersect(currentTableName);
//比對PK是否一致
//比對Schema是否一致
//比對資料是否一致
foreach (var tableName in intersect_tablename)
{
DataTable dt_TablePK = new DataTable();
DataTable dt_TableSchema = new DataTable();
DataTable dt_data = new DataTable();
using (StreamReader sr = new StreamReader(Path.Combine(recorderDirPath, tableName + ".txt")))
{
sw.WriteLine();
sw.WriteLine("開始處理:" + tableName);
#region PK部分
dt_TablePK = DBManager.ConnDB(ConnString, SQLManager.Select.GetTablePK(tableName));
List <string> currentPK = new List <string>();
for (int j = 0; j < dt_TablePK.Rows.Count; j++)
{
currentPK.Add(dt_TablePK.Rows[j]["COLUMN_NAME"].ToString());
}
string line = "";
List <string> old_key_list = new List <string>();
while ((line = sr.ReadLine()) != null)
{
if (line == "TableKey:")
{
continue;
}
if (line == "TableSchema:")
{
break;
}
old_key_list.Add(line);
}
foreach (var item in old_key_list.Except(currentPK))
{
sw.WriteLine("已刪除PK:" + item);
}
foreach (var item in currentPK.Except(old_key_list))
{
sw.WriteLine("已新增PK:" + item);
}
#endregion
#region Scahema部分
dt_TableSchema = DBManager.ConnDB(ConnString, SQLManager.Select.GetTableSchema(tableName));
List <string> currentScahema = new List <string>();
for (int j = 0; j < dt_TableSchema.Rows.Count; j++)
{
currentScahema.Add(dt_TableSchema.Rows[j]["COLUMN_NAME"].ToString());
}
List <DataBaseSchema> oldSchema_List = new List <DataBaseSchema>();
//當Key查詢用
List <string> oldSchema_SearchKey = new List <string>();
while ((line = sr.ReadLine()) != null)
{
if (line == "==冷資料記錄，請買錸德(2349)==")
{
break;
}
if (line.StartsWith("COLUMN_NAME:"))
{
DataBaseSchema oldSchemaItem = new DataBaseSchema();
line = line.Remove(0, "COLUMN_NAME:".Length);
oldSchemaItem.COLUMN_NAME = line;
oldSchema_SearchKey.Add(line);
line = sr.ReadLine();
line = line.Remove(0, "ORDINAL_POSITION:".Length);
oldSchemaItem.ORDINAL_POSITION = line;
line = sr.ReadLine();
line = line.Remove(0, "DATA_TYPE:".Length);
oldSchemaItem.DATA_TYPE = line;
line = sr.ReadLine();
line = line.Remove(0, "CHARACTER_MAXIMUM_LENGTH:".Length);
oldSchemaItem.CHARACTER_MAXIMUM_LENGTH = line;
oldSchema_List.Add(oldSchemaItem);
}
}
foreach (var item in oldSchema_SearchKey.Except(currentScahema))
{
sw.WriteLine("已刪除Schema:" + item);
}
foreach (var item in currentScahema.Except(oldSchema_SearchKey))
{
sw.WriteLine("已新增Schema:" + item);
}
var intersectSchema = currentScahema.Intersect(oldSchema_SearchKey);
for (int j = 0; j < dt_TableSchema.Rows.Count; j++)
{
if (intersectSchema.Contains(dt_TableSchema.Rows[j]["COLUMN_NAME"]))
{
sw.WriteLine("檢查Schema:" + dt_TableSchema.Rows[j]["COLUMN_NAME"]);
var targetSchrma = oldSchema_List.Single(o => o.COLUMN_NAME == dt_TableSchema.Rows[j]["COLUMN_NAME"].ToString());
if (targetSchrma.DATA_TYPE != dt_TableSchema.Rows[j]["DATA_TYPE"].ToString())
{
sw.WriteLine("DATA_TYPE已變更::" + targetSchrma.DATA_TYPE + "==>" + dt_TableSchema.Rows[j]["DATA_TYPE"].ToString());
}
if (targetSchrma.ORDINAL_POSITION != dt_TableSchema.Rows[j]["ORDINAL_POSITION"].ToString())
{
sw.WriteLine("ORDINAL_POSITION已變更::" + targetSchrma.ORDINAL_POSITION + "==>" + dt_TableSchema.Rows[j]["ORDINAL_POSITION"].ToString());
}
if (targetSchrma.CHARACTER_MAXIMUM_LENGTH != dt_TableSchema.Rows[j]["CHARACTER_MAXIMUM_LENGTH"].ToString())
{
sw.WriteLine("CHARACTER_MAXIMUM_LENGTH已變更::" + targetSchrma.CHARACTER_MAXIMUM_LENGTH + "==>" + dt_TableSchema.Rows[j]["CHARACTER_MAXIMUM_LENGTH"].ToString());
}
}
}
#endregion
#region 資料部分
//透過 intersectSchema 來查
dt_data = DBManager.ConnDB(ConnString, SQLManager.Select.GetTableData(tableName));
string where = "";
bool CheckData = false;
while ((line = sr.ReadLine()) != null)
{
CheckData = true;
foreach (var colName in intersectSchema)
{
var targetSchrma = oldSchema_List.Single(o => o.COLUMN_NAME == colName);
var datetimeFormat = targetSchrma.DATA_TYPE == "datetime" ? true : false;
if (line.StartsWith(colName))
{
string value = line.Remove(0, (colName + ":").Length);
if (!string.IsNullOrEmpty(value) && !datetimeFormat)
{
if (string.IsNullOrEmpty(where))
{
where = colName + "=" + value;
}
else
{
where += " and " + colName + "=" + "'" + value + "'";
}
}
}
line = sr.ReadLine();
if (string.IsNullOrEmpty(line))
{
continue;
}
}
DataRow[] dr = dt_data.Select(where);
if (dr.Length > 1)
{
sw.WriteLine("使用下列條件查到兩筆相同資料：" + where);
}
if (dr == null || dr.Length == 0)
{
sw.WriteLine("資料已被刪除或修改：" + where);
}
foreach (var o in dr)
{
dt_data.Rows.Remove(o);
}
where = "";
}
if (CheckData)
{
for (int j = 0; j < dt_data.Rows.Count; j++)
{
sw.WriteLine("以下資料可能為新增資料或修改資料：");
for (int k = 0; k < dt_data.Columns.Count; k++)
{
string columnName = dt_data.Columns[k].ColumnName;
sw.WriteLine(string.Format("{0}:{1}", columnName, dt_data.Rows[j][k].ToString()));
}
sw.WriteLine();
}
}
#endregion
}
}
}
}
}
label6.Text = "比對完成";
}
catch (Exception ex)
{
Logger.Error(System.Reflection.MethodBase.GetCurrentMethod().Name + ex.ToString());
MessageBox.Show(ex.Message);
label6.Text = "失敗";
}
}
/// <summary>
/// Returns the full schema of the database including tables, indexes, foreign keys, etc.
/// </summary>
/// <remarks>
/// It's very slow for large databases
/// </remarks>
public static DataBaseSchema Load(DataBase database, string schemaProvider)
{
DataBaseSchema schema = new DataBaseSchema();
DatabaseSchema schemaReader;
using (var dbReader = new DatabaseSchemaReader.DatabaseReader(database.ConnectionString, schemaProvider))
{
dbReader.AllTables();
dbReader.AllViews();
try
{
dbReader.AllStoredProcedures();
}
catch { }
try
{
dbReader.AllUsers();
}
catch { }
schemaReader = dbReader.DatabaseSchema;
}
foreach (DatabaseTable dbt in schemaReader.Tables)
{
if (dbt.PrimaryKeyColumn == null)
{
continue;
}
dbt.PrimaryKeyColumn.AddIdentity();
var table = new Table()
{
Name = dbt.Name,
DataBase = schema,
IdentityIncrement = dbt.PrimaryKeyColumn.IdentityDefinition.IdentityIncrement,
IdentitySeed = dbt.PrimaryKeyColumn.IdentityDefinition.IdentitySeed,
};
schema.Tables.Add(table);
}
foreach (DatabaseTable dbt in schemaReader.Tables)
{
if (dbt.PrimaryKeyColumn == null)
{
continue;
}
var table = schema[dbt.Name];
foreach (DatabaseColumn dbc in dbt.Columns)
{
Column column = new Column()
{
Name = dbc.Name,
Table = table,
Description = dbc.Description,
IsNullable = dbc.Nullable,
IsAutoNumber = dbc.IsAutoNumber,
ComputedDefinition = dbc.ComputedDefinition,
DefaultValue = dbc.DefaultValue,
IsPrimaryKey = dbc.IsPrimaryKey,
Length = (uint?)dbc.Length,
Ordinal = dbc.Ordinal,
Precision = dbc.Precision,
Scale = dbc.Scale,
};
if (dbc.DataType != null)
{
column.DbType = DbTypeMapper.Parse(dbc.DataType.GetNetType());
}
else
{
if (dbc.DbDataType.StartsWith("varchar"))
{
column.DbType = DbType.AnsiString;
}
else if (dbc.DbDataType.StartsWith("int"))
{
column.DbType = DbType.Int32;
}
else if (dbc.DbDataType.StartsWith("decimal"))
{
column.DbType = DbType.Decimal;
}
else if (dbc.DbDataType.StartsWith("datetime"))
{
column.DbType = DbType.DateTime;
}
else if (dbc.DbDataType.StartsWith("money"))
{
column.DbType = DbType.Currency;
}
else if (dbc.DbDataType.StartsWith("char"))
{
column.DbType = DbType.AnsiStringFixedLength;
}
else if (dbc.DbDataType.StartsWith("text"))
{
column.DbType = DbType.AnsiString;
}
}
table.Columns.Add(column);
}
foreach (DatabaseIndex dbi in dbt.Indexes)
{
Index index = new Index()
{
Name = dbi.Name,
Table = table,
Direction = SortDirection.Ascending,
Unique = dbi.IsUnique,
};
foreach (DatabaseColumn dbc in dbi.Columns)
{
index.Columns.Add(table[dbc.Name]);
}
table.Indexes.Add(index);
}
foreach (DatabaseTrigger dbtr in dbt.Triggers)
{
DataBaseOperation operation = DataBaseOperation.Insert;
Enum.TryParse <DataBaseOperation>(dbtr.TriggerEvent, true, out operation);
Trigger trigger = new Trigger()
{
TriggerBody = dbtr.TriggerBody,
TriggerEvent = operation,
Table = table,
};
table.Triggers.Add(trigger);
}
foreach (DatabaseConstraint dbcons in dbt.CheckConstraints)
{
if (dbcons.ConstraintType == ConstraintType.Check)
{
CheckConstraint constraint = new CheckConstraint()
{
Expression = dbcons.Expression,
Table = table,
};
table.CheckConstraints.Add(constraint);
}
else if (dbcons.ConstraintType == ConstraintType.ForeignKey)
{
ForeignKey foreignKey = new ForeignKey()
{
Name = dbcons.Name,
DeleteAction = schema.ParseConstraintAction(dbcons.DeleteRule),
UpdateAction = schema.ParseConstraintAction(dbcons.UpdateRule),
RemoteTable = schema[dbcons.RefersToTable],
Table = table,
};
var referencedColumns = dbcons.ReferencedColumns(schemaReader).ToArray();
for (int i = 0; i < dbcons.Columns.Count; i++)
{
foreignKey.Columns.Add(new Tuple <Column, Column>(table[dbcons.Columns[i]], foreignKey.RemoteTable[referencedColumns[i]]));
}
table.ForeignKeys.Add(foreignKey);
}
}
}
foreach (DatabaseView dbv in schemaReader.Views)
{
View view = new View()
{
Name = dbv.Name,
Command = dbv.Sql,
Description = dbv.Description,
DataBase = schema,
};
schema.Views.Add(view);
}
foreach (DatabaseUser dbu in schemaReader.Users)
{
User user = new User()
{
Name = dbu.Name,
DataBase = schema,
};
schema.Users.Add(user);
}
return(schema);
}
