Nested loops on a self referencing table

[Andrew Banks]

I have a database table with the following kind of structure

CategoryID (int)
Parent CategoryID (int)
CategoryName (varchar)

The table has a self referencing relationship between ParentCategoryID
CategoryID.

In C#.NET how can I loop through this table and list all categories,
with their sub-categories all the way down the hierarchy?

For example

Category 1
- Sub Category 1
--Sub Sub Category 1
--Sub Sub Category 2
--Sub Sub Category 3
Category 2
- Sub Category 1
--Sub Sub Category 1
- Sub Category 2
- Sub Category 3
--Sub Sub Category 1
--Sub Sub Category 2
--Sub Sub Category 3

I know I need to use a nested loop in some way but not sure how to take
this forward

Thanks in advance

 

[Guest]

Andrew,
Load the table into a dataTable and try something like this

ListItems(dataTable, 0);
..
..
..
..
public void ListItems(DataTable dataTable, int categoryID)
{
foreach (DataRow dataRow in dataTable.Rows)
{
if ((int)dataRow[ "Parent CategoryID" ] == categoryID)
{
ListItems(dataTable, (int)dataRow[ "CategoryID" ]);
}
}
}

All the Best,
Phil.