Hierarchical Relationships Without DataRelation Object in ADO.NET

By Robbe D. Morris

Printer Friendly Version

Robbe Morris
Robbe & Melisa Morris
At my place of employment, I work a great deal with somewhat complex hierarchical data as part of our measurement and analysis software.  Thus, recursion and I have become rather close friends over the last few years.  While rewriting an old website, I needed to turn a set of four relational tables into a hierarchical data structure.  The top level table held self-referencing relationships as well as child relationships to table 2.  Tables 3 and 4 were your basic "run of the mill" child tables up through table2.
I initially turned to ADO.NET's DataRelation and DataSet objects so that they could handle the relationship management for me.  The DataRelation object's capabilities are dead on for single tables with self-referencing keys or multi-table relationships without self-referencing keys.  If your current situation falls into either of these categories, you'll want to use the DataRelation object scenario to recursively build your hierarchical structure rather than the solution described in this article.  In fact, most tutorials you'll find in books or on the web deal with either of these two situations.  However, I have found that the DataRelation object is a little flaky when it comes to combinations of self-referencing tables and multi-table nested relationships, particularly with those that run more than 2 levels deep or have their root level row with the same PrimaryKey as its ForeignKey ParentID.  Of course, if you are using foreign key constraints, you can't just set the ParentID to 0.
With this in mind, I had to resort to writing a custom solution, taking control of the recursion at each and every level.  This tight control enables you to specifically direct how the recursion should react at what levels and how to handling switching back and forth between self-referencing keys and multi-table relationships at runtime.  In order to try and make the code as reusable as possible, I've embedded some of the relationship drivers in the DataTables returned from the SQL Server stored procedure.  You'll find these as derived columns named SQL and ParentColumn.  I use the hard coded values contained in both of these derived columns to build a query for the disconnected DataTable.
It is important to note that with the DataRow object of ADO.NET, the .Select method used does NOT make another trip to the database.  It actually performs a SQL query against the disconnected DataTable.  This is a pretty powerful feature for performing disconnected WHERE, ORDER BY, COMPUTE, AVG, etc... clauses against the DataTable.
It is probably easier to follow what I've done by stepping through the code and reading my comments.  To run the sample below, copy and paste the stored procedure into one your existing SQL Server databases.  The sample uses table variables with test data so there is no need to create your own database or special tables.  Then, create a new C# console application to copy and paste the sample code into.  The application will create a new DataTable with rows in hierarchical order and print the output to the command window.  At the very least, you can iterate through the new DataTable in hierarchical order or you can even create a new XmlDocument instead.  That's it.  I hope you found this as helpful to you as it was to me when putting it together.


SQL Server Stored Procedure
  
CREATE PROCEDURE dbo.EggHeadTest
 
as
set nocount on

declare @RowCnt int 
declare @MaxRows int 
declare @Email nvarchar(255) 

select @RowCnt = 1 
   
 /* Records will contain self-referencing relationships */
  declare @Sample1 table 
  ( 
      Sample1ID int  Primary key NOT NULL , ParentID int, SiblingOrder int,Description nvarchar(100)
   )

 /* Child records to Sample1 */ 

  declare @Sample2 table 
  ( 
      Sample2ID int  Primary key NOT NULL , Sample1ID int, SiblingOrder int, Description nvarchar(100)
   ) 

  /* Child records to Sample2 */
 
  declare @Sample3 table 
  ( 
      Sample3ID int  Primary key NOT NULL , Sample2ID int, SiblingOrder int, Description nvarchar(100)
   )  

 /* Child records to Sample3 */ 

  declare @Sample4 table 
  ( 
      Sample4ID int  Primary key NOT NULL , Sample3ID int, SiblingOrder int, Description nvarchar(100)
   )  

 /* Start loading of test data */
  insert into @Sample1 values(1,1,0,'CEO')
  insert into @Sample1 values(2,1,1,'Vice Pres. Marketing')
  insert into @Sample1 values(3,1,2,'Vice Pres. Ops-')
  insert into @Sample1 values(4,2,1,' Marketing Director - Direct Mail')
  insert into @Sample1 values(5,2,2,' Marketing Director - TV')
  insert into @Sample1 values(6,1,3,'Vice Pres. - Research')
  insert into @Sample1 values(7,4,1,' Human Resources Director')
  insert into @Sample1 values(8,4,2,' Some other item')
  insert into @Sample1 values(9,6,1,'Research Analyst')


  insert into @Sample2 values (1,8,1,'  Marketing Analyst')
  insert into @Sample2 values (2,8,2,'  Marketing Assistant')
  insert into @Sample2 values (3,9,1,'  Research Assistant 1')
  insert into @Sample2 values (4,9,2,'  Research Assistant 2')
  insert into @Sample2 values (5,9,3,'  Research Assistant 3')
  insert into @Sample2 values (6,7,1,'  Direct Mail Assistant 1')
  insert into @Sample2 values (7,7,2,'  Direct Mail Assistant 2')
  insert into @Sample2 values (8,7,3,'  Direct Mail Assistant 3')
  insert into @Sample2 values (9,7,4,'  Direct Mail Assistant 4')
 
  insert into @Sample3 values (1,9,1,'   Employee 1')
  insert into @Sample3 values (2,9,2,'   Employee 2')
  insert into @Sample3 values (3,9,3,'   Employee 3')
  insert into @Sample3 values (4,9,4,'   Employee 4')
  insert into @Sample3 values (5,9,5,'   Employee 5')
  insert into @Sample3 values (6,9,6,'   Employee 6')
  insert into @Sample3 values (7,9,7,'   Employee 7')
  insert into @Sample3 values (8,9,8,'   Employee 8')
  insert into @Sample3 values (9,9,9,'   Employee 9')
 
   insert into @Sample4 values (1,1,1,'       Contact 1')
   insert into @Sample4 values (2,1,2,'       Contact 2')
      
 /* End loading of test data */


