public static List <PossibleReference> Get_DocumentedReferences()
{
LogHelper.LogApp($"{MethodBase.GetCurrentMethod().Name}");
Dictionary <string, PossibleReference> RefNameToObject = new Dictionary <string, PossibleReference>();
List <string> AllDocRefNames = Get_AllDocRefNames();
if (AllDocRefNames.Count > 0)
{
foreach (string s in Get_AllDocRefNames())
{
PossibleReference _tmp = new PossibleReference()
{
PK_Relation = Get_DocRef_PK_Relation(s),
FK_Relation = Get_DocRef_FK_Relation(s),
FK_Attributes = Get_DocRef_FK_Attributes(s)
};
_tmp.Childless = Get_PRStat_Childless(_tmp);
_tmp.Parents = Get_PRStat_Parents(_tmp);
_tmp.Orphans = 0;
RefNameToObject.Add(s, _tmp);
}
}
return(RefNameToObject.Values.ToList());
}
private static string Get_PRStat_JoinOn(PossibleReference _PR)
{
string _joinon = "";
for (int i = 0; i < _PR.FK_Attributes.Count; i++)
{
_joinon += $"T_FK.[{_PR.FK_Attributes[i]}] = T_PK.[{_PR.PK_Relation.Attributes[i]}] {(i < _PR.FK_Attributes.Count-1 ? "AND" : " ")}";
}
return(_joinon);
}
public static PossibleReference Test_Reference(PossibleKey PK, string R_FK, string C_FK)
{
PossibleReference _tmp = new PossibleReference()
{
PK_Relation = PK,
FK_Relation = R_FK,
FK_Attributes = new List <string>(new string[] { C_FK })
};
_tmp.Childless = Get_PRStat_Childless(_tmp);
_tmp.Parents = Get_PRStat_Parents(_tmp);
_tmp.Orphans = Get_PRStat_Orphans(_tmp);
return(_tmp);
}
private static long Get_PRStat_Orphans(PossibleReference _PR)
{
LogHelper.LogApp($"{MethodBase.GetCurrentMethod().Name}");
string _select = Get_PRStat_Select(_PR);
string _joinon = Get_PRStat_JoinOn(_PR);
string _where = "";
for (int i = 0; i < _PR.FK_Attributes.Count; i++)
{
_where += $"PK{i} IS NULL {(i < _PR.FK_Attributes.Count - 1 ? "AND" : " ")}";
}
string sql_cmd_str = $@"
SELECT
COUNT_BIG(*)
FROM (
SELECT
{_select}
FROM
{_PR.FK_Relation} T_FK
LEFT OUTER JOIN
{_PR.PK_Relation.Relation} T_PK
ON
{_joinon}
) K
WHERE {_where}
;
";
SqlDataReader _DR = DBManager.ExecuteRead(sql_cmd_str);
_DR.Read();
long?_c = _DR.IsDBNull(0) ? (long?)null : _DR.GetInt64(0);
_DR.Close();
if (!_c.HasValue)
{
throw new ArgumentNullException("SQL Query returned invalid NULL value!");
}
return(_c.Value);
}
private static string Get_PRStat_Select(PossibleReference _PR)
{
int i = 0, j = 0;
string _select = "";
foreach (string _a in _PR.FK_Attributes)
{
_select += $"T_FK.[{_a}] FK{i},";
i++;
}
foreach (string _a in _PR.PK_Relation.Attributes)
{
_select += $"T_PK.[{_a}] PK{j}{(_a == _PR.PK_Relation.Attributes.Last() ? "" : ",")}";
j++;
}
return(_select);
}
private static long Get_PRStat_Parents(PossibleReference _PR)
{
LogHelper.LogApp($"{MethodBase.GetCurrentMethod().Name}");
string _select = Get_PRStat_Select(_PR);
string _joinon = Get_PRStat_JoinOn(_PR);
string sql_cmd_str = $@"
SELECT
COUNT_BIG(*)
FROM (
SELECT
{_select}
FROM
{_PR.FK_Relation} T_FK
INNER JOIN
{_PR.PK_Relation.Relation} T_PK
ON
{_joinon}
) K
;
";
SqlDataReader _DR = DBManager.ExecuteRead(sql_cmd_str);
_DR.Read();
long?_c = _DR.IsDBNull(0) ? (long?)null : _DR.GetInt64(0);
_DR.Close();
if (!_c.HasValue)
{
throw new ArgumentNullException("SQL Query returned invalid NULL value!");
}
return(_c.Value);
}
