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;

 

}

Print | posted on Tuesday, January 27, 2004 1:26 PM
Comments have been closed on this topic.