ASP.Net Nova

Approach 1:

USE [StudentInfo]
GO
/****** Object:  Table [dbo].[Testcomments]    Script Date: 10/12/2009 13:51:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Testcomments](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DeviceType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ParentId] [int] NULL,
CONSTRAINT [PK_Testcomments] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (1, N'Object ', NULL)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (2, N'WebServer', 4)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (3, N'Database Server', 4)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (4, N'Server', 1)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (5, N'File Server', 4)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (6, N'Mail Server ', 4)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (7, N'IIS web server', 2)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (8, N'Virtual servers', 4)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (9, N'V S Guest OS', 8)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (10, N'VS Hosts', 8)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (11, N'New master', NULL)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (12, N'new master child', 11)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (13, N'new child1', 12)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (14, N'new child2', 13)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (15, N'new child4', 11)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (16, N'new child5', 12)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (17, N'new child6', 12)
INSERT [dbo].[Testcomments] ([Id], [DeviceType], [ParentId]) VALUES (19, N'new child7', 13)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- this can be used when we know the last child node of the tree

alter PROC [dbo].[TestHierar]
AS
BEGIN
 declare @ID int
 set @ID = 19;
--SET @ID = (SELECT max(ID) FROM dbo.TestComments )
with Hierarchy(ParentID,ID,level,Devicetype,Sort) As
 (

     Select ParentId,Id,0,DeviceType,cast(DeviceType as nvarchar(1024))
     from testcomments
where  Id= @ID
     union all
    select dt.ParentId, dt.ID, level+1,dt.DeviceType , cast(Sort +'|'+dt.Devicetype as nvarchar(1024))
    from TestComments dt inner join hierarchy h on dt.Id = h.parentId
)
,HierarchyReverse(ParentID,ID, level, DeviceType, Sort) as

(
select parentId, Id, 0, DeviceType, Cast(DeviceType as nvarchar(1024))
from hierarchy
where parentId is null
union all
Select h.parentId,h.id,hr.LEVEL+1, h.deviceType, cast(hr.Sort + '|' +h.sort as nvarchar(1024))
from Hierarchy h inner join HierarchyReverse hr on h.ParentId = hr.ID
)
select Id, ParentId, DeviceType, Level,Sort
from HierarchyReverse
order by sort


END