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

Question

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

Answer 1

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:

• http://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/
• http://msdn.microsoft.com/en-us/library/ms186243.aspx
• Printing tree with SQL CTE

Answer 2

Have you looked at using hierarchyid? see: http://msdn.microsoft.com/en-us/library/bb677173(v=sql.100).aspx

Of course you'll need Sql 2008 or later, but it's intended for what you're trying to do.