How to put a self-reference table into hierarchical objects : The Official Microsoft ASP.NET Forums

Reply

How to put a self-reference table into hierarchical objects

Apr 15, 2008 09:21 AM|LINK

Note! this is a question focussed on the data transfer between Data Layer and Business Logic Layer.
So NO Presentation Layer kind of answers!

I have a self-referenced datatable, with columns:

GroupID
GroupName
Parent

How do I fill a hierachical object named "Group", with the records from this datatable.

I have to make some kind of loop to analyse the records in the datatable, but do not know where to start.

Who can help?

Johan Theunissen
MCPD, MCSE, MCTS BizTalk 2006

==============================

Please mark the most helpful reply/replies as "Answer".

Reply

Re: How to put a self-reference table into hierarchical objects

Apr 15, 2008 03:03 PM|LINK

I'd start with a collection of the group records pulled straight from the database. Then, iterate that collection adding nodes either to the root (if no Parent specified) or the appropriate parent node. If the appropriate parent doesn't exist in the tree yet, add that group to an orphans collection. Once you're done repeat the addition using the orphans collection until the number of orphans doesn't change between attempts.

You can decide at that point whether or not the orphans are an issue and you should have the heirarchical structure that you want.

Reply

lchrennew

Member

361 Points

79 Posts

Re: How to put a self-reference table into hierarchical objects

Apr 17, 2008 08:09 AM|LINK

JohanNL

Note! this is a question focussed on the data transfer between Data Layer and Business Logic Layer.
So NO Presentation Layer kind of answers!

I have a self-referenced datatable, with columns:

GroupID
GroupName
Parent

How do I fill a hierachical object named "Group", with the records from this datatable.

I have to make some kind of loop to analyse the records in the datatable, but do not know where to start.

Who can help?

Hi,

You should make an Group entity LAZY LOAD its Parent property and Children property. Don't load these self-referenced property when the entity being initializing, and when Parent or Children being call, call the DAL to get the related data from DB.

Good Luck

Reply

DigiMortal

Contributor

5658 Points

939 Posts

MVP

Re: How to put a self-reference table into hierarchical objects

Apr 17, 2008 09:05 PM|LINK

Well...when asking data from groups table (I took this table from your example) you have to ask all top-level groups. You are asking groups from DAL class or classes - depends on how you organized your DAL. Now, if you need to now groups of some parent group you have to call special method in DAL and give current group's ID as argument. This method returns you a list of subgroups. The other way is to write some lazy loading support to your class. Instead of asking subgroups from DAL directly you have property for subgroups. In this property you will check if child groups list is null or not. If it is null then it is uninitialized and you have to ask DAL for data. This way you are hiding groups/subgroupslogic to group object and you don't have to expose this functionality to other objects. And in the same time you have also lazy loading support there.

Don't forget to mark solution providing post as "Answered".

Also visit my ASP.NET blog or follow me @ Twitter:twitter.com/gpeipman

Reply

kiransdusk

Member

17 Points

13 Posts

Re: How to put a self-reference table into hierarchical objects

Apr 18, 2008 07:00 AM|LINK

Hi JohanNL,

Let me explain my approach toward solving the issue. Please tell me whether this helps you.

For convenience of explaining iam noting down my column names below.

[CategoryID]

,[CategoryName]

,[ParentID]

[DisplayOrderNo] - This column maintains the display order in the whole table.

If you wish to creawte the table here is the create code.

