DataTable Relational Operators in C# - TCLOSE Method (Recursion)
This TCLOSE method has no equivalent in TSQL
TCLOSE stands for Transitive Closure and is used to solve hierarchy representations, most notably the part explosion problem.
The algorithm used here is the Semi naive method as outlined in "An Introduction to Database Systems 7th Edition."
As a side note.. this book should be a must have for every DBA on the planet and is currently in its 8th Edition.
To support DBDebunk purchase it via the link found here.. Books
If you use NULL to represent the root node then you will receive incorrect results.
This is simple to overcome by making the NULL value (Parent) the same as the Child.
As an example, the Northwind Employees table uses a NULL to represent the root.
Change the ReturnsTo column from NULL to be the same as the EmployeeID
There is only the one signature for this method.
The Mod calculations are used to offset the Join's renaming of columns.
public static DataTable TClose(DataTable Input, string ParentColumn, string ChildColumn)
{
//Final Result
DataTable table = SQLOps.Project(Input.Copy(),ParentColumn,ChildColumn);
//Temp
DataTable temp = table.Copy();
temp.TableName = "temp";
//Copy of Input
DataTable input = table.Copy();
//Recursion - SemiNaive
int i = 2;
while(temp.Rows.Count > 0)
{
if(i % 2 == 0)
temp = SQLOps.Join(temp,input,temp.Columns[ParentColumn],input.Columns[ChildColumn]);
else
temp = SQLOps.Join(temp,input,temp.Columns[ParentColumn+"_Second"],input.Columns[ChildColumn]);
if(i % 2 == 0)
temp = SQLOps.Project(temp, ChildColumn, ParentColumn+"_Second");
else
temp = SQLOps.Project(temp, ChildColumn, ParentColumn);
temp.TableName = "temp";
temp = SQLOps.Difference(temp,table);
table = SQLOps.Union(table,temp);
i++;
}
temp.Dispose();
input.Dispose();
return table;
}