Best way to recursively populate a tree based on SQL Server 2000 backend

M

Mark

Hi Folks

I have a classic child parent relationship within the same table where an
unlimited number of levels is possible (theoretically). I have front-end
code that recursively populates a tree control by getting the children of
each node etc. etc. This makes potentially hundreds of calls to the backend.

What I would like to do (as I think it may be more efficient) is create a
single recursive stored procedure that returns all records in lineage (text
description) order, i.e. top level first, then the first child, its children
and so forth... I could then construct the tree by walking through the data
in a single pass - I hope :mad:)

Is this possible or is there a better / more appropriate way to achieve the
same thing?

I am trying to avoid multiple calls to the backend and at the same time
migrate the front end code to the sql server...

Thanks very much

Mark
 
G

Guest

Mark,

Reflective relationships can sometimes be a pain to deal with. If your
data is slow changing I would highly reccommend using xml. You will still
need to create your recursive stored procedure however instead of outputting
a recordset you will be using the "for xml" clause. Once you return the xml,
cache it and build your tree from the xml doc from now on. If your data
changing all the time then you'll have to stick with running your sproc.
There is a middle ground. You can also cache your results for a certain
period (2 hours) and only hit the db every two hours for updates. The
possiblities are endless and you'll have to decide what is the best fit for
your design.


Pete "The Greek" Orologas
Neudesic Consulting
 
M

Mark

Hi Robbie

I just had a quick look (probably too quick) I am off on site today so wont
get a chance to play properly.

At first glance it looks like you are working with a fixed number of
levels... Is this correct? I need to be able to outout any number of levels
in a single pass.

I'm starting to think a faster solution would be to build lineage strings
(from a trigger) that can be used to sort the data instead...

Thanks again

Mark
 
R

Robbe Morris [C# MVP]

The sample data has a fixed number of levels but the code
sample isn't restricted to that. The relationships in that
sample dealt with a combination of self referencing keys
and those same keys have children of different types at sub levels.

criteria
criteria
criteria
different node type
different node type
another different node type

I think your relationships may be simple enough
to just implement the DataRelation object on
a DataSet.

--
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.masterado.net
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,731
Messages
2,569,432
Members
44,835
Latest member
KetoRushACVBuy

Latest Threads

Top