Hierarchy from a single table

[C P]

I'm just getting into ADO.NET and C#, and am playing with some options to
move my old Win32 app to this new platform.

My app displays a questionairre where the questions are organized in a
hierarchy (much like a directory structure). In my current app, everything
is fixed at 4 levels, and is stored in 4 tables. (i.e. the actual questions
are always at 'level 4', and levels 1, 2 and 3 group the questions).

I was hoping to be more dynamic and store this in a single table. I've set
up a table where each record can reference a parent. Every record has a
recId field that is unique. Each record optionally fills a parentRecId
field that points to it's parent, or is null (if this is a root). ...it's a
basic tree structure.

I was hoping with XML etc. and ADO.NET, I'd be able to construct this
automatically into a hierarchy from the single flat table. I've tried using
a data relation to link the parentRecId fields to the recID fields.
However, the best I can get is to have the parent records, and one level of
children, but I can't get the grandchildren and greatgrandchildren etc. to
happen automatically.

I've played around with creating a schema, but I don't really know what I'm
doing here. Is what I want to do even possible (without a lot of manual
work)? Maybe there's a SQL FOR XML statement that could build a
hierarchical XML file from a single flat table? I suppose I could build
some recursive functions to build the XML manually, but I'm hoping that
VS.NET/XML/ADO.NET already supports this.

Thanks,
Chris

 

[C P]

Ok, it looks like I can do this after all... sort of. I'm almost getting
there, but I'm apparently violating some sort of constraint. I get errors
if I preview my data in a data adapter, or if I try to run my code.
Actually, in the data adapter preview, it gives a warning, then proceeds to
show the data I'd expect.

I have a data table that has a relationsihip with itself. The data table
has a single primary key (qId). The data table has a relationship with
another of it's fields (qParentId). qParentId is the Foreign key, and
points to it's parent record. qID is never null. qParentId may be null for
the tree roots.

I'm obviously setting something up wrong, but I'm not sure what.

Help!?!

 

[C P]

I think the problem was that contraints were being validated as data was
being loaded, and because of the order of loading the data, a child may have
been loaded before a parent. When I turn off the contraint validation
everything works great! I guess I need to figure out how to validate the
constaints only after all the data has been loaded in the XML.