Search code examples
c#asp.netsqlsql-servertree-structure

I need to show data in a drop down like a tree structure, I tried but could not get it right in ASP.Net

I am working on a CMS which supports multiple Language and i want drop-down to show "pageNames" created similar to example below

Home Page
Second Page
-Child Page1
-Child Page2
Third Page
-Child Page1
-Child Page2
-Child Page3
--Sub Child Page1
--Sub Child Page2

Below is the table structure I am using to store page. I tried it using SQL and C# code but couldn't get it right. If will appreciate if some one can send me SQL query if that can be achieved using a SQL query only or show me a code snippet in C# to do this.

From my side my table structure looks fine any change to this structure are also welcome to improve performance

tbl_Language

LangID int   -- PK
LangName nVarchar(20) -- English, Arabic, Spanish
Lang_Code varchar(6)  -- en-US, ar-AE etc

tbl_Pages

pageID int
PageName nVarchar(50)
pageTitle nVarchar(200)
pageDesc nVarchar(400)
pageBody nVarchar(Max)
.....
.....
PageParent int
LangID int  -- FK
....
....

Thanks in advance

Solution

  • Use a Common Table Expressions to write a recursive query to traverse the tree structure of pages in sql side if you want to do that in sql side.

    Take a look at these links for more info: