TreeToTable hierarchical tables with SQLServer 2005 and C#

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

using System.Collections.Generic;

public partial class UserDefinedFunctionsInt
{
	/// <summary>
	/// Converts an hierarchical table (id-parent relations) to a flat table (id-child relations) where
	/// all subrecords are included.
	/// usage example:
	/// SELECT * FROM TreeToTable ('Organization', 'Id', 'ParentId', null)
	/// </summary>
	/// <remarks>
	/// (c) 2006 Arjan Pot - Evident Interactive
	/// </remarks>
	/// <param name="tableName">Name of the table.</param>
	/// <param name="idColumnName">Name of the id column.</param>
	/// <param name="parentIdColumnName">Name of the parent id column.</param>
	/// <param name="rootId">The root id.</param>
	/// <returns></returns>
    [SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "TreeToTableIntFillRow", TableDefinition = "Id int, ChildId int, Depth int, ChildDepth int")]
    public static IEnumerable TreeToTableInt(string tableName, string idColumnName, string parentIdColumnName, SqlInt32 rootId)
    {
        // The maximum depth that of child records that will be returned. This can be a parameter.
        int maxDepth = int.MaxValue;

		// Additional where to clause to limit records that need to be processed. This can be a parameter.
		string whereClause = null;

        // Read source table into dictionary and find the root
        Node root = null;
        SortedDictionary<SqlInt32, Node> source = new SortedDictionary<SqlInt32, Node>();
		using (SqlConnection cn = new SqlConnection("Context Connection=true"))
		{
			string sql = string.Format("SELECT {0}, {1} FROM {2} ", idColumnName, parentIdColumnName, tableName);
			if (whereClause != null && whereClause.Length > 0)
			{
				sql += string.Format(" WHERE ({0})", whereClause);
			}
			SqlCommand cmd = new SqlCommand(sql, cn);
			cmd.CommandType = CommandType.Text;
			cn.Open();
			using (SqlDataReader rdr = cmd.ExecuteReader())
			{
				while (rdr.Read())
				{
					SqlInt32 id = rdr.GetSqlInt32(0);
					SqlInt32 parentId = rdr.GetSqlInt32(1);
					Node node = new Node(id, parentId);
					source.Add(id, node);
					// If the requested root is null, the node with parent-id null is used
					if (id == rootId || rootId.IsNull && parentId.IsNull)
					{
						root = node;
					}
				}
				rdr.Close();
			}
			cn.Close();
		}
		
		// Check if there is a root
        if (root == null)
        {
            return null;
        }

        // Create hierarchy
        foreach (Node node in source.Values)
        {
            Node parent;
            if (source.TryGetValue(node.ParentId, out parent))
            {
                parent.Add(node);
            }
        }

        // Calculate the child-depth
        root.CalculateDepth(0);

        // Flatten
        RowList result = new RowList();
        root.Flatten(result, root.Id, root.Depth, maxDepth);
        foreach (Node node in root.SubNodeList())
        {
            node.Flatten(result, node.Id, node.Depth, maxDepth);
        }

        // Return result
        return result;
    }

    /// <summary>
    /// This method is called when SQL Server needs to translate and understand what
    /// the enumeration returned. Here we translate the content of the enumeration
    /// to the columns in the output table.
    /// </summary>
    /// <param name="obj">An object containing one row from the enumeration.</param>
    /// <param name="id">The id.</param>
    /// <param name="childId">The child id.</param>
    /// <param name="depth">The depth.</param>
    /// <param name="childDepth">The child depth.</param>
    public static void TreeToTableIntFillRow(Object obj, out SqlInt32 id, out SqlInt32 childId, out int depth, out int childDepth)
    {
        Row row = (Row)obj;

        id = row.Id;
        childId = row.ChildId;
        depth = row.Depth;
        childDepth = row.ChildDepth;
    }

    public class Row
    {
        public SqlInt32 Id;
        public SqlInt32 ChildId;
        public int Depth = -1;
        public int ChildDepth = -1;

        /// <summary>
        /// Initializes a new instance of the Row class.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <param name="childId">The child id.</param>
        /// <param name="depth">The depth.</param>
        /// <param name="childDepth">The child depth.</param>
        public Row(SqlInt32 id, SqlInt32 childId, int depth, int childDepth)
        {
            this.Id = id;
            this.ChildId = childId;
            this.Depth = depth;
            this.ChildDepth = childDepth;
        }
    }

    public class RowList : List<Row>
    {
    }

    public class Node
    {
        public SqlInt32 Id;
        public SqlInt32 ParentId;
        public NodeList NodeList;
        public int Depth = -1;
        public int ChildDepth = -1;

        /// <summary>
        /// Initializes a new instance of the <see cref="T:Org"/> class.
        /// </summary>
        /// <param name="id">The id.</param>
        /// <param name="parentId">The parent id.</param>
        public Node(SqlInt32 id, SqlInt32 parentId)
        {
            this.Id = id;
            this.ParentId = parentId;
        }

        /// <summary>
        /// Adds the specified node.
        /// </summary>
        /// <param name="node">The node.</param>
        public void Add(Node node)
        {
            // Add
            if (NodeList == null)
            {
                NodeList = new NodeList();
            }
            NodeList.Add(node);
        }

        /// <summary>
        /// Get a list of subnodes.
        /// </summary>
        /// <returns>All nodes below this node.</returns>
        public NodeList SubNodeList()
        {
            NodeList result = new NodeList();

            if (this.NodeList != null)
            {
                result.AddRange(this.NodeList);
                foreach (Node node in this.NodeList)
                {
                    if (node.NodeList != null)
                    {
                        result.AddRange(node.SubNodeList());
                    }
                }
            }

            return result;
        }

		/// <summary>
		/// Flattens the specified list.
		/// </summary>
		/// <param name="result">The result.</param>
		/// <param name="id">The id.</param>
		/// <param name="depth">The depth.</param>
		/// <param name="maxDepth">The maximum depth.</param>
        public void Flatten(RowList result, SqlInt32 id, int depth, int maxDepth)
        {
            if (depth > maxDepth)
            {
                return;
            }

            Row row = new Row(id, this.Id, depth, this.Depth);
            result.Add(row);

            foreach (Node node in this.SubNodeList())
            {
                Row row2 = new Row(id, node.Id, depth, node.Depth);
                result.Add(row2);
            }
        }

        /// <summary>
        /// Calculates the depth.
        /// </summary>
        /// <param name="startDepth">The start depth.</param>
        public void CalculateDepth(int startDepth)
        {
            this.Depth = startDepth;

            if (this.NodeList != null)
            {
                foreach (Node node in this.NodeList)
                {
                    node.CalculateDepth(startDepth + 1);
                }
            }
        }

        /// <summary>
        /// Returns a <see cref="T:System.String"></see> that represents the current <see cref="T:System.Object"></see>.
        /// </summary>
        /// <returns>
        /// A <see cref="T:System.String"></see> that represents the current <see cref="T:System.Object"></see>.
        /// </returns>
        public new string ToString()
        {
            return string.Format("Id={0} ParentId={1} Depth={2}", this.Id, this.ParentId, this.Depth);
        }
    }

    public class NodeList : List<Node>
    {
    }

}