Returns an unordered list of sql hierarchical data

Question

I have a table with pageId, parentPageId, header columns.

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

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

The result should look like this:

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

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

EDIT 1: I thought there was already a solution for this, but it seems not. I wanted it to be as simple as possible and run using the ASP.NET menu at all costs, because by default it uses tables. Then I have to use CSS adapters, etc.

" ASP.NET", : http://aspalliance.com/822 DataAdapter DataSet: (

- ?

Answer 1

, IHierarchyData IHierarchalEnumerable DataBind , HierarchalDataBoundControl ( , TreeView).

, #:

//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 2

linq2sql, :

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

, Pages PageInfo :

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

, , -, sql. , .

, . asp.net.

1:. , , :

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

Answer 3

.

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

:

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

XML- SQL Server SELECT... FOR XML EXPLICIT? , , .

:

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

, .

Answer 5

RexM - -, , , #. - "" . , , "pleaseSendMeTheCode" , , "".

, UL , .

: pageID, parentID, pageOrder, pageTitle

, node.

, SelectAllDataFromTable_OrderedByParentIDAscending();:

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

jsTree, .

:

, :

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

"" ( ) "", "" . "".

db parentID, pageOrder "cars", SQL-, , ( jsTree, UL), .

, , , , sql UL, , - .

Javascript, JQuery.ajax( my ), UL, , , .

Thank you very much, though, for starting my choice of a solution!