USE [MyDB]
GO
/****** Object: Table [dbo].[FCategories] Script Date: 04/18/2008 12:09:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FCategories](
[CategoryID] [uniqueidentifier] NOT NULL,
[CategoryName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ParentID] [uniqueidentifier] NULL,
[DisplayOrderNo] [int] NULL,
CONSTRAINT [PK_FCategories] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Sample Data Values in Table

CategoryId	CategoryName	ParentId	DisplayOrderNo
dc8d3121-d341-40be-a9b4-1fb025cc1d2c	Learn Vista	8599f454-5839-4124-ba2d-5b2cf122f72b	4
1cd26b41-6223-4081-ae4a-38bc144314c8	Antivirus	0867b723-96f8-4481-adbe-e69e82ed29a6	2
bffa400c-6451-4192-a877-394d29fda4bb	Vista Resources	8599f454-5839-4124-ba2d-5b2cf122f72b	4
a67de5c1-9254-45f0-8deb-3daea20e0d68	Hardwares	NULL	1
8599f454-5839-4124-ba2d-5b2cf122f72b	Win Vista	477969f6-bf2b-454e-814f-cbac6d8621e3	3
bf900797-007c-4667-be09-6fe394ff7698	Find a Teacher	dc8d3121-d341-40be-a9b4-1fb025cc1d2c	5
c5dc19ad-e2c7-42a1-bab7-8f03e5496d10	e Learning	dc8d3121-d341-40be-a9b4-1fb025cc1d2c	5
16f789b3-2e58-4d22-84cf-a8e9dc76e77f	Win 98	477969f6-bf2b-454e-814f-cbac6d8621e3	3
477969f6-bf2b-454e-814f-cbac6d8621e3	Operating Systems	0867b723-96f8-4481-adbe-e69e82ed29a6	2
1c562863-c8ab-4d7b-8516-d4096a31af14	Order Books	dc8d3121-d341-40be-a9b4-1fb025cc1d2c	5
0867b723-96f8-4481-adbe-e69e82ed29a6	Softwares	NULL	1
NULL	NULL	NULL	NULL

Generate a sql query which can return the hierarchical structure of the data within the Table

DECLARE @Qry NVARCHAR(4000)

DECLARE @Qry1 NVARCHAR(4000)

SELECT @Qry = 'SELECT CategoryID, CategoryName, ParentID', @Qry1 = ''

DECLARE Heirarchy CURSOR FOR SELECT DISTINCT DisplayOrderNo FROM Fcategories WHERE DisplayOrderNo > 1

DECLARE @Level INT

OPEN Heirarchy

FETCH NEXT FROM Heirarchy INTO @Level

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @Qry = @Qry + ', (SELECT CategoryID, CategoryName, ParentID '

SELECT @Qry1 = 'FROM Fcategories F' + convert(varchar, @Level) +' WHERE F' + convert(varchar, @Level-1) + '.CategoryID = F' + convert(varchar, @Level) + '.ParentID FOR XML AUTO, Type) '

+ @Qry1

FETCH NEXT FROM Heirarchy INTO @Level

END

CLOSE Heirarchy

DEALLOCATE Heirarchy

select @Qry = @Qry + @Qry1

SELECT @Qry = @Qry + ' FROM Fcategories F1 WHERE F1.DisplayOrderNo = 1 for xml auto, root(''Categories'')'

select @qry

exec (@Qry)

go

On executing the @Qry the result set displays the data in an xml format ordered in the heirarchical format.

This Xml could be either fetched to the code behind and loaded in a tree view control (VS 2005) for displaying in the way you wanted it. Edting the databindings will be needed to bind the data accordingly in the Treeview Control.

<F2 CategoryID="1CD26B41-6223-4081-AE4A-38BC144314C8" CategoryName="Antivirus" ParentID="0867B723-96F8-4481-ADBE-E69E82ED29A6"/>

<F2 CategoryID="477969F6-BF2B-454E-814F-CBAC6D8621E3" CategoryName="Operating Systems" ParentID="0867B723-96F8-4481-ADBE-E69E82ED29A6">

<F3 CategoryID="8599F454-5839-4124-BA2D-5B2CF122F72B" CategoryName="Win Vista" ParentID="477969F6-BF2B-454E-814F-CBAC6D8621E3">

<F4 CategoryID="DC8D3121-D341-40BE-A9B4-1FB025CC1D2C" CategoryName="Learn Vista" ParentID="8599F454-5839-4124-BA2D-5B2CF122F72B">

<F5 CategoryID="BF900797-007C-4667-BE09-6FE394FF7698" CategoryName="Find a Teacher" ParentID="DC8D3121-D341-40BE-A9B4-1FB025CC1D2C"/>

<F5 CategoryID="C5DC19AD-E2C7-42A1-BAB7-8F03E5496D10" CategoryName="e Learning" ParentID="DC8D3121-D341-40BE-A9B4-1FB025CC1D2C"/>

<F5 CategoryID="1C562863-C8AB-4D7B-8516-D4096A31AF14" CategoryName="Order Books" ParentID="DC8D3121-D341-40BE-A9B4-1FB025CC1D2C"/>

</F4>

<F4 CategoryID="BFFA400C-6451-4192-A877-394D29FDA4BB" CategoryName="Vista Resources" ParentID="8599F454-5839-4124-BA2D-5B2CF122F72B"/>

</F3>

<F3 CategoryID="16F789B3-2E58-4D22-84CF-A8E9DC76E77F" CategoryName="Win 98" ParentID="477969F6-BF2B-454E-814F-CBAC6D8621E3"/>

</F2>

</F1>

</Categories>

Red in Heart...

Edited by kiransdusk on Apr 18, 2008 07:05 AM
Edited by Benson Yu - MSFT on Apr 21, 2008 06:56 AM
Edited by JohanNL on May 06, 2008 08:11 AM

Reply

DigiMortal

Contributor

5658 Points

939 Posts

MVP

Re: How to put a self-reference table into hierarchical objects

Apr 18, 2008 07:15 AM|LINK

Seems to complex to me. I would prefer more simple code without dynamic SQL generation in stored procedures. Can you imagine how stored procedures with dynamic sql look like if system grows and queries are going to be more complex? My other question is: why not retrieve only those categories you really need at time? Sooner or later, expecting you are writing application that is in use a long time, complexity grows, so does the amounts of data, and you need to optimize your system. Why not optimize it when you begin and optimize this way you don't waste your time on any hacks you have throw away later.

Don't forget to mark solution providing post as "Answered".

Also visit my ASP.NET blog or follow me @ Twitter:twitter.com/gpeipman

Reply

kiransdusk

Member

17 Points

13 Posts

Re: How to put a self-reference table into hierarchical objects

Apr 18, 2008 10:36 AM|LINK

.

DigiMortal
Seems to complex to me. I would prefer more simple code without dynamic SQL generation in stored procedures.

I stand by you on your comments on my posting. Even though our problems were similar they seems to be different at the implemetation side. I wanted the categories to be loaded to a tree view from an xml and this is not going to change often. so whenever i have a new category added to it i shall create a new xml and update it. Ocne created the xmlfile will stay as the datasource to my treeview to the next upadte. But in your case things are gng to be more complex since it demands dynamic generation everytime... Anyway Thanks for the feedback. Shall update if any more ideas pour in...

Red in Heart...

Reply

JohanNL

Participant

1644 Points

1227 Posts

Re: How to put a self-reference table into hierarchical objects

May 06, 2008 08:13 AM|LINK

Is this the scenario where I should use the Composite Pattern for the OO?

Johan Theunissen
MCPD, MCSE, MCTS BizTalk 2006

==============================

Please mark the most helpful reply/replies as "Answer".

Reply

vladb

Member

167 Points

79 Posts

Re: How to put a self-reference table into hierarchical objects

Jun 19, 2008 04:35 PM|LINK

So let's say you have this structure:

/*
John
	- Ben
	. - Alen
	. . - Mark
	. - Will
	. - Franc
	. . - Hu
	. . . - Eve
	. . . - Peter
	- Max
*/
CREATE TABLE Employee
(
	Name varchar(100) NOT NULL PRIMARY KEY,
	ManagerName varchar(100) REFERENCES Employee (Name)
)
INSERT INTO Employee
SELECT 'John', NULL


