@manhnguyenv

Welcome to my blog!

Parent child - Hierarchy - TreeView - Recursion

November 13, 2017 13:05

Samples

https://www.codeproject.com/Articles/1077556/Populating-TreeView-up-to-N-Levels-in-Csharp-from

https://blogs.msmvps.com/deborahk/populating-a-treeview-control-from-a-list/

Other source code for TreeView

https://www.dotnetperls.com/treeview

https://www.codeproject.com/Articles/23115/Working-with-TreeView-Controls

https://www.codeproject.com/Articles/23114/Working-with-TreeView-Controls

http://deepak-sharma.net/2012/02/08/how-to-bind-multiple-sql-server-tables-with-a-treeview-in-a-hierarchical-order/

DateTime

https://www.codeproject.com/Articles/13613/A-date-control-better-than-DateTimePicker

Sample 01

https://stackoverflow.com/questions/9380620/parent-child-hierarchy-tree-view

;
WITH relation ( Id, ParentId, Name, [level], [orderSequence] )
AS ( SELECT Id ,
ParentId ,
Name ,
0 ,
CAST(Id AS VARCHAR(20))
WHERE ParentId IS NULL
UNION ALL
SELECT p.Id ,
p.ParentId ,
p.Name ,
r.[level] + 1 ,
CAST(r.orderSequence + '_' + CAST(p.Id AS VARCHAR) AS VARCHAR(20))
INNER JOIN relation r ON p.ParentId = r.Id
)
SELECT *
FROM relation
ORDER BY orderSequence;

Sample 02

DECLARE @pc TABLE
(
Id INT ,
ParentId INT ,
Name VARCHAR(80)
);

INSERT INTO @pc
SELECT 1 ,
NULL ,
'Bill'
UNION ALL
SELECT 2 ,
1 ,
'Jane'
UNION ALL
SELECT 3 ,
1 ,
'Steve'
UNION ALL
SELECT 4 ,
2 ,
'Ben'
UNION ALL
SELECT 5 ,
3 ,
'Andrew'
UNION ALL
SELECT 6 ,
NULL ,
'Tom'
UNION ALL
SELECT 7 ,
8 ,
'Dick'
UNION ALL
SELECT 8 ,
6 ,
'Harry'
UNION ALL
SELECT 9 ,
3 ,
'Stu'
UNION ALL
SELECT 10 ,
7 ,
'Joe';

;
WITH r AS ( SELECT Id ,
ParentId ,
Name ,
depth = 0 ,
sort = CAST(Id AS VARCHAR(MAX))
FROM @pc
WHERE ParentId IS NULL
UNION ALL
SELECT pc.Id ,
pc.ParentId ,
pc.Name ,
depth = r.depth + 1 ,
sort = r.sort + CAST(pc.Id AS VARCHAR(30))
FROM r
INNER JOIN @pc pc ON r.Id = pc.ParentId
WHERE r.depth < 32767
)
SELECT tree = REPLICATE('-', r.depth * 3) + r.Name
FROM r
ORDER BY sort
OPTION ( MAXRECURSION 32767 );