asp.net - Return unordered list from hierarchical sql data

Question

I have table with pageId, parentPageId, title columns.

Is there a way to return unordered nested list using asp.net, cte, stored procedure, UDF... anything?

Table looks like this:

PageID    ParentId    Title
1         null        Home
2         null        Products
3         null        Services
4         2           Category 1
5         2           Category 2
6         5           Subcategory 1
7         5           SubCategory 2
8         6           Third Level Category 1
...  

Result should look like this:

Home
Products
    Category 1
        SubCategory 1
            Third Level Category 1
        SubCategory 2
    Category 2
Services

Ideally, list should contain <a> tags as well, but I hope I can add it myself if I find a way to create <ul> list.

EDIT 1: I thought that already there is a solution for this, but it seems that there isn't. I wanted to keep it simple as possible and to escape using ASP.NET menu at any cost, because it uses tables by default. Then I have to use CSS Adapters etc.

Even if I decide to go down the "ASP.NET menu" route I was able to find only this approach: http://aspalliance.com/822 which uses DataAdapter and DataSet :(

Any more modern or efficient way?

Answer 1

Using linq2sql you could do:

List<PageInfo> GetHierarchicalPages()
{
   var pages = myContext.PageInfos.ToList();
   var parentPages = pages.Where(p=>p.ParentId == null).ToList();
   foreach(var page in parentPages)
   {
      BuildTree(
        page, 
        p=> p.Pages = pages.Where(child=>p.pageId == child.ParentId).ToList()
        );
   }
}
void BuildTree<T>(T parent, Func<T,List<T>> setAndGetChildrenFunc)
{
   foreach(var child in setAndGetChildrenFunc(parent))
   {
       BuildTree(child, setAndGetChildrenFunc);
   }
}

Assuming you define a Pages property in the PageInfo like:

public partial class PageInfo{
   public List<PageInfo> Pages{get;set;}
}

The processing to get it on a hierarchy is happening on web application side, which avoids extra load on the sql server. Also note that this type of info is a perfect candidate to cache.

You can do the render as Rex mentioned. Alternatively you could expand a bit on this implementation and make it support the hierarchy interfaces and use asp.net controls.

Update 1: For the rendering variation you asked on a comment, you can:

var sb = new System.IO.StringWriter();
var writer = new HtmlTextWriter(sb);
// rex's rendering code
var html = sb.ToString();