Return Unordered List From Hierarchical Sql Data

June 29, 2023 Post a Comment

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:

Solution 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()
        );
   }
}
voidBuildTree<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:

publicpartialclassPageInfo{
   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 codevar html = sb.ToString();

Solution 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 treepublicclassPage
{
    publicstring Title {get;set;}
    publicint 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 pagepublicoverridevoidRender(HtmlTextWriter writer)
{
    writer.WriteFullBeginTag("ul");
    foreach(Page child in topPages)
    {
        RenderPage(writer, child);
    }
    writer.WriteEndTag("ul");
}

privatevoidRenderPage(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");
}

Solution 3:

This should get you started.

with x (pageID, title)
      as (
  selectcast(title asvarchar(100)),pageID
    from pages
   where parentID isnullunionallselectcast(x.title||' - '||e.title asvarchar(100)),
         e.pageID
    from pages e, x
   where e.parentID = x.pageID
  )
  select title as title_tree
    from x
   orderby1

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

Solution 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.

Solution 5:

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!

Html5 Library