private FkTable[] GetFkRelationShips(int objectId, TypeSearchRelationship relationship, string dbPath = "")
{
var query = "";
switch (relationship)
{
case TypeSearchRelationship.Direct:
query = _queryGenerator.GetRelationshipDirect(objectId, "parent_object_id", dbPath);
break;
case TypeSearchRelationship.Indirect:
query = _queryGenerator.GetRelationshipOneToMany(objectId, dbPath);
break;
case TypeSearchRelationship.ManyToMany:
query = _queryGenerator.GetRelationshipManyToMany(objectId, dbPath);
break;
}
var dtFkRel = _dbConnect.OpenConnectionAndExecute(query) ?? new DataTable();
var tmpPos = 0;
var fkTables = new FkTable[dtFkRel.Rows.Count];
foreach (DataRow item in dtFkRel.Rows)
{
var tmpFk = new FkTable(item);
fkTables[tmpPos] = tmpFk;
tmpPos++;
}
return(fkTables);
}
private FkTable[] GetFkRelationShips(int object_id, TypeSearchRelationship relationship, string dbPath = "")
{
string queryFormat = "";
if (relationship == TypeSearchRelationship.Direct)
{
string query = @"SELECT
fk.name AS 'FkName',
o1.name 'FkTable',
c1.name 'FkColumn',
o2.name 'PkTable',
c2.name 'PkColumn'
FROM
{0}sys.foreign_keys fk
INNER JOIN
{0}sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
-- Obtenemos la relacion con las columans
INNER JOIN
{0}sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_id
INNER JOIN
{0}sys.columns c2 ON fkc.referenced_column_id = c2.column_id AND fkc.referenced_object_id = c2.object_id
-- Obtenemos la relacion con las Tablas
INNER JOIN
{0}sys.objects o1 ON o1.object_id = c1.object_id
INNER JOIN
{0}sys.objects o2 ON o2.object_id = c2.object_id
WHERE fk.{1} = {2}";
queryFormat = string.Format(query, dbPath, "parent_object_id", object_id);
}
else if (relationship == TypeSearchRelationship.Indirect)
{
queryFormat = string.Format(SQLQuery.GetOneToMany, object_id, dbPath);
}
else if (relationship == TypeSearchRelationship.ManyToMany)
{
queryFormat = string.Format(SQLQuery.GetManyToMany, object_id, dbPath);
}
DataTable dtFkRel = dbConnect.Execute(queryFormat);
int tmpPos = 0;
FkTable[] fkTables = new FkTable[dtFkRel.Rows.Count];
FkTable tmpFk = null;
foreach (DataRow item in dtFkRel.Rows)
{
tmpFk = new FkTable(item);
fkTables[tmpPos] = tmpFk;
tmpPos++;
}
return(fkTables);
}
