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