public List <ErikEJ.SqlCeScripting.Constraint> GetAllForeignKeys()
{
var result = new List <ErikEJ.SqlCeScripting.Constraint>();
var dt = cn.GetSchema("ForeignKeys");
for (int i = 0; i < dt.Rows.Count; i++)
{
var fk = new ErikEJ.SqlCeScripting.Constraint();
fk.ColumnName = dt.Rows[i]["FKEY_FROM_COLUMN"].ToString();
fk.ConstraintName = dt.Rows[i]["CONSTRAINT_NAME"].ToString();
fk.ConstraintTableName = dt.Rows[i]["TABLE_NAME"].ToString();
fk.DeleteRule = dt.Rows[i]["FKEY_ON_DELETE"].ToString();
fk.UniqueColumnName = dt.Rows[i]["FKEY_TO_COLUMN"].ToString();
fk.UniqueConstraintName = "PK_" + dt.Rows[i]["FKEY_TO_TABLE"].ToString();
fk.UniqueConstraintTableName = dt.Rows[i]["FKEY_TO_TABLE"].ToString();
fk.UpdateRule = dt.Rows[i]["FKEY_ON_UPDATE"].ToString();
fk.Columns = new ColumnList();
fk.UniqueColumns = new ColumnList();
result.Add(fk);
}
return(RepositoryHelper.GetGroupForeingKeys(result, GetAllTableNames()));
}
// Contrib from hugo on CodePlex - thanks!
public static List<Constraint> GetGroupForeingKeys(List<Constraint> foreignKeys, List<string> allTables)
{
var groupedForeingKeys = new List<Constraint>();
var uniqueTables = (from c in foreignKeys
select c.ConstraintTableName).Distinct().ToList();
int i = 1;
foreach (string tableName in uniqueTables)
{
{
var uniqueConstraints = (from c in foreignKeys
where c.ConstraintTableName == tableName
select c.ConstraintName).Distinct().ToList();
foreach (string item in uniqueConstraints)
{
string value = item;
var constraints = foreignKeys.Where(c => c.ConstraintName.Equals(value, System.StringComparison.Ordinal) && c.ConstraintTableName == tableName).ToList();
if (constraints.Count == 1)
{
Constraint constraint = constraints[0];
constraint.Columns.Add(constraint.ColumnName);
constraint.UniqueColumns.Add(constraint.UniqueColumnName);
var found = groupedForeingKeys.Where(fk => fk.ConstraintName == constraint.ConstraintName && fk.ConstraintTableName != constraint.ConstraintTableName).Any();
if (found)
{
constraint.ConstraintName = constraint.ConstraintName + i.ToString();
i++;
}
else
{
var tfound = allTables.Where(ut => ut == constraint.ConstraintName).Any();
if (tfound)
{
constraint.ConstraintName = constraint.ConstraintName + i.ToString();
i++;
}
}
groupedForeingKeys.Add(constraint);
}
else
{
var newConstraint = new Constraint { ConstraintTableName = constraints[0].ConstraintTableName, ConstraintName = constraints[0].ConstraintName, UniqueConstraintTableName = constraints[0].UniqueConstraintTableName, UniqueConstraintName = constraints[0].UniqueConstraintName, DeleteRule = constraints[0].DeleteRule, UpdateRule = constraints[0].UpdateRule, Columns = new ColumnList(), UniqueColumns = new ColumnList() };
foreach (Constraint c in constraints)
{
newConstraint.Columns.Add(c.ColumnName);
newConstraint.UniqueColumns.Add(c.UniqueColumnName);
}
var found = groupedForeingKeys.Where(fk => fk.ConstraintName == newConstraint.ConstraintName && fk.ConstraintTableName != newConstraint.ConstraintTableName).Any();
if (found)
{
newConstraint.ConstraintName = newConstraint.ConstraintName + i.ToString();
i++;
}
groupedForeingKeys.Add(newConstraint);
}
}
}
}
return groupedForeingKeys;
}
