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

If grabbing the entire table for each query is not so much of a big deal (ie, the total number of rows in the table is less than say 1000), then you could just do that, ie

SELECT
  PageID,
  ParentId,
  Title
FROM
  pages

And then as you grab these results back, and as you do so dump them into a multidimensional array.

Warning: pseudocode!

For each row returned:
  Add this to Row[PageID]
  If ParentId is not null:
    Add this row to Row[ParentRow]->childIDs

Then you would recurse over that multidimensional array/object in order to build your UL.

BuildListItem(Item)
  Build the HTML for this list item
  If Item->childIDs is not empty:
    Build the HTML for a <ul> nested inside the list item.
    For each Item->childIDs as someiterator:
      BuildListItem(Item->childIDs[someiterator])    // recurse
    Close the HTML for this nested <ul>
  Close the HTML for this list item

Answer 2

Best practice would be to do this using IHierarchyData and IHierarchalEnumerable and DataBind to a custom control which inherits from HierarchalDataBoundControl (this is the base for controls like TreeView).

However, let's try for a quick-and-dirty, not-especially-efficient, simple example in c#:

//class to hold our object graph in memory
//this is only a good idea if you have a small number of items
//(less than a few thousand)
//if so, this is a very flexible and reusable way to represent your tree
public class Page
{
    public string Title {get;set;}
    public int ID {get;set;}
    public Collection<Page> Pages = new Collection<Page>();

    public Page FindPage(int id)
    {
        return FindPage(this, id);
    }

    private Page FindPage(Page page, int id)
    {
        if(page.ID == id)
        {
            return page;
        }
        Page returnPage = null;
        foreach(Page child in page.Pages)
        {
            returnPage = child.FindPage(id);
            if(returnPage != null)
            {
                break;
            }
        }
        return returnPage;
    }
}

//construct our object graph
DataTable data = SelectAllDataFromTable_OrderedByParentIDAscending();
List<Page> topPages = new List<Page>();
foreach(DataRow row in data.Rows)
{
    Page page = new Page();
    page.Title = (string)row["Title"];
    page.ID = (int)row["PageID"];
    if(row["ParentID"] == null)
    {
        topPages.Add(page);
    }
    else
    {
        int parentID = (int)row["ParentID"];
        foreach(Page topPage in topPages)
        {
            Page parentPage = topPage.FindPage(parentID);
            if(parentPage != null)
            {
                parentPage.Pages.Add(page);
                break;
            }
        }
    }
}

//render to page
public override void Render(HtmlTextWriter writer)
{
    writer.WriteFullBeginTag("ul");
    foreach(Page child in topPages)
    {
        RenderPage(writer, child);
    }
    writer.WriteEndTag("ul");
}

private void RenderPage(HtmlTextWriter writer, Page page)
{
    writer.WriteFullBeginTag("li");
    writer.WriteBeginTag("a");
    writer.WriteAttribute("href", "url");
    writer.Write(HtmlTextWriter.TagRightChar);
    writer.Write(page.Title);
    writer.WriteEndTag("a");
    if(page.Pages.Count > 0)
    {
        writer.WriteFullBeginTag("ul");
        foreach(Page child in page.Pages)
        {
            RenderPage(writer, child);
        }
        writer.WriteEndTag("ul");
    }
    writer.WriteEndTag("li");
}

Answer 3

This should get you started.

with x (pageID, title)
      as (
  select cast(title as varchar(100)),pageID
    from pages
   where parentID is null
   union all
  select cast(x.title||' - '||e.title as varchar(100)),
         e.pageID
    from pages e, x
   where e.parentID = x.pageID
  )
  select title as title_tree
    from x
   order by 1

Output:

TITLE_TREE
Home
Products
Services
Products - Category 1 
Products - Category 2
Products - Category 2 - Subcategory 1 
Products - Category 2 - Subcategory 1 - Third Level Category 1
Products - Category 2 - Subcategory 2

Answer 4

Have you considered getting XML output from SQL Server using SELECT ... FOR XML EXPLICIT? Your data seems set up perfectly for that.

For an example:

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

If you want to pursue I could work through an example.

Answer 5

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();

Answer 6

RexM - firstly I must state that I'm a front-end developer so can't even touch you for skill and knowedge of coding C#. However - I did implement your solution using the Page object and encountered a problem. Yes, sorry I'm a "pleaseSendMeTheCode" leech in this instance, but neverless, thought it was important to detail the "bug".

I'm building a site that uses a nested UL to display menu items and allows the user to re-sort the menu however they want.

My menu has the following data fields: pageID, parentID, pageOrder, pageTitle

Page order refers to the order in which the pages appear in a node.

So my query for SelectAllDataFromTable_OrderedByParentIDAscending();was:

SELECT * FROM [pages] ORDER BY [parentID] ASC, [pageOrder] ASC

I then use jsTree to make the menu items draggable and droppable.

I re-ordered a few pages and discovered a bug:

Say my structure is like so:

home
  cars
    usa
      muscle cars
      suvs
    europe
  colours
  directions
    vertical
    horizontal
      up
      down

If I move "cars" (and all it's children) inside "down", the children of "cars" no longer display in the menu. That's the "bug".

I have checked the db and parentID and pageOrder are all correct under "cars", I also tried changing my SQL query, starting from scratch, all sorts of testing directly on the DB (all the above with jsTree turned off so I can see the basic nested UL) - but with no success.

Just wondering, as I've seen other forums pointing to this page for solutions to turning hierarchical sql data into nested UL's, it might be worth somebody looking into it.

As my whole site is based on the use of Javascript I've now implemented a Jquery.ajax solution (which, very badly commented, is on my site here) to build the nested UL but as I said, just flagging as potential problem.

Thanks very much though for a kick start in my own finding of a solution!