Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
649 views
in Technique[技术] by (71.8m points)

asp.net - Return unordered list from hierarchical sql data

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...