/* Bring back a Resultset to be loaded into individual DataTable objects.
    Create derived columns for the DataRow object's .Select method and
    store it in the SQL column.  Also store the column name for the ParentColumn.
    This value is also used in the DataRow's .Select method query.
 */



SELECT 
        'SAMPLE1' as NodeType,
         Sample1ID,
         ParentID,
         SiblingOrder,
         Description,
         'ParentID <> Sample1ID and ParentID=' as SQL,
         'Sample1ID' as ParentColumn
FROM @Sample1 as Tree
   
     order by ParentID,SiblingOrder asc

SELECT 
        'SAMPLE2' as NodeType,
         Sample2ID,
         Sample1ID,
         SiblingOrder,
         Description,
         'Sample1ID=' as SQL,
         'Sample1ID' as ParentColumn
    FROM @Sample2  as Tree
    Where Sample1ID in (select Sample1ID from @Sample1)
     order by Sample1ID,SiblingOrder asc

SELECT 
        'SAMPLE3' as NodeType,
         Sample3ID,
         Sample2ID,
         SiblingOrder,
         Description,
         'Sample2ID=' as SQL,
         'Sample2ID' as ParentColumn
    FROM @Sample3  as Tree
    Where Sample2ID in (select Sample2ID from @Sample2)            
      order by Sample2ID,SiblingOrder asc


SELECT 
        'SAMPLE4' as NodeType,
         Sample4ID,
         Sample3ID,
         SiblingOrder,
         Description,
         'Sample3ID=' as SQL,
         'Sample3ID' as ParentColumn
    FROM @Sample4  as Tree
    Where Sample3ID in (select Sample3ID from @Sample3)            
      order by Sample3ID,SiblingOrder asc

 set nocount off
GO
C# Console Application Source Code
  
using System;
using System.Data;
using System.Data.SqlClient; 

namespace DataTableArticle
{
	 
	class Class1
	{
		 
		[STAThread]
		static void Main(string[] args)
		{
			Class1 oTest = new Class1();
			oTest.LoadTree(); 
		
		}


		public void LoadTree()
		{
			SqlConnection oConn = new SqlConnection();
			DataSet oDataSet = new DataSet();
			DataTable oTable = new DataTable();
			DataTable oNewTable = new DataTable();
			DataRow[] oTopLevelRow;

			string sConnectionString = "SQL Server connection string goes here.";	
			 
	 
			try
			{   
				/* execute stored procedure and fill dataset */

				oConn.ConnectionString = sConnectionString;
				
				oConn.Open();	  
				
				SqlDataAdapter oDA = new SqlDataAdapter("exec EggHeadTest",oConn);
				
				oDA.Fill(oDataSet);

				oConn.Close();
 
				oTable = oDataSet.Tables[0]; 

				/* select the top level row in the hierarchy */

				oTopLevelRow = oTable.Select("ParentID=Sample1ID"); 
 
				/* Clone the table structure and details for our new table */

				oNewTable = oTable.Clone();

				/* Create a copy of the top level row
				   and place it in our new table */

				this.AddRow(ref oNewTable,oTopLevelRow[0]);
	 
				/* Start the recursion with our top level row */

				TransformToHierarchy(0,ref oNewTable, oTopLevelRow[0]); 
			  
				/* Iterate through the rows of our newly created
				   DataTable. */

				foreach(DataRow oRow in oNewTable.Rows)
				{
				  Console.Write(oRow["NodeType"].ToString() + "  "); 
				  Console.Write(oRow[1].ToString() + "    ");
				  Console.Write(oRow[2].ToString() + "    ");
				  Console.Write(oRow["SiblingOrder"].ToString() + "    ");
				  Console.WriteLine(oRow["Description"].ToString() + "    ");
				}
  		  
			}
			catch (Exception e) { Console.WriteLine(e.Message); }  
			finally	{ if (oConn.State == ConnectionState.Open) { oConn.Close(); } } 
				
			/* Don't let console window close until we're ready */

			Console.ReadLine(); 

		}

