ASP.Net : DropDownList containing category - sub category hierarchy

Introduction:

This article will provide us a way to embed both categories and sub-categories hierarchy in a single DropDownList exactly same as the image on the left.

I searched the web for this but did not find exactly what I needed. So, I planned to write my own logic for this. If somebody has a better idea then I really appreciate and request to share.

Description:

Lets take an example where we need to show states and districts of India in a single DropDownList with their hierarchy. Here state will behave as category and districts can be considered as sub-categories.

In DB part we need two tables i.e, States and Districts may be like following structure.

• State       –> (StateId int pk, StateName varchar(50))
• Districts –> (DistrictId int pk, StateId int fk, DistrictName varchar(50))

Or, this script may help you creating the tables

CREATE TABLE [dbo].[States](
 [StateId] [int] IDENTITY(1,1) NOT NULL,
 [StateName] [varchar](50) NULL,
 CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED
(
 [StateId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Districts](
 [DistrictId] [int] IDENTITY(1,1) NOT NULL,
 [StateId] [int] NOT NULL,
 [DistrictName] [varchar](50) NULL,
 CONSTRAINT [PK_Districts] PRIMARY KEY CLUSTERED
(
 [DistrictId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Districts] WITH CHECK ADD CONSTRAINT [FK_Districts_States1] FOREIGN KEY([StateId])
REFERENCES [dbo].[States] ([StateId])
GO

ALTER TABLE [dbo].[Districts] CHECK CONSTRAINT [FK_Districts_States1]
GO

Now insert some sample data or you can execute this script.

/****** Object: Table [dbo].[States] ******/
SET IDENTITY_INSERT [dbo].[States] ON
INSERT [dbo].[States] ([StateId], [StateName]) VALUES (1, N'Odisha')
INSERT [dbo].[States] ([StateId], [StateName]) VALUES (2, N'West Bengal')
INSERT [dbo].[States] ([StateId], [StateName]) VALUES (3, N'Bihar')
INSERT [dbo].[States] ([StateId], [StateName]) VALUES (4, N'Jharkhand')
SET IDENTITY_INSERT [dbo].[States] OFF
/****** Object: Table [dbo].[Districts] ******/
SET IDENTITY_INSERT [dbo].[Districts] ON
INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (1, 1, N'Mayurbhanj')
INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (2, 1, N'Keonjhar')
INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (3, 1, N'Khorda')
INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (4, 1, N'Balasore')
INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (5, 2, N'Hoogly')
INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (6, 2, N'Howrah')
INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (7, 2, N'Jalpaiguri')
INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (8, 3, N'Aurangabad')
INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (9, 3, N'Patna')
INSERT [dbo].[Districts] ([DistrictId], [StateId], [DistrictName]) VALUES (10, 4, N'Bokaro')
SET IDENTITY_INSERT [dbo].[Districts] OFF

Place a DropDownList in your page.

<asp:DropDownList ID="drpDistricts" runat="server"></asp:DropDownList>

Lastly do the coding required to bind the DropDownList. Things to remember are we have a single DropDownList and it should be meant for choosing a sub-category(District), which means categories should be disabled for clicking. Although there may be several ways like looping the sub-categories and inserting categories in-between etc. but, I personally prefer to query both of them in a single query.

SELECT '- '+DistrictName AS TextField,DistrictId AS ValueField,StateId FROM DBO.Districts UNION SELECT UPPER(StateName),0,StateId FROM dbo.States ORDER BY StateId,DistrictId

Put DataTextField =”TextField” and DataValueField=”ValueField”

Now, lets do the coding to disable the categories (States).

foreach (ListItem item in drpDistricts.Items)
 {
 if (item.Value.ToString() == "0")
 {
 item.Attributes.Add("Style", "color:maroon");
 item.Attributes.Add("Disabled", "true");
 }
 }

That’s all.
Thanks for reading.
Feedback or suggestion are most welcome.

About these ads