/// <summary>
/// Loads a generic list of <see cref="ORMSolutions.ORMArchitect.DatabaseImport.DcilUniquenessConstraint"/> objects (representing Uniqueness Constraints) 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.DcilUniquenessConstraint"/> objects (representing Uniqueness Constraints) for the specified MySQL 5.0 Schema and Table</returns>
public IList <DcilUniquenessConstraint> LoadIndexes(string schemaName, string tableName)
{
IList <DcilUniquenessConstraint> constraints = new List <DcilUniquenessConstraint>();
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, CASE WHEN CONSTRAINT_TYPE = 'PRIMARY KEY' THEN 1 ELSE 0 END AS IS_PRIMARY FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE (CONSTRAINT_TYPE = 'PRIMARY KEY' OR CONSTRAINT_TYPE = 'UNIQUE')";
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 tableSchema = reader.GetString(2);
string table = reader.GetString(3);
bool isPrimary = (reader.GetInt32(4) == 1 ? true : false);
constraints.Add(new DcilUniquenessConstraint(constraintSchema, constraintName, tableSchema, table, new StringCollection(), isPrimary));
}
}
int constraintCount = constraints.Count;
for (int i = 0; i < constraintCount; ++i)
{
DcilUniquenessConstraint constraint = constraints[i];
IDbCommand columns = _conn.CreateCommand();
columns.CommandType = CommandType.Text;
columns.CommandText = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA = '" + constraint.Schema + "' AND CONSTRAINT_NAME = '" + constraint.Name + "' AND TABLE_SCHEMA = '" + constraint.ParentTableSchema + "' AND TABLE_NAME = '" + constraint.ParentTable + "'";
StringCollection columnList = new StringCollection();
using (IDataReader columnReader = columns.ExecuteReader(CommandBehavior.Default))
{
while (columnReader.Read())
{
constraint.Columns.Add(columnReader.GetString(0));
}
}
}
return(constraints);
}
finally
{
if (opened && _conn.State == ConnectionState.Open)
{
_conn.Close();
}
}
}
/// <summary>
/// Loads a generic list of <see cref="ORMSolutions.ORMArchitect.DatabaseImport.DcilUniquenessConstraint"/> objects (representing Uniqueness Constraints) 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.DcilUniquenessConstraint"/> objects (representing Uniqueness Constraints) for the specified Oracle Schema and Table</returns>
public IList <DcilUniquenessConstraint> LoadIndexes(string schemaName, string tableName)
{
IList <DcilUniquenessConstraint> constraints = new List <DcilUniquenessConstraint>();
bool opened = false;
try
{
if (_conn.State != ConnectionState.Open)
{
_conn.Open();
opened = true;
}
IDbCommand cmd = _conn.CreateCommand();
cmd.CommandType = CommandType.Text;
string commandText = "select owner, table_name, constraint_name, constraint_type " +
"from all_constraints " +
"where constraint_type in ('P', 'U') ";
if (!String.IsNullOrEmpty(schemaName))
{
commandText += " and owner = '" + 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 table = reader.GetString(1);
string constraintName = reader.GetString(2);
string tableSchema = reader.GetString(0);
bool isPrimary = reader.GetString(3) == "P" ? true : false;
constraints.Add(new DcilUniquenessConstraint(constraintSchema, constraintName, tableSchema, table, new StringCollection(), isPrimary));
}
}
int constraintCount = constraints.Count;
for (int i = 0; i < constraintCount; ++i)
{
DcilUniquenessConstraint constraint = constraints[i];
IDbCommand columns = _conn.CreateCommand();
columns.CommandType = CommandType.Text;
columns.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.ParentTable, constraint.Name);
StringCollection columnList = new StringCollection();
using (IDataReader columnReader = columns.ExecuteReader(CommandBehavior.Default))
{
while (columnReader.Read())
{
constraint.Columns.Add(columnReader.GetString(0));
}
}
}
return(constraints);
}
finally
{
if (opened && _conn.State == ConnectionState.Open)
{
_conn.Close();
}
}
}