	private void TransformToHierarchy(int TableIndex,ref DataTable oNewTable,DataRow oParentRow)
		{

		    DataRow[] oRows = null;
           
			try
			{ 

				/* Evaluate which level of the hierarchy we're
				   working with */ 

				switch (TableIndex)
				{
					case 0:
                                                                                                                               /* 
						   Working with a self-referencing
						   relationship.  If no keys found,
						   skip to next level table.
						  */

						    oRows = GetRows(TableIndex,oParentRow);
						
						    if (oRows.Length < 1)
					                       {
                                                                                                                                    TableIndex += 1;
						        oRows = GetRows(TableIndex,oParentRow);
						    }

						    break;
					
					case 1:
                                                                                                                                 /* 
						    working with a known multi-table child relationship
						    so automatically advance to next table
						    level in the hierarchy.
						   */
                                                                                                                                  TableIndex += 1;
                                                                                                                                  oRows = GetRows(TableIndex,oParentRow);
						    break;
					
					case 2:
			                                                                    /* 
						    Working with a known multi-table child relationship
						    so automatically advance to next table
						    level in the hierarchy.
						   */
                                                                                                                                   TableIndex += 1;
						    oRows = GetRows(TableIndex,oParentRow);
						    break;
 
					default:
				             	                    /* 
						  We know there are no more child table levels
						  to go, so exit method.
						 */
						    return;
				}

				/* 
				  Iterate through each child found and add the Row to
				  our new DataTable.  Then, continue the recursion to
				  the level defined in TableIndex.
				 */ 

				foreach(DataRow oRow in oRows)
				{
				  this.AddRow(ref oNewTable,oRow);
				  TransformToHierarchy(TableIndex,ref oNewTable, oRow); 
				}
				 
			}
			catch (Exception e) { Console.WriteLine(e.Message); }  

		}

	private DataRow[] GetRows(int TableIndex,DataRow oRow)
		{
		   DataRow[] oRows = null;
		   DataTable oTable = null;
		   string ParentColumn = "";
		   string Query = "";

		   try
		   {
                                                            /* 
			    See if the TableIndex actually exists without
			    throwing an error.  If so, make sure it has
			    at least one row so we can retrieve our
			    .Select query string.
			   */

			   if (TableIndex < oRow.Table.DataSet.Tables.Count) 
			   {
			
				 oTable = oRow.Table.DataSet.Tables[TableIndex];

				 if (oTable.Rows.Count <1)
				  {
				    return oRows;
				  }

			   }

			   /*
				  Construct and execute the query for the .Select
				  method to retrieve the next level of child rows.
			   */

			   oTable = oRow.Table.DataSet.Tables[TableIndex];
			   ParentColumn = oTable.Rows[0]["ParentColumn"].ToString();
			   Query = oTable.Rows[0]["SQL"].ToString();
			   oRows = oTable.Select(Query + oRow[ParentColumn].ToString());

		   }
		   catch (Exception e) { throw e; } 
		   return oRows;
		}

	 
	private void AddRow(ref DataTable NewTable,DataRow OldRow)
		{
		 
			try
			{
				/*
				  Use .ItemArray to copy old row columns and
				  values to the new row.  This is a true copy
				  and not a reference pointer to the original row.
				*/

				DataRow NewRow = NewTable.NewRow(); 
				NewRow.ItemArray = OldRow.ItemArray; 
				NewTable.Rows.Add(NewRow);
			}
			catch (Exception e) { throw e; }  

		}

	 

	}
}
  
          
	    

Robbe has been a Microsoft MVP in C# since 2004.  He is also the co-founder of NullSkull.com which provides .NET articles, book reviews, software reviews, and software download and purchase advice.