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