Click here to Skip to main content
15,562,222 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I created Products_Categories table with self relations to users can create unlimited subcategories as tree. How can i retrieve data from database to TreeView like this photos


Screenshot 1


Screenshot 2


SQL
CREATE TABLE ProductsCategories(
    CategoryID int IDENTITY(1,1) NOT NULL,
    Name nvarchar(50) NOT NULL,
    Specifications nvarchar(250) NULL,
    ParentID int NULL,
    IsDeleted int NOT NULL,
 CONSTRAINT PK_ProductsCategories PRIMARY KEY CLUSTERED 
(
    CategoryID 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
SET IDENTITY_INSERT ProductsCategories ON 

GO
INSERT ProductsCategories (CategoryID, Name, Specifications, ParentID, IsDeleted) VALUES (9, N'Hardware', NULL, 9, 1)
GO
INSERT ProductsCategories (CategoryID, Name, Specifications, ParentID, IsDeleted) VALUES (10, N'CPU', NULL, 9, 1)
GO
INSERT ProductsCategories (CategoryID, Name, Specifications, ParentID, IsDeleted) VALUES (11, N'Accessories', NULL, 11, 0)
GO
INSERT ProductsCategories (CategoryID, Name, Specifications, ParentID, IsDeleted) VALUES (12, N'Network', NULL, 12, 0)
GO
INSERT ProductsCategories (CategoryID, Name, Specifications, ParentID, IsDeleted) VALUES (13, N'Intel', NULL, 10, 0)
GO
INSERT ProductsCategories (CategoryID, Name, Specifications, ParentID, IsDeleted) VALUES (14, N'AMD', NULL, 10, 0)
GO
INSERT ProductsCategories (CategoryID, Name, Specifications, ParentID, IsDeleted) VALUES (15, N'3.0/2M', NULL, 13, 0)
GO
INSERT ProductsCategories (CategoryID, Name, Specifications, ParentID, IsDeleted) VALUES (16, N'Athlon', NULL, 14, 0)
GO
INSERT ProductsCategories (CategoryID, Name, Specifications, ParentID, IsDeleted) VALUES (17, N'Mouse', NULL, 11, 0)
GO
INSERT ProductsCategories (CategoryID, Name, Specifications, ParentID, IsDeleted) VALUES (18, N'Mouse Premium', NULL, 17, 0)
GO
SET IDENTITY_INSERT ProductsCategories OFF
GO
ALTER TABLE ProductsCategories ADD  CONSTRAINT DF_ProductsCategories_IsDeleted  DEFAULT ((0)) FOR IsDeleted
GO
ALTER TABLE ProductsCategories  WITH CHECK ADD  CONSTRAINT FK_ProductsCategories_ProductsCategories FOREIGN KEY(ParentID)
REFERENCES ProductsCategories (CategoryID)
GO
ALTER TABLE ProductsCategories CHECK CONSTRAINT FK_ProductsCategories_ProductsCategories
GO

Select PC2.Name AS 'Parent Category',
        PC.Name AS 'Category',
        PC.Specifications
From ProductsCategories AS PC
Join ProductsCategories AS PC2
On PC.CategoryID = PC.ParentID


What I have tried:

I have added TreeView data manually but i have not ideas to retrieve it from database
Posted
Updated 11-Jun-17 11:41am

1 solution

In order to fetch all levels using a single query, you need to use recursive query. This can be achieved by Common Table Expressions (CTE).

For an example, have a look at Recursive Queries Using Common Table Expressions[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900