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

[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 )

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

 

[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

 

[Robbe Morris [C# MVP]]

You run one query against the database and then iterate through
the DataTable using the .Select method to get it's children at
each level.

Here's a sample:

http://www.eggheadcafe.com/articles/20031014.asp

 

[Mark]

Thanks Robbie - will check it out )

Mark

You run one query against the database and then iterate through
the DataTable using the .Select method to get it's children at
each level.

Here's a sample:

http://www.eggheadcafe.com/articles/20031014.asp

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

 

[Mark]

Thanks Pete - Data's changes quite a bit so I need to stay realively live...

Mark

 

[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

 

[K R Lal]

Hello,
for this is it possible on vb.net

thanks

lal

 

[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

 

[Robbe Morris [C# MVP]]

And, the sample spanned across several tables.

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

 

[Mark]

Thanks Robbe

I will take a look at DataRelation etc.

Mark