public Database Read() { Database database = new Database(this._dbSetting.Database); var conn = this._db.Connection; try { conn.Open(); this.LoadAllTables(database); this.LoadAllColumns(database); this.LoadAllConstraints(database); } catch (DbException) { database.Removed = true; } finally { conn.Close(); } return database; }
protected override void LoadAllColumns(Database database) { foreach (Table table in database.Tables) { using (var columnsReader = this.Db.QueryDataReader(@" SELECT * FROM user_tab_columns WHERE TABLE_NAME = {0} ", table.Name)) { while (columnsReader.Read()) { string columnName = columnsReader["COLUMN_NAME"].ToString(); string sqlType = columnsReader["DATA_TYPE"].ToString().ToLower(); if (sqlType == "number") { var dataScale = columnsReader["DATA_SCALE"].ToString(); if (dataScale == "0") { sqlType = "integer"; } } DbType dbType = OracleDbTypeHelper.ConvertFromOracleTypeString(sqlType); Column column = new Column(dbType, null, columnName, table); column.IsRequired = columnsReader["NULLABLE"].ToString() == "N"; table.Columns.Add(column); } table.SortColumns(); } } }
internal DatabaseChanges(Database oldDatabase, DestinationDatabase newDatabase, IList<TableChanges> tableChanges) { this.OldDatabase = oldDatabase; this.NewDatabase = newDatabase; this.ChangeType = ChangeType.UnChanged; if (!oldDatabase.Removed || !newDatabase.Removed) { if (oldDatabase.Removed) { this.ChangeType = ChangeType.Added; return; } if (newDatabase.Removed) { this.ChangeType = ChangeType.Removed; return; } if (tableChanges != null && tableChanges.Count > 0) { this.ChangeType = ChangeType.Modified; this._allRecords = tableChanges; } } }
public Table(string name, Database dataBase) { if (string.IsNullOrWhiteSpace(name)) throw new ArgumentNullException("name"); if (dataBase == null) throw new ArgumentNullException("dataBase"); this.Columns = new List<Column>(); this.Name = name; this.DataBase = dataBase; }
protected override void LoadAllTables(Database database) { using (var reader = this.Db.QueryDataReader(@"select * from user_tables")) { while (reader.Read()) { string tableName = reader["TABLE_NAME"].ToString(); Table table = new Table(tableName, database); database.Tables.Add(table); } } }
/// <summary> /// 计算出两个数据库元数据的所有表差别 /// </summary> /// <param name="oldDatabase">旧数据库</param> /// <param name="newDatabase">新数据库</param> /// <returns></returns> public static DatabaseChanges Distinguish(Database oldDatabase, DestinationDatabase newDatabase) { if (!oldDatabase.Removed) { oldDatabase.OrderByRelations(); } if (!newDatabase.Removed) { newDatabase.OrderByRelations(); } List<TableChanges> result = new List<TableChanges>(); if (!oldDatabase.Removed && !newDatabase.Removed) { //先找出所有被删除的表 foreach (var oldTable in oldDatabase.Tables.Reverse()) { if (newDatabase.FindTable(oldTable.Name) == null && !newDatabase.IsIgnored(oldTable.Name)) { result.Add(new TableChanges(oldTable, null, ChangeType.Removed)); } } foreach (var newTable in newDatabase.Tables) { if (!newDatabase.IsIgnored(newTable.Name)) { var oldTable = oldDatabase.FindTable(newTable.Name); //如果没有找到旧表,说明这个表是新加的。 if (oldTable == null) { result.Add(new TableChanges(null, newTable, ChangeType.Added)); } else { //即不是新表,也不是旧表,计算两个表的区别 TableChanges record = Distinguish(oldTable, newTable); //如果有区别,则记录下来 if (record != null) { result.Add(record); } } } } } return new DatabaseChanges(oldDatabase, newDatabase, result); }
/// <summary> /// 加载每个表的所有列 /// </summary> /// <param name="database"></param> protected override void LoadAllColumns(Database database) { foreach (Table table in database.Tables) { using (var columnsReader = this.Db.QueryDataReader(@" SELECT C.COLUMN_NAME, C.IS_NULLABLE, C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS C WHERE C.TABLE_NAME = {0} ", table.Name)) { while (columnsReader.Read()) { string columnName = columnsReader["COLUMN_NAME"].ToString(); string sqlType = columnsReader["DATA_TYPE"].ToString(); //不再读取 Length //string length = null; //var lengthObj = columnsReader["CHARACTER_MAXIMUM_LENGTH"]; //if (lengthObj != null && !DBNull.Value.Equals(lengthObj) && lengthObj.ToString() != "-1") //{ // length = lengthObj.ToString(); //} DbType dbType = SqlDbTypeHelper.ConvertFromSQLTypeString(sqlType); Column column = new Column(dbType, null, columnName, table); column.IsRequired = string.Compare(columnsReader["IS_NULLABLE"].ToString(), "no", true) == 0; table.Columns.Add(column); } table.SortColumns(); } } this.LoadIsIdentity(database); }
/// <summary> /// 添加所有表 /// </summary> /// <param name="database"></param> protected override void LoadAllTables(Database database) { using (var reader = this.Db.QueryDataReader(@"select * from INFORMATION_SCHEMA.TABLES")) { while (reader.Read()) { string tableName = reader["TABLE_NAME"].ToString(); string tableType = reader["TABLE_TYPE"].ToString().ToLower(); //SqlServer 中是 "BASE TABLE",同时还会把一些系统表也查询出来,例如:sysdiagrams //SQLCE 中是 "TABLE" if (tableType.Contains("table") && !tableName.StartsWith("sys")) { //string schemaName = reader["SCHEMA_NAME"].ToString(); Table table = new Table(tableName, database); database.Tables.Add(table); } } } }
private void LoadIsIdentity(Database database) { var identities = this.ReadAllIdentities(); foreach (var identity in identities) { var table = database.FindTable(identity.TableName); if (table != null) { var column = table.FindColumn(identity.ColumnName); if (column != null) { column.IsIdentity = true; } } } }
/// <summary> /// 加载每个表的所有列 /// </summary> /// <param name="database"></param> protected abstract void LoadAllColumns(Database database);
/// <summary> /// 加载外键 /// </summary> /// <param name="database"></param> private void LoadAllConstraints(Database database) { List<Constraint> allConstrains = this.ReadAllConstrains(); foreach (var table in database.Tables) { foreach (var column in table.Columns) { this.DealColumnConstraints(column, allConstrains); } } }
/// <summary> /// 添加所有表 /// </summary> /// <param name="database"></param> protected abstract void LoadAllTables(Database database);