dataset for tree structure?

[Andreas Leitner]

Hi,

how can I (can I at all?) model trees with data sets?

Given I have the following db-table:

---
Table Folder:
FolderId (Primary Key)
Name
ParentFolderId (Foreign Key to Folder/FolderId, Nullable)
---

This is a simple tree, just like a directory structure on a file system
is (it's even simpler, there are no files

Here is my try:

---
DataSet result = new DataSet ("FolderTree");
SqlDataAdapter adapter = new SqlDataAdapter("select * from [Folder]",
connection);
adapter.Fill(result, "Folder");

result.EnforceConstraints = false;

DataRelation rel = result.Relations.Add("FolderContainsFolder",
result.Tables["Folder"].Columns["FolderId"],
result.Tables["Folder"].Columns["ParentFolderId"]);
rel.Nested = true;

result.EnforceConstraints = true;

result.WriteXml ("c:\\folder_tree.xml");
---

But when I try to run this code, I get an exception saying that a
circular references has been found.

I would like to get an XML representation like this:
---
<FolderTree>
<Folder>
<FolderId>1</FolderId>
<Name>Root</Name>

<Folder>
<FolderId>2</FolderId>
<Name>A</Name>
<ParentFolderId>1</ParentFolderId>
</Folder>
<Folder>
<FolderId>3</FolderId>
<Name>B</Name>
<ParentFolderId>1</ParentFolderId>
</Folder>
<Folder>
<FolderId>4</FolderId>
<Name>C</Name>
<ParentFolderId>1</ParentFolderId>
</Folder>
</Folder>
</FolderTree>

 

[Andreas Leitner]

This is an "unnested" or flat xml representation of the data.

For this just set

rel.Nested = false;

David

Hi David,

thank you very much for answering. I know of that way. Sorry my original
post was not clear on this point. I want users to modify the resulting
XML file (and then reimport it to the db). A flat XML representation is
just too confusing for users. Isn't there any way to output a nested
version?

If nothing else helps, what do you (or others) think about
postprocessing the flat XML with an XSLT sheet? The sheet would
transform the flat file into a nested one (not sure exactly how complex
this will turn out to be , then the user modifies the nested XML file.
Before importing I would retransformate it into a non-nested XML file.

many thanks in advance,
Andreas

 

[Andreas Leitner]

Looks like i was too tired yesterday to see the obvious.
It actually works the way I described it initially, but my table
had indeed a circular reference... (which means it doesnt represent a
tree anymore of course). After removing the circular reference all
worked as expected...


regards,
Andreas