/// <summary>
/// Reads specified table foreign keys.
/// </summary>
private void ApplyTablesForeignKeys(List <DbTable> tables)
{
if (_dbConnection.State != ConnectionState.Open)
{
_dbConnection.Open();
}
// Used to get columns Sql DataType
using (DataTable foreignKeysTable = _dbConnection.GetSchema("ForeignKeys"))
{
// nothing found!
if (foreignKeysTable.Rows.Count == 0)
{
return;
}
// find description if there is any
foreach (var table in tables)
{
// only FOREIGN KEY
foreignKeysTable.DefaultView.RowFilter =
string.Format(" CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_NAME='{0}' ", table.TableName);
// Fetch the rows
foreach (DataRowView keysData in foreignKeysTable.DefaultView)
{
// foreign key found!
DataRow keyRow = keysData.Row;
var foreignKeyTableName = keyRow["FKEY_TO_TABLE"].ToString();
var primaryKeyTableName = table.TableName;
var foreignKeyTable = FindTable(tables, foreignKeyTableName);
var primaryKeyTable = table;
if (primaryKeyTable != null)
{
// foreign key
var foreignKey = new DbForeignKey()
{
ForeignKeyName = keyRow["CONSTRAINT_NAME"].ToString(),
LocalColumnName = keyRow["FKEY_FROM_COLUMN"].ToString(),
ForeignColumnName = keyRow["FKEY_TO_COLUMN"].ToString(),
ForeignTableName = keyRow["FKEY_TO_TABLE"].ToString(),
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.OneToMany
};
// add foreign key
table.ForeignKeys.Add(foreignKey);
// apply local column
DbColumn localColumn = table.FindColumnDb(foreignKey.LocalColumnName);
foreignKey.LocalColumn = localColumn;
//apply foreign table
DbTable foreignTable = foreignKeyTable;
// referenced key
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = foreignTable;
}
if (foreignTable != null)
{
foreignKey.ForeignTable = foreignTable;
// apply foreign column
DbColumn foreignColumn = foreignTable.FindColumnDb(foreignKey.ForeignColumnName);
foreignKey.ForeignColumn = foreignColumn;
}
else
{
foreignKey.ForeignTable = null;
foreignKey.ForeignColumn = null;
}
}
// adding the relation to the foreign table!
if (foreignKeyTable != null)
{
// foreign key
var oneMultiplicityKey_Foreign = new DbForeignKey()
{
ForeignKeyName = keyRow["CONSTRAINT_NAME"].ToString(),
LocalColumnName = keyRow["FKEY_TO_COLUMN"].ToString(),
ForeignColumnName = keyRow["FKEY_FROM_COLUMN"].ToString(),
ForeignTableName = primaryKeyTableName,
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.ManyToOne
};
// check if it is already there
if (foreignKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == oneMultiplicityKey_Foreign.ForeignKeyName))
{
continue;
}
// to the list
foreignKeyTable.ForeignKeys.Add(oneMultiplicityKey_Foreign);
// apply local column
DbColumn localColumn = foreignKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.LocalColumnName);
oneMultiplicityKey_Foreign.LocalColumn = localColumn;
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = primaryKeyTable;
}
if (primaryKeyTable != null)
{
// foreign table of that!
oneMultiplicityKey_Foreign.ForeignTable = primaryKeyTable;
// apply foreign column
DbColumn foreignColumn = primaryKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.ForeignColumnName);
oneMultiplicityKey_Foreign.ForeignColumn = foreignColumn;
}
else
{
oneMultiplicityKey_Foreign.ForeignTable = null;
oneMultiplicityKey_Foreign.ForeignColumn = null;
}
}
}
}
}
}