Recently I was working with some hierarchical data stored in SQL Server. The data was related to products where each product is a package which includes other packages which in turn can include individual products. The data was stored in a table where a a product row had a parentId column which indicated the parent of that product. This is a how we have stored hiearchies in tables. The solution has worked for years but is it the most efficient way to store hiearchichal data? Perhaps not. That’s why SQL Server 2008 had a data type called hierarchyId. This post is about working with  the data type. Rather than divulging details of the application I’m working on commercially, I will use Northwind database as an example to demonstrate concepts talked in here.

Basic Setup

Northwind database has a table called Employees. This table stores information about an Employee and it also stores information about who an employee reports to. This structure is maintained by using a ReportsTo column in Employees table which links to EmployeeId within the same table.

As the objective here is to store this information using hierarchyId type, I created a new table called Employees2. To make things simple I only included EmployeeId, LastName and FirstName columns other than EmployeeNode which stores the hierarchical information and PlaceINHieararchy which indicates the place of a particular employee in organizational hierarchy. For example employee A can report to Employee B who reports to Employee C, so employee A’s place in hierarchy is 3.

This is the script I used to create Employees2 table.

CREATE TABLE Employees2
(
    EmployeeNode hierarchyid NOT NULL PRIMARY KEY NONCLUSTERED,
    EmployeeId INT NOT NULL,
    LastName NVARCHAR(20) NOT NULL,
    FirstName NVARCHAR(10) NOT NULL,
    PlaceInHierarchy AS EmployeeNode.GetLevel()
)

Populating Data

To have a meaningful example I decided to insert some data into Employees2 table from Employees table. This script inserts data into Employees2 table. It may not be the best way to insert this data especially me using a temp table. But this is a dirty script which means that I’ll only run it once so I am not too worried about its elegance.

WITH Emp AS (
  SELECT '/'+CAST(EmployeeId AS VARCHAR(49)) AS PATH,
    EmployeeId
  FROM dbo.Employees b
  WHERE b.ReportsTo IS NULL
    UNION ALL
  SELECT CAST(Emp.path+'/'+CAST(b.EmployeeId AS VARCHAR(4)) AS VARCHAR(50)),
  b.EmployeeId
  FROM dbo.Employees b
    JOIN Emp ON b.ReportsTo = Emp.EmployeeId)

SELECT * INTO #temp FROM Emp

INSERT INTO Employees2
(
    EmployeeNode,
    EmployeeId,
    LastName,
    FirstName
)
SELECT CAST( t.PATH+'/' AS hierarchyId) ,
    b.EmployeeID,
    b.LastName,
    b.FirstName
FROM #temp t
    JOIN dbo.Employees b ON t.EmployeeId = b.EmployeeId
ORDER BY 1

A simple select from Employees2 table which shows data inserted into employees2 table.

Writing .NET Code

After creating a table and inserting data my first thought was how can I use Entity Framework or LINQ To SQL to do CRUD on Employees2 table. Unfortunately those are not valid options. Microsoft did not provide any support to work with hierarchyId data type in either Entity Framework or LINQ To SQL. A major draw back which I hope will be addressed in future versions of these ORMs.

Lack of support for hierarcyId data type in Entity Framework and LINQ To SQL does not mean that you don’t have any options. Good old raw ADO.NET is still around and comes in handy when working with hierarchyId data type. Another good thing is that Sql Server data type hierarchyId is available as SqlHierarchyId struct in Microsoft.SqlServer.Types assembly. To use the struct I added a reference to the Microsoft.SqlServer.Types assembly. The location of this assembly on my machine is

C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies

Next I wanted to write some code which can retrieve data from employees2 table and display it on a console. This method fetches all rows form employees2 table and displays the EmployeeNode column. Note how I store the value of EmployeeNode column into a employeeNode SqlHierarchyId struct and then I call a ToString() method on the struct.

static void GetEmployeeNodes()
{
  using (SqlConnection cn = new SqlConnection(connectionString))
  {
    string query = "SELECT * FROM employees2";
    SqlCommand cmd = new SqlCommand(query, cn);
    cn.Open();
    SqlDataReader reader = cmd.ExecuteReader();

    while (reader.Read())
    {
      SqlHierarchyId employeeNode = new SqlHierarchyId();
      employeeNode = (SqlHierarchyId)reader["EmployeeNode"];
      Console.WriteLine("Employee Node = " + employeeNode.ToString());
    }

  }
}

Output of the method looked like this.

I also wrote a method which displays ancestors for a particular employee. Getting an ancestor for a node is done by calling GetAncestor method on employeeNode which is of type SqlHierarchyId .

static void GetEmployeeDetails(int employeeId)
{
  using (SqlConnection cn = new SqlConnection(connectionString))
  {
    string query =
      "SELECT * FROM employees2 WHERE employeeId = " + employeeId.ToString();
    SqlCommand cmd = new SqlCommand(query, cn);
    cn.Open();
    SqlDataReader reader = cmd.ExecuteReader();
    string output = string.Empty;

    while (reader.Read())
    {
      SqlHierarchyId employeeNode = new SqlHierarchyId();
      employeeNode = (SqlHierarchyId)reader["EmployeeNode"];
      Console.WriteLine("Employee Node = " + employeeNode.ToString());
      output = string.Format("{0} {1}", reader["LastName"], reader["FirstName"]);

      var ancestorNode = employeeNode.GetAncestor(1);
      while (!ancestorNode.IsNull)
      {
        using (SqlConnection cn2 = new SqlConnection(connectionString))
        {
          query =
            "Select * from employees2 where EmployeeNode=hierarchyid::Parse(@node)";
          SqlCommand cmd2 = new SqlCommand(query, cn2);
          cmd2.Parameters.AddWithValue("@node", ancestorNode.ToString());
          cn2.Open();
          SqlDataReader reader2 = cmd2.ExecuteReader();
          while (reader2.Read())
          {
            output +=
              string.Format("/{0} {1}", reader2["LastName"], reader2["FirstName"]);
          }
          ancestorNode = ancestorNode.GetAncestor(1);
        }
      }
    }

    Console.WriteLine(output);
  }
}

The output I got.

A bit about SqlHierarchyId struct

SqlHierarchyId struct provides other methods like GetDescendant, GetLevel, GetRoot etc. to retrieve more information. In my personal opinion I think that Microsoft needs to do a little more work on this and make it more useful. Of course the best thing to do will be to provide support for hierarchyId data type in Entity Framework and LINQ To SQL (if it’s not already dead).

I hope you enjoyed reading the post as much as I enjoyed writing it and learning something I didn’t know.