从分层SQL数据返回无序列表 [英] Return unordered list from hierarchical sql data

问题描述

我有PAGEID表，parentPageId，标题栏。

I have table with pageId, parentPageId, title columns.

有没有办法使用返回无序嵌套列表asp.net，CTE，存储过程，UDF ... ...什么吗？

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

表看起来是这样的：

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

在理想情况下，列表应包含＆LT; A＆GT; 标签为好，但我希望我可以添加它自己，如果我找到一个方法来创建 ＆LT; UL＆GT; 列表

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.

编辑1：我想已经有一个解决方案，但似乎没有。我想保持它的简单越好，使用ASP.NET菜单不惜任何代价逃避，因为它使用默认的表。然后，我必须使用CSS适配器等。

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.

即使我决定去下ASP.NET菜单路线我只能找到这种方法： HTTP： //aspalliance.com/822 它使用DataAdapter的数据集和：（

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?

推荐答案

最好的做法是为此使用的 IHierarchyData 和的 IHierarchalEnumerable 并调用DataBind方法来从的 HierarchalDataBoundControl （这是喜欢的 TreeView控件）。

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

但是，让我们尝试一个快速和肮脏的，不是，尤其是高效的，简单的例子，在C＃中：

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

这篇关于从分层SQL数据返回无序列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！