INSERT INTO Employee
SELECT 'Ben', 'John'


INSERT INTO Employee
SELECT 'Max', 'John'


INSERT INTO Employee
SELECT 'Will', 'Ben'


INSERT INTO Employee
SELECT 'Alen', 'Ben'


INSERT INTO Employee
SELECT 'Franc', 'Ben'


INSERT INTO Employee
SELECT 'Mark', 'Alen'


INSERT INTO Employee
SELECT 'Hu', 'Franc'


INSERT INTO Employee
SELECT 'Eve', 'Hu'


INSERT INTO Employee
SELECT 'Peter', 'Hu'

Now you can traverse it as:

WITH EmployeeCTE  (Name, Level, Path, Parent)
AS
(
 SELECT 
	Name, 
	1 AS Level, 
	CAST('/'+Name as nvarchar(max)) as Path,
	CAST(NULL as nvarchar(max)) as Parent
 FROM Employee
 WHERE ManagerName IS NULL

 UNION ALL

 SELECT 
	e.Name, 
	x.Level + 1 AS Level, 
	x.Path + '/' + e.Name as Path,
	REVERSE(SUBSTRING( REVERSE(x.[Path]) ,0 , CHARINDEX( '/', REVERSE(x.[Path])) )) as [Parent]
 FROM Employee e
 JOIN EmployeeCTE x ON x.Name = e.ManagerName
)
SELECT Name, Level, Path, Parent
FROM EmployeeCTE
ORDER BY Path

And if you want the reverse - to send hierarchical data to sql to as is described here:

http://blog.bodurov.com/Post.aspx?postID=30

blog.bodurov.com

Edited by vladb on Jun 19, 2008 04:36 PM

Reply

latticesoft

Participant

1493 Points

335 Posts

Re: How to put a self-reference table into hierarchical objects

Jun 19, 2008 08:17 PM|LINK

Let's keep it simple. Recursive is your friend.

Li Xin
Get it Done! Simple is Best!
LatticeFramework Studio - model-driven template-based code generator

How to put a self-reference table into hierarchical objects RSS

10 replies

JohanNL

How to put a self-reference table into hierarchical objects

stiletto

Re: How to put a self-reference table into hierarchical objects

lchrennew

Re: How to put a self-reference table into hierarchical objects

JohanNL

DigiMortal

Re: How to put a self-reference table into hierarchical objects

kiransdusk

Re: How to put a self-reference table into hierarchical objects

DigiMortal

Re: How to put a self-reference table into hierarchical objects

kiransdusk

Re: How to put a self-reference table into hierarchical objects

DigiMortal

JohanNL

Re: How to put a self-reference table into hierarchical objects

vladb

Re: How to put a self-reference table into hierarchical objects

latticesoft

Re: How to put a self-reference table into hierarchical objects

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft