how to bind asp.net controls using a self join and items tables "hierarchically structure"?

Question

I have the following tables:
1-Categories:
-CategoryID
-CategoryName
-ParentID

2-Items:
-ItemId
-ItemName
-CategoryID

categories can be in a hierarchically view, with many children categories inside each other.
And any the last child category can have items, so just the last category child will show items under it.
The view will be like a treeview and when clicking on an item it will go to a new page.

I wrote it like this:

<asp:Repeater runat="server" ID="rptCategories" OnItemDataBound="rptCategories_ItemDataBound" >
    <ItemTemplate>
        <div id="type_<%# Eval("Type") %>">
            <p >
                <a id="<%# Eval("CategoryID") %>" class="Categories">
                    <%# Eval("CategoryName") %></a>
            </p>
            <div id="ProjectsDiv_<%# Eval("CategoryID") %>" class="Projects">

                <asp:Repeater ID="rptProjects" runat="server">
                    <ItemTemplate>
                        <a id="<%# Eval("ProjectID") %>" class="ProjectLink">
                        <%# GetProject(Eval("ProjectID"))%>

                        </a>
                    </ItemTemplate>
                </asp:Repeater>
            </div>
        </div>
    </ItemTemplate>
</asp:Repeater>

but this represent a one level structure,
My question is how to make it as a tree view?

Answer 1

This is going to be complicated with a repeater if you don't know how deep the hierarchy goes. Why don't you just use an asp.net TreeView?

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.treeview.aspx

You can use asp.net ajax and an updatepanel to achieve the effect you want without writing any javascript yourself.

Answer 2

To do this you need two classes - one for a tree-like control, and the other which feeds the data to the control in a hierarchy.

• For the control, you could just use something like a TreeView, or you could write your own by inheriting from HierarchalDataBoundControl.

• For the data source, since you have a very specific and custom format of your data, you need to write a class which implements IHierarchalDataSource. That class will become the DataSource of your control, and when you call DataBind, it will feed the control the data in a hierarchal manner.

Here is a tutorial on how to build a HierarchalDataBoundControl from scratch.

Another option is to just use recursion and be quick-and-dirty about it. I answered a related question on this here.

Answer 3

Oracle had a CONNECT BY statement that was designed for hierarchical queries. At least as of Sql Server 2005 I don't believe there was a direct equivalent so you had to fake it with stored procedures...

Answer 4

if (!IsPostBack) { Populate(TreeView1.Nodes);

        }
    }

  protected  void TreeView1_SelectedNodeChanged(object sender, EventArgs e)
    {
        TreeNode node = TreeView1.SelectedNode;
        string nodeId = node.Value;
        string nodeName = node.Text;

    }
    private void Populate(TreeNodeCollection nodes)
    {
        TreeNode parentNode = null;
        foreach (Category1 category in GetProductCategories())
        {
            parentNode = new TreeNode(category.Name, category.Id.ToString());
            if (category.Subcategories != null)
            {
                SubNodes(category, parentNode);
            }
            parentNode.Collapse();
            TreeView1.Nodes.Add(parentNode);
        }
    }
    private void SubNodes(Category1 category, TreeNode childNode)
    {
        foreach (Category1 c1 in category.Subcategories)
        {
            TreeNode subchildnode = new TreeNode(c1.Name, c1.Id.ToString());
            childNode.ChildNodes.Add(subchildnode);
            if (c1.Subcategories != null)
            {
                SubNodes(c1, subchildnode);
            }
        }
    }
    protected void TreeView1_TreeNodePopulate(object sender, TreeNodeEventArgs e)
    {
    }
    #region unwanted

    // Show all checkboxes

    //TreeView1.ShowCheckBoxes = TreeNodeTypes.All;
    //protected void TreeView1_TreeNodePopulate(object sender, TreeNodeEventArgs e)
    //{
    //    PopulateSubLevel(Int32.Parse(e.Node.Value), e.Node);
    //}
    //private void PopulateSubLevel(int parentid, TreeNode parentnode)
    //{
    //    Populate1( parentid,parentnode.ChildNodes);
    //}
    //private void Populate1(int k1,TreeNodeCollection nodes)
    //{
    //    foreach (var j in GetProductCategories())
    //    {
    //        if (j.ParentId == k1)
    //        {
    //            foreach (var k in j.Subcategories)
    //            {

    //                TreeNode tn = new TreeNode();
    //                tn.Text = k.Name;
    //                tn.Value = k.ParentId.ToString();
    //                nodes.Add(tn);
    //                //tn.PopulateOnDemand = ((int)(k.Subcategories.Count) > 0);

    //            }
    //           // break;
    //        }

    //    }
    //}
    #endregion
    #region class
    //public List<Category1> GetProductCategories()
    //{
    //    return new List<Category1> { 
    //        new Category1{Id=1,Name="Power Tools",ParentId=0,Sequence=1,Description="Power Tools",DisplayInHeader=true, 
    //            Subcategories = new List<Category1>
    //                {new Category1{Id=100,Name="Drills",ParentId=1,Sequence=1,Description="Drills",DisplayInHeader=true},
    //                new Category1{Id=101,Name="Drill Accessories",ParentId=1,Sequence=1,Description="Drill Accessories",DisplayInHeader=true},
    //                new Category1{Id=102,Name="Saws",ParentId=0,Sequence=1,Description="Saws",DisplayInHeader=true}  
    //                }},
    //                new Category1{Id=2,Name="Cordless Tools",ParentId=1,Sequence=1,Description="Cordless Tools",DisplayInHeader=true, 
    //            Subcategories = new List<Category1>
    //                {new Category1{Id=200,Name="Batteries/Chargers",ParentId=2,Sequence=1,Description="Batteries/Chargers",DisplayInHeader=true} 

    //                }},new Category1{Id=3,Name="AirTools",ParentId=2,Sequence=1,Description="Air Tools",DisplayInHeader=true, 
    //            Subcategories = new List<Category1>
    //                {new Category1{Id=300,Name="Portable Compressors",ParentId=0,Sequence=1,Description="Portable Compressors",DisplayInHeader=true},

    //                }}
    //    };
    //}
    #endregion

    public List<Category1> GetProductCategories()
    {
        return new List<Category1> { 
            new Category1{Id=1,Name="Power Tools",ParentId=0,Sequence=1,Description="Power Tools",DisplayInHeader=true, 
                Subcategories = new List<Category1>
                    {new Category1{Id=100,Name="Drills",ParentId=1,Sequence=1,Description="Drills",DisplayInHeader=true},
                    new Category1{Id=101,Name="Drill Accessories",ParentId=1,Sequence=1,Description="Drill Accessories",DisplayInHeader=true,
                        Subcategories= new List<Category1>{new Category1{Id=1001,Name="Drill Accessories",ParentId=101,Sequence=1,Description="Drill Accessories",DisplayInHeader=true,
                        Subcategories= new List<Category1>{new Category1{Id=1001,Name="Drill Accessories",ParentId=101,Sequence=1,Description="Drill Accessories",DisplayInHeader=true}}}}},                              

                    new Category1{Id=102,Name="Saws",ParentId=0,Sequence=1,Description="Saws",DisplayInHeader=true}  
                    }},
                    new Category1{Id=2,Name="Cordless Tools",ParentId=1,Sequence=1,Description="Cordless Tools",DisplayInHeader=true, 
                Subcategories = new List<Category1>
                    {new Category1{Id=200,Name="Batteries/Chargers",ParentId=2,Sequence=1,Description="Batteries/Chargers",DisplayInHeader=true,
                    Subcategories = new List<Category1>
                    {new Category1{Id=200,Name="Batteries",ParentId=2,Sequence=1,Description="Batteries/Chargers",DisplayInHeader=true,
                    Subcategories = new List<Category1>
                    {new Category1{Id=200,Name="Batteries",ParentId=2,Sequence=1,Description="Batteries/Chargers",DisplayInHeader=true,
                    Subcategories = new List<Category1>
                    {new Category1{Id=200,Name="Batteries",ParentId=2,Sequence=1,Description="Batteries/Chargers",DisplayInHeader=true,
                    Subcategories = new List<Category1>
                    {new Category1{Id=200,Name="Batteries",ParentId=2,Sequence=1,Description="Batteries/Chargers",DisplayInHeader=true}}}}}}}}} 

                    }},new Category1{Id=3,Name="AirTools",ParentId=2,Sequence=1,Description="Air Tools",DisplayInHeader=true, 
                Subcategories = new List<Category1>
                    {new Category1{Id=300,Name="Portable Compressors",ParentId=0,Sequence=1,Description="Portable Compressors",DisplayInHeader=true},

                    }},


        };
    }