Bind Category and Subcategory in asp.net

In this article, i am going to explain how to maintain Category and Subcategory hierarchy in asp.net.

Aspx Page

   <form id="form1" runat="server">
        <div>
            <asp:Label ID="lblMsg" runat="server"></asp:Label>
            <ul>
                <asp:Repeater ID="repCategory" runat="server" OnItemDataBound="repCategory_ItemDataBound">
                    <ItemTemplate>
                        <li>
                            <asp:Label Font-Size="Large" Font-Bold="true" ID="lblCategoryName" runat="server"
                                Text='<%# Eval("CategoryName") %>' />
                        </li>
                        <ul>
                            <asp:Repeater ID="repSubCategory" runat="server">
                                <ItemTemplate>
                                    <li style="background-color:lightblue">
                                        <asp:HyperLink ID="hlkSubCategory" runat="server" Text='<%# Eval("SubCategoryName")%>' />
                                    </li>
                                </ItemTemplate>
                            </asp:Repeater>
                        </ul>
                    </ItemTemplate>
                </asp:Repeater>
            </ul>
        </div>
    </form>

 CS Page

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

public partial class Category : System.Web.UI.Page
{
    string strConnection = @"Data Source=AMREEK\SQLEXPRESS; uid=sa; pwd=12345;database=demo;";
    
    protected void Page_Load(object sender, EventArgs e)
    {
        BindData();
    }

    private void BindData()
    {
        try
        {
            SqlConnection myConnection = new SqlConnection(strConnection);
            SqlCommand myCommand = new SqlCommand("[dbo].[GetCategoryWithSubCategory]", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter ad = new SqlDataAdapter(myCommand);
            DataSet ds = new DataSet();
            ad.Fill(ds);
            // Attach the relationship to the dataSet with relationship name, parent column, child column
            ds.Relations.Add(new DataRelation("CategoriesRelation", ds.Tables[0].Columns["CategoryID"],
            ds.Tables[1].Columns["CategoryID"]));
            repCategory.DataSource = ds.Tables[0];
            repCategory.DataBind();
        }

        catch(Exception ex)
        { Response.Write(ex.Message); }

    }

    protected void repCategory_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        if (e.Item.ItemType == ListItemType.Item ||
        e.Item.ItemType == ListItemType.AlternatingItem)
        {
            DataRowView drv = e.Item.DataItem as DataRowView;
            Repeater innerRep = e.Item.FindControl("repSubCategory") as Repeater;
            innerRep.DataSource = drv.CreateChildView("CategoriesRelation");
            innerRep.DataBind();
        }
    }
}

SQL Query 

-- category table --
create table category
(
categoryId int identity primary key not null,
categoryName nvarchar(100) not null
)

insert category values ('a')
insert category values ('b')
insert category values ('c')
select * from category


-- sub category table --
create table subcategory
(
categoryId int not null FOREIGN KEY REFERENCES category(categoryId),
subcategoryId int identity not null,
subcategoryName nvarchar(100) not null,
)

insert subcategory values (1,'one')
insert subcategory values (1,'two')
insert subcategory values (1,'three')
insert subcategory values (2,'one')
insert subcategory values (2,'two')
insert subcategory values (3,'one')
insert subcategory values (3,'two')
insert subcategory values (3,'three')
insert subcategory values (3,'four')
select * from subcategory



-- procedure to select both tables data --
Go
Create PROCEDURE [dbo].[GetCategoryWithSubCategory]
AS

-- only those categories selected whose sub-categories exist
SELECT * FROM category WHERE CategoryID IN
( SELECT CategoryID FROM subcategory )

-- select all records from subcategories, here p just used as alias
SELECT p.subcategoryId , p.SubCategoryName ,p.CategoryID FROM subcategory p
Go

 Output

That’s it!!…..Happy Programming...