This thread is for discussions of Tree structures in ASP.NET and SQL Server.

First of all, great article.  I agree with it and would like your feedback in regards to simplifying your approach on the node levels.  You use the 'lineage' varchar field to track where a node is in regards to order and depth.  Considering you have already created a depth field, and you have a unique node (somewhere in the structure), you have 2 out of the 3 pieces that you need to know where the node is.  You are simply missing the placement of the node at its depth.   So, why wouldn't you use an int field named NodeOrder instead of the lineage field?  This would help the doubling of data, and would be easier to maintain.  There are many similar fields in other databases that you could rob code from.  Just look for databases that use the 'sortorder' field.  By using a sort order index, you can fetch for a particular node, know its unique id, parent id, its depth and at what placement the node will sit.  and furthermore, you will know this for each node you retrieve in your select, which will in effect, create the /1/2/6/ .  The slash being the depth, and the number being the position and the unique id is in behind the postition number.... in other words (/position(unique_id)/)

What do you think of this?

Regards,

Jay

Hi, very interesting article.

I have run into a number of problems wiht the sample source code though.

1. The SQL Statement will not fully execute. I get the following error:

Server: Msg 271, Level 16, State 1, Procedure dfTree_UpdateTrigger, Line 10
Column 'depth' cannot be modified because it is a computed column.

2. The createNodes button was not on the page for TreeDemoA.


3. when I try to run TreeDemoD I get the following error:

id is not a parameter for procedure dfTreeGetTree.

I don't know if this is due to the SQL statement failing or what.

Any advice on getting the code running properly would be much appreciated.

Thanks,
Max

Hey,

Apologies! Thanks for pointing that out - I think I've fixed the sample download now - if you could try downloading it again, and let me know how it goes, that would be great

Cheers,

~ James

Hi, I still have all the same problems with the sample code.

TREEDEMOA:
----------------

There is a missing Button for "CreateNodes" on the skin for TreeDemoA.


TREEDEMOD:
---------------

Get SQL error for unknown "id" parameter on execution of an SPROC.

I looked into this and the problem is in SqlServerTreeProvider.cs

There are a bunch of places where the SQL Params do not have the @ before the parameter names. so the "id" should be "@id"

After fixing these too, I can get TreeDemoD to run.


SQL SCRIPT:
----------------

I get the following error on the execution:

Server: Msg 271, Level 16, State 1, Procedure dfTree_UpdateTrigger, Line 10
Column 'depth' cannot be modified because it is a computed column.

It is not creating the UpdateTrigger. The problem here is that the Computed Column seems to be a late addition to the design and the code that originally updated the "depth" column in the UpdateTrigger has not been removed from the UpdateTrigger (corresponding code WAS removed from the InsertTrigger)


Also, there is a reference in the file to the SPROC dfTreeGetValidParents, but that SPROC is not included in the SQL Script

       public ArrayList GetValidParents(int rootID, int uniqueID)
       {
           return ProcessList("dfTreeGetValidParents",
               new SqlParameter("@rootId",rootID),
               new SqlParameter("@id",uniqueID));
       }


If you could update the Zip package with these changes it be most helpful for others trying the download.

Again, very interesting article, and now I'm going to dive in and see how I can use the ideas and techniques in my projects. javascript:smilie('')
smile

Thanks,
Max

Hi - these problems have been fixed in the new ZIP file I uploaded. Are you sure your browser is not caching the old ZIP file?

huh... isn't that interesting. Firefox WAS caching the zip I guess. Who knows... but I fired up Internet Explorer and I got the proper version. Thanks for the fixes, everything worked straight off when I fired it up this time. ;-)

I do have a question though. In the original version of the zip file, you used  the computed columns. Intuitively it makes sense to not have to comput this each time we touch a row in the DB (as you say in the article), but have you done any tests (or heard anything back from anyone) as to the performance gain by calculating this via the triggers as opposed to the computed column approach.

I'm just interested in any different thinking on the idea. Like I said before I've never really worked with computed columns before and while they don't really strike me as a good idea in general. better to do somehting once the first time, rather than recomputer on each access. I was wondering if you might know of any circumstances where computed columns would be the best approach (besides the obvious storage space considerations).

Again, great article. I have done a bunch of different projects with hierarchies like this, and this article has a lot of good ideas for dealing with this kind of data.

Thanks,
Max

Hello again,

I was playing around with the updating trigger and I uncovered another little problem.

It seems that I need to run the UPDATE statement twice in order to get the trigger to execute properly and update the depth and lineage columns.

I have been playing around with the trigger to see if I could correct this, but haven't come up wiht anything yet.

I guess its not a big deal as it isnt that hard to just run the statement twice, but do you have any ideas on how to get it working in a single pass?

Thanks,
Max

Strange. I'll look into that and let you know....

The problem is fixed. Change the line in the UPDATE trigger that reads

INNER JOIN deleted old ON child.lineage LIKE old.lineage + '%'

to

INNER JOIN inserted old ON child.lineage LIKE old.lineage + '%'

I've updated the download.

A much better way of doing this is to use a stack:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_5yk3.asp

No triggers needs, no column needed to track depth...

James:

That was good reading!

Good Job!

I'm not sure this is actually much better performance wise...

Yes, the method I suggest requires some overhead when adding to the tree - but SELECTing from the tree is fast. Looking at the recursive function there, with multiple SELECT and DELETE statements... just to retrieve the tree, doesn't look especially nice to me when compared to

SELECT t.* FROM dfTree t ORDER BY t.lineage

...

It's very performant.  Feel free to try it with a 10000 node, 10 level deep tree.  Works fine.

HI. Is there a way to sort the Hierarchies? You used something like this:
''''''''''''''''''''''''''
CREATE PROCEDURE dfTreeGetSubChildren ( @id INT, @depth INT ) AS
SELECT t.* FROM dfTree AS children INNER JOIN dfTree AS actualNode
   ON children.lineage LIKE actualNode.lineage + '_%'
   WHERE actualNode.id = @id
ORDER BY children.lineage, children.name
'''''''''''''''''''''''''

Since this do order by children.lineage, children.name, and the children.lineage is unique, the children.name order will not run.
Is there a way to overcome this? Example, sort by the start of lineage except the last id /1/3/5 --> /1/3 this way we could sort by this field, cobined with name. But I do not know how to overcome this with T-SQL.

I would appreciate if anyone could help me.

Rgs
Vidar