/// <summary> /// When implemented in a child class, loads a generic list of <see cref="ORMSolutions.ORMArchitect.DatabaseImport.DcilReferenceConstraint"/> objects (representing Foreign Keys) for the specified MySQL 5.0 Schema and Table /// </summary> /// <param name="schemaName">Name of the MySQL 5.0 Schema for which the given Table resides in</param> /// <param name="tableName">Name of the Table from which to load the Indexes</param> /// <returns>Generic list of <see cref="ORMSolutions.ORMArchitect.DatabaseImport.DcilReferenceConstraint"/> objects (representing Foreign Keys) for the specified MySQL 5.0 Schema and Table</returns> public IList <DcilReferenceConstraint> LoadForeignKeys(string schemaName, string tableName) { IList <DcilReferenceConstraint> constraints = new List <DcilReferenceConstraint>(); bool opened = false; try { if (_conn.State != ConnectionState.Open) { _conn.Open(); opened = true; } IDbCommand cmd = _conn.CreateCommand(); cmd.CommandType = CommandType.Text; string commandText = "SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'"; if (!String.IsNullOrEmpty(schemaName)) { commandText += " AND TABLE_SCHEMA = '" + schemaName + "'"; } if (!String.IsNullOrEmpty(tableName)) { commandText += " AND TABLE_NAME = '" + tableName + "'"; } cmd.CommandText = commandText; using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.Default)) { while (reader.Read()) { string constraintSchema = reader.GetString(0); string constraintName = reader.GetString(1); string sourceTableSchema = reader.GetString(2); string sourceTable = reader.GetString(3); constraints.Add(new DcilReferenceConstraint(constraintSchema, constraintName, sourceTableSchema, sourceTable, "", "", new StringCollection(), new StringCollection())); } } int constraintCount = constraints.Count; for (int i = 0; i < constraintCount; ++i) { DcilReferenceConstraint constraint = constraints[i]; IDbCommand sourceColumns = _conn.CreateCommand(); sourceColumns.CommandType = CommandType.Text; sourceColumns.CommandText = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME = '" + constraint.Name + "' AND CONSTRAINT_SCHEMA = '" + constraint.Schema + "' AND TABLE_SCHEMA = '" + constraint.SourceTableSchema + "' AND TABLE_NAME = '" + constraint.SourceTable + "'"; using (IDataReader sourceColumnReader = sourceColumns.ExecuteReader(CommandBehavior.Default)) { while (sourceColumnReader.Read()) { constraint.SourceColumns.Add(sourceColumnReader.GetString(0)); } } IDbCommand targetColumns = _conn.CreateCommand(); targetColumns.CommandType = CommandType.Text; targetColumns.CommandText = "SELECT REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME = '" + constraint.Name + "' AND CONSTRAINT_SCHEMA = '" + constraint.Schema + "' AND TABLE_SCHEMA = '" + constraint.SourceTableSchema + "' AND TABLE_NAME = '" + constraint.SourceTable + "'"; StringCollection targetColumnList = new StringCollection(); using (IDataReader targetColumnReader = targetColumns.ExecuteReader(CommandBehavior.Default)) { while (targetColumnReader.Read()) { if (constraint.TargetTableSchema == "") { constraint.TargetTableSchema = targetColumnReader.GetString(0); constraint.TargetTable = targetColumnReader.GetString(1); } constraint.TargetColumns.Add(targetColumnReader.GetString(2)); } } } return(constraints); } finally { if (opened && _conn.State == ConnectionState.Open) { _conn.Close(); } } }
/// <summary> /// When implemented in a child class, loads a generic list of <see cref="ORMSolutions.ORMArchitect.DatabaseImport.DcilReferenceConstraint"/> objects (representing Foreign Keys) for the specified Oracle Schema and Table /// </summary> /// <param name="schemaName">Name of the Oracle Schema for which the given Table resides in</param> /// <param name="tableName">Name of the Table from which to load the Indexes</param> /// <returns>Generic list of <see cref="ORMSolutions.ORMArchitect.DatabaseImport.DcilReferenceConstraint"/> objects (representing Foreign Keys) for the specified Oracle Schema and Table</returns> public IList <DcilReferenceConstraint> LoadForeignKeys(string schemaName, string tableName) { IList <DcilReferenceConstraint> constraints = new List <DcilReferenceConstraint>(); bool opened = false; try { if (_conn.State != ConnectionState.Open) { _conn.Open(); opened = true; } IDbCommand cmd = _conn.CreateCommand(); cmd.CommandType = CommandType.Text; string commandText = "select a.owner, a.table_name, a.constraint_name, a.r_owner, b.table_name r_table_name, a.r_constraint_name " + "from all_constraints a " + " join all_constraints b on (b.owner = a.r_owner and b.constraint_name = a.r_constraint_name) " + "where a.constraint_type in ('R') "; if (!String.IsNullOrEmpty(schemaName)) { commandText += "and a.owner = '" + schemaName + "' "; } if (!String.IsNullOrEmpty(tableName)) { commandText += "and a.table_name = '" + tableName + "' "; } cmd.CommandText = commandText; IList <string> targetConstraintNames = new List <string>(); using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.Default)) { while (reader.Read()) { string constraintSchema = reader.GetString(0); string sourceTableSchema = reader.GetString(0); string sourceTable = reader.GetString(1); string constraintName = reader.GetString(2); string targetTableSchema = reader.GetString(3); string targetTable = reader.GetString(4); targetConstraintNames.Add(reader.GetString(5)); constraints.Add(new DcilReferenceConstraint(constraintSchema, constraintName, sourceTableSchema, sourceTable, targetTableSchema, targetTable, new StringCollection(), new StringCollection())); } } int constraintCount = constraints.Count; for (int i = 0; i < constraintCount; ++i) { DcilReferenceConstraint constraint = constraints[i]; IDbCommand sourceColumns = _conn.CreateCommand(); sourceColumns.CommandType = CommandType.Text; sourceColumns.CommandText = string.Format("select column_name from all_cons_columns " + "where owner = '{0}' and table_name = '{1}' and constraint_name = '{2}' " + "order by position", constraint.Schema, constraint.SourceTable, constraint.Name); using (IDataReader sourceColumnReader = sourceColumns.ExecuteReader(CommandBehavior.Default)) { while (sourceColumnReader.Read()) { constraint.SourceColumns.Add(sourceColumnReader.GetString(0)); } } string targetConstraintName = targetConstraintNames[i]; IDbCommand targetColumns = _conn.CreateCommand(); targetColumns.CommandType = CommandType.Text; targetColumns.CommandText = string.Format("select column_name from all_cons_columns " + "where owner = '{0}' and table_name = '{1}' and constraint_name = '{2}' " + "order by position", constraint.TargetTableSchema, constraint.TargetTable, targetConstraintName); using (IDataReader targetColumnReader = targetColumns.ExecuteReader(CommandBehavior.Default)) { while (targetColumnReader.Read()) { constraint.TargetColumns.Add(targetColumnReader.GetString(0)); } } } return(constraints); } finally { if (opened && _conn.State == ConnectionState.Open) { _conn.Close(); } } }