public void AddColumn(string tableName, DbColumnInfo columnInfo) { #region QUERY const string query = "ALTER TABLE {0} ADD {1}"; #endregion StringBuilder inserting = new StringBuilder(""); inserting.Append(columnInfo.Name); inserting.Append(" "); inserting.Append(columnInfo.Type); inserting.Append(" "); if (!columnInfo.Nullable) { inserting.Append("NOT NULL "); } if (columnInfo.PrimaryKey) { inserting.Append("PRIMARY KEY "); } if (columnInfo.Reference != null && columnInfo.Reference != "") { DbColumnReference reference = DbColumnReference.TryParse(columnInfo.Reference); inserting.Append(string.Format("REFERENCES {0}({1})", reference.TableName, reference.ColumnName)); inserting.Append(" "); } while (inserting.Length > 0 && inserting[inserting.Length - 1] == ' ') { inserting.Remove(inserting.Length - 1, 1); } ExecuteCommandReader(string.Format(query, tableName, inserting.ToString())); }
public DbColumnReference GetColumnReference(DbColumnReference column) { #region QUERY const string query = "SELECT c.owner, c_pk.table_name r_table_name, b.column_name r_column_name " + "FROM user_cons_columns a JOIN user_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name JOIN user_constraints c_pk ON c.r_owner = c_pk.owner " + "AND c.r_constraint_name = c_pk.constraint_name " + "JOIN user_cons_columns b ON C_PK.owner = b.owner AND C_PK.CONSTRAINT_NAME = b.constraint_name " + "WHERE c.constraint_type = 'R' AND c.owner = \'{0}\' AND a.table_name = \'{1}\' AND a.column_name = \'{2}\'"; #endregion using (var cmd = _databaseConnection.CreateCommand()) { cmd.CommandText = String.Format(query, column.Owner.ToUpper(), column.TableName.ToUpper(), column.ColumnName.ToUpper()); using (DbDataReader reader = cmd.ExecuteReader()) { if (reader == null) { return(null); } if (!reader.Read()) { return(null); } DbColumnReference res = new DbColumnReference(); res.Owner = reader.GetString(0); res.TableName = reader.GetString(1); res.ColumnName = reader.GetString(2); return(res); } } }
public string CreateString() { StringBuilder res = new StringBuilder(""); res.Append(Name); res.Append(" "); res.Append(Type); res.Append(" "); if (Reference != "" && Reference != null) { DbColumnReference reference = DbColumnReference.TryParse(Reference); res.Append(string.Format("REFERENCES {0}({1})", reference.TableName, reference.ColumnName)); res.Append(" "); } if (!Nullable) { res.Append("NOT NULL "); } return(res.ToString()); }
public IDictionary <string, DbColumnReference> GetTableReferences(string tableName) { if (tableName == "" || tableName == null) { return(new Dictionary <string, DbColumnReference>()); } #region QUERY const string query = "SELECT c.owner, c_pk.table_name r_table_name, b.column_name r_column_name " + "FROM user_cons_columns a JOIN user_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name JOIN user_constraints c_pk ON c.r_owner = c_pk.owner " + "AND c.r_constraint_name = c_pk.constraint_name " + "JOIN user_cons_columns b ON C_PK.owner = b.owner AND C_PK.CONSTRAINT_NAME = b.constraint_name " + "WHERE c.constraint_type = 'R' AND a.table_name = \'{0}\'"; #endregion IDictionary <string, DbColumnReference> res = new Dictionary <string, DbColumnReference>(); using (var cmd = _databaseConnection.CreateCommand()) { cmd.CommandText = String.Format(query, tableName.ToUpper()); using (DbDataReader reader = cmd.ExecuteReader()) { if (reader == null) { return(null); } while (reader.Read()) { DbColumnReference cur = new DbColumnReference(); cur.Owner = reader.GetString(0); cur.TableName = reader.GetString(1); cur.ColumnName = reader.GetString(2); res[cur.ColumnName] = cur; } return(res); } } return(res); }
private static void SetDbForeignKeys(DbModel dbModel, DatabaseType type, DbConnection conn, int connectionId, string databaseName) { var columnLookup = dbModel.Tables.SelectMany(t => t.Columns) .Select(c => c as Models.DbColumn) .ToDictionary(c => new DbColumnReference(c.Table.Schema, c.Table.Name, c.Name)); DbCommand cmd = null; switch (type) { case DatabaseType.MySQL: { string sql = "SELECT NULL AS CHILD_TABLE_SCHEMA, " + " C.TABLE_NAME AS CHILD_TABLE_NAME, " + " C.COLUMN_NAME AS CHILD_COLUMN_NAME, " + " NULL AS PARENT_TABLE_SCHEMA, " + " P.TABLE_NAME AS PARENT_TABLE_NAME, " + " P.COLUMN_NAME AS PARENT_COLUMN_NAME " + "FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC " + "INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS C " + " ON C.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG " + " AND C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA " + " AND C.TABLE_NAME = RC.TABLE_NAME " + " AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME " + "INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS P " + " ON P.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG " + " AND P.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA " + " AND P.TABLE_NAME = RC.REFERENCED_TABLE_NAME " + " AND P.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME " + " AND P.ORDINAL_POSITION = C.ORDINAL_POSITION " + "WHERE C.TABLE_SCHEMA = @schema;"; cmd = CreateCommand(type, conn, sql); cmd.Parameters.Add(new MySqlParameter("@schema", databaseName)); } break; case DatabaseType.PostgreSQL: { string sql = "SELECT C.TABLE_SCHEMA AS CHILD_TABLE_SCHEMA, " + " C.TABLE_NAME AS CHILD_TABLE_NAME, " + " C.COLUMN_NAME AS CHILD_COLUMN_NAME, " + " P.TABLE_SCHEMA AS PARENT_TABLE_SCHEMA, " + " P.TABLE_NAME AS PARENT_TABLE_NAME, " + " P.COLUMN_NAME AS PARENT_COLUMN_NAME " + "FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC " + "INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS C " + " ON C.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG " + " AND C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA " + " AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME " + "INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS P " + " ON P.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG " + " AND P.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA " + " AND P.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME " + " AND P.ORDINAL_POSITION = C.ORDINAL_POSITION " + "WHERE C.TABLE_SCHEMA <> 'sys' AND P.TABLE_SCHEMA <> 'sys';"; cmd = CreateCommand(type, conn, sql); } break; case DatabaseType.SQLServer: { string sql = "SELECT C.TABLE_SCHEMA AS CHILD_TABLE_SCHEMA, " + " C.TABLE_NAME AS CHILD_TABLE_NAME, " + " C.COLUMN_NAME AS CHILD_COLUMN_NAME, " + " P.TABLE_SCHEMA AS PARENT_TABLE_SCHEMA, " + " P.TABLE_NAME AS PARENT_TABLE_NAME, " + " P.COLUMN_NAME AS PARENT_COLUMN_NAME " + "FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC " + "INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS C " + " ON C.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG " + " AND C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA " + " AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME " + "INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS P " + " ON P.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG " + " AND P.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA " + " AND P.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME " + " AND P.ORDINAL_POSITION = C.ORDINAL_POSITION " + "WHERE C.TABLE_SCHEMA <> 'sys' AND P.TABLE_SCHEMA <> 'sys';"; cmd = CreateCommand(type, conn, sql); } break; } var fkData = new List <Tuple <DbColumnReference, DbColumnReference> >(); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { var child = new DbColumnReference(reader.IsDBNull(0) ? (string)null : reader.GetString(0), reader.IsDBNull(1) ? (string)null : reader.GetString(1), reader.IsDBNull(2) ? (string)null : reader.GetString(2)); var parent = new DbColumnReference(reader.IsDBNull(3) ? (string)null : reader.GetString(3), reader.IsDBNull(4) ? (string)null : reader.GetString(4), reader.IsDBNull(5) ? (string)null : reader.GetString(5)); fkData.Add(new Tuple <DbColumnReference, DbColumnReference>(child, parent)); } } foreach (var fk in fkData) { if (columnLookup.ContainsKey(fk.Item1) && columnLookup.ContainsKey(fk.Item2)) { var childColumn = columnLookup[fk.Item1]; childColumn.Parent = columnLookup[fk.Item2]; } } foreach (var table in dbModel.Tables) { foreach (var column in table.Columns.Select(c => c as Models.DbColumn)) { DbTable dbTable = null; if (column.IsPrimaryKey == false && column.Parent == null) { dbTable = dbModel.Tables.FirstOrDefault(t => t.Schema == table.Schema && ((t.Name.ToLower() + "_id") == column.Name.ToLower()) || ((t.Name.ToLower() + "id") == column.Name.ToLower())); if (dbTable != null) { column.Parent = dbTable.Columns[0] as Models.DbColumn; } } } } }