/// <summary>
/// Reads specified table foreign keys.
/// </summary>
private void ApplyTablesForeignKeys(List<DbTable> tables, SQLServerVersions sqlServer)
{
/*
* sql 2005 format
SELECT CONVERT(SYSNAME, DB_NAME()) AS PKTABLE_QUALIFIER, CONVERT(SYSNAME, SCHEMA_NAME(O1.schema_id)) AS PKTABLE_OWNER, CONVERT(SYSNAME,
O1.name) AS PKTABLE_NAME, CONVERT(SYSNAME, C1.name) AS PKCOLUMN_NAME, CONVERT(SYSNAME, DB_NAME()) AS FKTABLE_QUALIFIER,
CONVERT(SYSNAME, SCHEMA_NAME(O2.schema_id)) AS FKTABLE_OWNER, CONVERT(SYSNAME, O2.name) AS FKTABLE_NAME, CONVERT(SYSNAME, C2.name)
AS FKCOLUMN_NAME, CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsUpdateCascade') WHEN 1 THEN 0 ELSE 1 END) AS UPDATE_RULE,
CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsDeleteCascade') WHEN 1 THEN 0 ELSE 1 END) AS DELETE_RULE, CONVERT(SYSNAME,
OBJECT_NAME(F.object_id)) AS FK_NAME, CONVERT(SYSNAME, I.name) AS PK_NAME, CONVERT(SMALLINT, 7) AS DEFERRABILITY, F.delete_referential_action,
F.update_referential_action
FROM sys.all_objects AS O1 INNER JOIN
sys.foreign_keys AS F INNER JOIN
sys.foreign_key_columns AS K ON K.constraint_object_id = F.object_id INNER JOIN
sys.indexes AS I ON F.referenced_object_id = I.object_id AND F.key_index_id = I.index_id ON O1.object_id = F.referenced_object_id INNER JOIN
sys.all_objects AS O2 ON F.parent_object_id = O2.object_id INNER JOIN
sys.all_columns AS C1 ON F.referenced_object_id = C1.object_id AND K.referenced_column_id = C1.column_id INNER JOIN
sys.all_columns AS C2 ON F.parent_object_id = C2.object_id AND K.parent_column_id = C2.column_id
*/
// GENERAL command format
string foreignKeySql = @"SELECT OBJECT_NAME(f.constid) AS 'ForeignKey', OBJECT_NAME(f.fkeyid) AS 'FKTable',
c1.name AS 'FKColumnName', OBJECT_NAME(f.rkeyid) AS 'PKTable', c2.name AS 'PKColumnName' ,
-1 as update_referential_action, -1 as delete_referential_action
FROM sysforeignkeys AS f
INNER JOIN syscolumns AS c1 ON f.fkeyid = c1.id AND f.fkey = c1.colid
INNER JOIN syscolumns AS c2 ON f.rkeyid = c2.id AND f.rkey = c2.colid
ORDER BY c1.colid ";
// NEW command format
if (sqlServer > SQLServerVersions.SQL2000)
{
foreignKeySql =
@"SELECT CONVERT(SYSNAME, DB_NAME()) AS PKTABLE_QUALIFIER, CONVERT(SYSNAME, SCHEMA_NAME(O1.schema_id)) AS PKTABLE_OWNER, CONVERT(SYSNAME,
O1.name) AS 'PKTable', CONVERT(SYSNAME, C1.name) AS 'PKColumnName', CONVERT(SYSNAME, DB_NAME()) AS FKTABLE_QUALIFIER,
CONVERT(SYSNAME, SCHEMA_NAME(O2.schema_id)) AS FKTABLE_OWNER, CONVERT(SYSNAME, O2.name) AS 'FKTable', CONVERT(SYSNAME, C2.name)
AS 'FKColumnName', CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsUpdateCascade') WHEN 1 THEN 0 ELSE 1 END) AS UPDATE_RULE,
CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsDeleteCascade') WHEN 1 THEN 0 ELSE 1 END) AS DELETE_RULE, CONVERT(SYSNAME,
OBJECT_NAME(F.object_id)) AS 'ForeignKey', CONVERT(SYSNAME, I.name) AS PK_NAME, CONVERT(SMALLINT, 7) AS DEFERRABILITY, F.delete_referential_action,
F.update_referential_action
FROM sys.all_objects AS O1 INNER JOIN
sys.foreign_keys AS F INNER JOIN
sys.foreign_key_columns AS K ON K.constraint_object_id = F.object_id INNER JOIN
sys.indexes AS I ON F.referenced_object_id = I.object_id AND F.key_index_id = I.index_id ON O1.object_id = F.referenced_object_id INNER JOIN
sys.all_objects AS O2 ON F.parent_object_id = O2.object_id INNER JOIN
sys.all_columns AS C1 ON F.referenced_object_id = C1.object_id AND K.referenced_column_id = C1.column_id INNER JOIN
sys.all_columns AS C2 ON F.parent_object_id = C2.object_id AND K.parent_column_id = C2.column_id
ORDER BY C2.column_id";
}
try
{
using (var adapter = new SqlDataAdapter(foreignKeySql, (SqlConnection)_dbConnection))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// description data table
using (var keysData = new DataTable())
{
// Just to avoid stupid "Failed to enable constraints" error!
using (var tempDs = new DataSet())
{
// Avoiding stupid "Failed to enable constraints" error!
tempDs.EnforceConstraints = false;
tempDs.Tables.Add(keysData);
// Get from db
adapter.Fill(keysData);
}
if (keysData.Rows.Count > 0)
{
foreach (DataRow keysDataRow in keysData.Rows)
{
var foreignKeyTableName = keysDataRow["FKTable"].ToString();
var primaryKeyTableName = keysDataRow["PKTable"].ToString();
var foreignKeyTable = FindTable(tables, foreignKeyTableName);
var primaryKeyTable = FindTable(tables, primaryKeyTableName);
// one-to-many foreign relation will be added
if (primaryKeyTable != null)
{
// foreign key many end
var manyMultiplicityKey_Local = new DbForeignKey()
{
ForeignKeyName = keysDataRow["ForeignKey"].ToString(),
LocalColumnName = keysDataRow["PKColumnName"].ToString(),
ForeignColumnName = keysDataRow["FKColumnName"].ToString(),
ForeignTableName = keysDataRow["FKTable"].ToString(),
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.ManyToOne
};
// check if it is already there
if (primaryKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == manyMultiplicityKey_Local.ForeignKeyName))
continue;
manyMultiplicityKey_Local.UpdateAction =
ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["update_referential_action"].ToString()));
manyMultiplicityKey_Local.DeleteAction =
ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["delete_referential_action"].ToString()));
// to the list
primaryKeyTable.ForeignKeys.Add(manyMultiplicityKey_Local);
// apply local column
DbColumn localColumn = primaryKeyTable.FindColumnDb(manyMultiplicityKey_Local.LocalColumnName);
manyMultiplicityKey_Local.LocalColumn = localColumn;
if (!localColumn.PrimaryKey)
{
localColumn.IsReferenceKey = true;
localColumn.IsReferenceKeyTable = primaryKeyTable;
}
if (foreignKeyTable != null)
{
// foreign table of that!
manyMultiplicityKey_Local.ForeignTable = foreignKeyTable;
// apply foreign column
DbColumn foreignColumn = foreignKeyTable.FindColumnDb(manyMultiplicityKey_Local.ForeignColumnName);
manyMultiplicityKey_Local.ForeignColumn = foreignColumn;
}
else
{
manyMultiplicityKey_Local.ForeignTable = null;
manyMultiplicityKey_Local.ForeignColumn = null;
}
}
// one-to-? foreign relation will be added
if (foreignKeyTable != null)
{
// foreign key many end
var oneMultiplicityKey_Foreign = new DbForeignKey()
{
ForeignKeyName = keysDataRow["ForeignKey"].ToString(),
LocalColumnName = keysDataRow["FKColumnName"].ToString(),
ForeignColumnName = keysDataRow["PKColumnName"].ToString(),
ForeignTableName = keysDataRow["PKTable"].ToString(),
Multiplicity = DbForeignKey.ForeignKeyMultiplicity.OneToMany
};
// check if it is already there
if (foreignKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == oneMultiplicityKey_Foreign.ForeignKeyName))
continue;
oneMultiplicityKey_Foreign.UpdateAction =
ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["update_referential_action"].ToString()));
oneMultiplicityKey_Foreign.DeleteAction =
ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["delete_referential_action"].ToString()));
// 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;
}
}
}// all foreign keys
// look for one-to-one situation!
}
}
}
}
catch
{
// Seems this version of SQL Server doesn't support this query!
// don't stop here!
}
}
/// <summary>
/// Removes duplicate table constraints, PK > UK > IX
/// </summary>
private void NormalizeTablesConstraintKeys(List<DbTable> result, SQLServerVersions sqlVersion)
{
// look in tables list
foreach (DbTable table in result)
{
if (table.ConstraintKeys.Count == 0)
{
continue;
}
StringCollection duplicateConstraints = new StringCollection();
// fetching the contraints keys
for (int j = table.ConstraintKeys.Count - 1; j >= 0; j--)
{
var constraintKey = table.ConstraintKeys[j];
// no primary keys are allowed
if (constraintKey.KeyColumn != null && constraintKey.KeyColumn.PrimaryKey)
{
// There is no need in keeping the primary key
table.ConstraintKeys.RemoveAt(j);
continue;
}
// first look in the foreign keys!
int index = table.ForeignKeys.FindIndex(x =>
x.LocalColumnName == constraintKey.KeyColumnName);
if (index != -1)
{
// this is a foreign key and should not be here
table.ConstraintKeys.RemoveAt(j);
continue;
}
// if this is not a unique key
// seach for a unique one if it is there
if (constraintKey.IsUnique == false)
{
index = table.ConstraintKeys.FindIndex(x =>
x.KeyColumnName == constraintKey.KeyColumnName
&& x.IsUnique == true);
if (index != -1)
{
// the same and the Unique key is already there!
table.ConstraintKeys.RemoveAt(j);
continue;
}
}
else
{
var notUniqueKeys = table.ConstraintKeys.FindAll(x =>
x.KeyColumnName == constraintKey.KeyColumnName
&& x.IsUnique == false);
if (notUniqueKeys != null && notUniqueKeys.Count > 0)
{
// remove them
notUniqueKeys.ForEach(x => table.ConstraintKeys.Remove(x));
continue;
}
}
// look for duplication constraint key
if (duplicateConstraints.Contains(constraintKey.KeyColumnName))
{
// the column with index is already there
table.ConstraintKeys.RemoveAt(j);
continue;
}
// all to the constraint key list
duplicateConstraints.Add(constraintKey.KeyColumnName);
}
}
}
/// <summary>
/// Reads tables index keys
/// </summary>
private void ApplyTablesConstraintKeys(List<DbTable> tables, SQLServerVersions sqlVersion)
{
if (sqlVersion == SQLServerVersions.SQL2000 ||
sqlVersion == SQLServerVersions.SQL2000Below)
{
// not supported
return;
}
//// Table constraints for SQL Server 2005 and above
//SELECT sys.objects.name AS TableName, sys.columns.name AS ColumnName, sys.indexes.name AS IndexName, sys.indexes.is_unique AS IsUnique,
// sys.indexes.is_primary_key AS IsPrimaryKey, sys.indexes.ignore_dup_key AS IgnoreDuplicateKey, sys.indexes.is_unique_constraint AS IsUniqueConstraintKey,
// sys.indexes.is_disabled AS Disabled
//FROM sys.objects INNER JOIN
// sys.indexes INNER JOIN
// sys.index_columns INNER JOIN
// sys.columns ON sys.index_columns.object_id = sys.columns.object_id AND sys.index_columns.column_id = sys.columns.column_id ON
// sys.indexes.object_id = sys.index_columns.object_id AND sys.indexes.index_id = sys.index_columns.index_id ON
// sys.objects.object_id = sys.index_columns.object_id
//WHERE (sys.objects.is_ms_shipped = 0) AND (sys.objects.type = 'U')
string constraintKeySql = "SELECT sys.objects.name AS TableName, sys.columns.name AS ColumnName, sys.indexes.name AS IndexName, sys.indexes.is_unique AS IsUnique, " +
" sys.indexes.is_primary_key AS IsPrimaryKey, sys.indexes.ignore_dup_key AS IgnoreDuplicateKey, sys.indexes.is_unique_constraint AS IsUniqueConstraintKey, " +
" sys.indexes.is_disabled AS Disabled " +
" FROM sys.objects INNER JOIN " +
" sys.indexes INNER JOIN " +
" sys.index_columns INNER JOIN " +
" sys.columns ON sys.index_columns.object_id = sys.columns.object_id AND sys.index_columns.column_id = sys.columns.column_id ON " +
" sys.indexes.object_id = sys.index_columns.object_id AND sys.indexes.index_id = sys.index_columns.index_id ON " +
" sys.objects.object_id = sys.index_columns.object_id " +
" WHERE (sys.objects.is_ms_shipped = 0) AND (sys.objects.type = 'U') ";
using (SqlDataAdapter adapter = new SqlDataAdapter(constraintKeySql, _dbConnection))
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// description data table
using (DataTable keysData = new DataTable())
{
// Just to avoid stupid "Failed to enable constraints" error!
using (DataSet tempDs = new DataSet())
{
// Avoiding stupid "Failed to enable constraints" error!
tempDs.EnforceConstraints = false;
tempDs.Tables.Add(keysData);
// Get from db
adapter.Fill(keysData);
}
if (keysData.Rows.Count > 0)
{
// find description if there is any
foreach (var table in tables)
{
// filter row
keysData.DefaultView.RowFilter = " TableName='" + table.TableName + "' AND IsPrimaryKey=0 ";
// fetch findings, if there is any
foreach (DataRowView keysDataRow in keysData.DefaultView)
{
// found table !
DataRow keyRow = keysDataRow.Row;
// constraint Key
var constraintKey = new DbConstraintKey()
{
IsUnique = Convert.ToBoolean(keyRow["IsUnique"].ToString()),
KeyColumnName = keyRow["ColumnName"].ToString(),
KeyName = keyRow["IndexName"].ToString()
};
// constraint keys
table.ConstraintKeys.Add(constraintKey);
// find key column
DbColumn keyColumn = table.FindColumnDb(constraintKey.KeyColumnName);
constraintKey.KeyColumn = keyColumn;
}
}
}
}
}
}
