MS SQL 2008 hierarchyid In Linq To SQL

I have of let been involved in trying to make my applications using the MS SQL 2008 hierarchyid data type. However am a LINQ fanatic and have found it really hard to incorporate such a good feature in any of ma application. I have however been doing some serious research and have come across multiple articles some of which were really helpful but seemed too complicated and none really seemed to focus on Stored procedures

So in this article I will try to show how you can use the tree view in VS 2008 to display records in the data base that are based on the hierarchyid data type while using the LINQ TO SQL tool. All you have to know is a little bit of TSL stored procedures (that is if u love them as I do)

First create the following table

CREATE TABLE [dbo].[Employee](
[NodeId] [hierarchyid] NOT NULL,
[NodeLevel] AS ([NodeId].[GetLevel]()),
[EmployeeId] [int] NOT NULL,
[EmployeeName] [varchar](20) NOT NULL,
[Title] [varchar](20) NULL,)

Remember to Include a primary key value for your above table. I recomend you use the NodeID column. Now since we have two unique columns we can query the table quite comfortably with either. (NodeId or EmployeeId). Plase remember that if we try to create query statements that take hierarchyid as the parameter values, we will not the able to access them using the LINQ TO SQL mapping tool provided in Visual studio 2008. Hence all our queries will be based on the employee Id (any unique field u can use to query the records within the table)

First fill the table with respective data
DECLARE @root hierarchyid;
DECLARE @child1 hierarchyid;
DECLARE @child2 hierarchyid;
DECLARE @childOf1 hierarchyid;
DECLARE @childOf2 hierarchyid;


SET @root=hierarchyid::GetRoot();
INSERT INTO Employee(NodeId,EmployeeId, EmployeeName, Title)
VALUES(@root,6,'Ndwiga','MD');


SET @child1=@root.GetDescendant(NULL,NULL)
INSERT INTO Employee( NodeId, EmployeeId, EmployeeName, Title)
VALUES(@child1,701,'Kamau','Accounts Manager')


SET @child2=@root.GetDescendant(@child1,NULL)
INSERT INTO Employee( NodeId, EmployeeId, EmployeeName, Title)
VALUES(@child2,801,'Mwangi','IT Manager')

SET @childOf1=@child1.GetDescendant(NULL,NULL)
INSERT INTO Employee( NodeId, EmployeeId, EmployeeName, Title)
VALUES(@childOf1,702,'Otieno','ASST Acc Manager')

SET @childOf2=@child2.GetDescendant(NULL,NULL)
INSERT INTO Employee( NodeId, EmployeeId, EmployeeName, Title)
VALUES(@childOf2,802,'Amani','ASST IT Manager')

Next create the following stored procedure is used to get the Employee id of the top most person within the organisation
CREATE PROCEDURE [dbo].[spGetRootNode]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from

SET NOCOUNT ON;
DECLARE @empid INT

-- Get The Root Node Employee id
SELECT @empid=e.EmployeeId FROM Employee e
WHERE e.nodeid=hierarchyid::GetRoot()

-- Return Result Set
SELECT e.EmployeeId, e.EmployeeName,e.Title FROM Employee e
WHERE e.EmployeeId=@empid
END

GO

Then create the following stored procedure to get the list of descendants for each member of the hierarchy. It takes the current known employee id as input parameter
CREATE PROCEDURE [dbo].[spGetDescendants]
@employeeId int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @managerId hierarchyid;
--Select Manager HierarchyId value
SELECT @managerId=nodeid FROM Employee e
WHERE e.EmployeeId=@employeeId;
-- Return a list of Emoloyees under the given Manager
SELECT e.EmployeeId,e.EmployeeName,e.Title FROM Employee e
WHERE e.nodeid.GetAncestor(1)=@managerId

END

Go to visual studio 2008


Then using C#
public partial class Form2 : Form
{

DataClasses1DataContext dc = new DataClasses1DataContext();
public Form2()
{
InitializeComponent();
}

private void Form2_Load(object sender, EventArgs e)
{


//Get Top Node
treeView1.Nodes.Add("Root", dc.spGetRootNode().Single ().EmployeeName);

//Get All Nodes Below the Root/Main Node
IList result = dc.spGetDescendants(6).ToList();
foreach (spGetDescendantsResult value in result)
{
treeView1.Nodes["Root"].Nodes.Add(value.EmployeeId.ToString(), value.EmployeeName);
IList temp = dc.spGetDescendants(value.EmployeeId).ToList();
foreach (spGetDescendantsResult ttValue in temp)
{
treeView1.Nodes["Root"].Nodes[value.EmployeeId.ToString()].Nodes.Add(ttValue.EmployeeId.ToString(), ttValue.EmployeeName);
}
}
}


}

However the only problem with this code is that it will allow you to interate From TOP NODE->>LEVEL ONE->>LEVEL TWO
You can however add another loop to get to the next level...am still working on the function to make it a little bit easier.
This is what your should get