Building Tree View Resultset
 
Published: 08 Mar 2004
Unedited - Community Contributed
Abstract
Ever had a problem where you wanted organizational structure or menu structure in hierarchal view returned back from SQL. This article explains about getting hierarchal data back from SQL.
by Ameet Phadnis
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 27740/ 88

Introduction

Most programmers might have faced the problem about displaying data in Tree View. There are different ways to solve this problem. The best way depends on your environment. As my previous articles I will first explain different approaches and then give you the solution I normally use.

 

Normally, the datastructure is somewhat like this –

[ID] int,

[NAME] nvarchar(100)

[PARENTID] int

[SUBVIEWORDER] int

 

Different approaches:

  1. Front End Approach / Middle Tier approach: In this approach, normally programmers do get all the data back from the database. This data is not sorted and the programmer has to write logic on the front end to resolve this issue. Normally, the programmer gets the highest level and checks if it is a Parent to some other record. Normally, the highest level will have Parent Item as NULL. In our above table structure the PARENTID will be null. So, first we get the ID and Check if that ID is present in any PARENTID column for records. Once we get those, we start building the tree on the front end. This approach might use lot of coding on the front end.
  2. Re-Ordering each time you update/insert any item: In this case, we might use the SUBVIEWORDER column or add another column called VIEWORDER. So, each time a new item is added/updated you will be playing with this column to keep all the items in sync. So, when we return back the result set you can get it in the right order. This procedure can be written in Stored Procedure and can be called from add or update stored procedure. This is better approach if there is too much data to be returned back in which case option 3 won’t be feasible as it uses temp tables.
  3. Doing it through Stored Procedure with temp tables and keeping it dynamic: This might be good option to have the data returned back dynamically. I will explain this method. Users can convert this example to option 2 by just replacing the table name instead of the temp table name.
Hierarchal data Stored procedures using Temp tables

For explaining this procedure I will use the following table

 

Menus

MenuID

MenuName

ParentMenuID

MenuSubOrder

 

The menuSubOrder will sort the sub menus. For example, you have MenuItem1 with two sub menus called SubMenuItem1 and SubMenuItem2. The SubMenuItem2 is supposed to be before SubMenuItem1, in this scenario the MenuSubOrder for Submenuitem2 will be 1 and for SubmenuItem1 will be 2.

 

First, we need to create a temp table and populate it with all records. You can either use the Create Table SQL Statement or you can use the SELECT INTO construct. For this example, your structure might look like –

CREATE TABLE #TempMenus

(

            MenuID int NOT NULL,

            MenuName nvarchar(50),

            ParentMenuID int NULL,

            DisplayOrder int Null,

            MenuSubOrder int Null,

            ViewLevel int Null

)

In this case, you will have to write separate select statement to insert all rows

INSERT INTO #TempMenus

(

            MenuID,

            MenuName,

            ParentMenuID,

            MenuSubOrder

)

SELECT MenuID,

            MenuName,

            IsNull(ParentMenuID, 0),

            MenuSubOrder

FROM            Menus

 

OR use the following syntax

 

SELECT        MenuID,

                        MenuName,

                        IsNull(ParentMenuID, 0) ParentMenuID,

                        Null DisplayOrder,

                        MenuSubOrder,

                        Null ViewLevel

INTO               #TempMenus

FROM                        Menus

 

We have all the records that will be returned back. We are changing the ParentMenuID from Null to 0 for specifying the root.

 

We will have to declare all variables that needs to be used. The following variables will be used –

 

@DisplayOrder int - This variable is being used to increment the actual order to return the result set.

@MenuID int – This variable stores the Current TabID

@ParentMenuID int – This Variable stores the Parent TabID of the current Tab

@ViewLevel int – The tree view position is stored in this variable

 

We also initialize all the variables before using it –

 

SET @DisplayOrder = 0

SET @ViewLevel = 0

SET @ParentMenuID = 0

SET @ViewLevel = 0

 

The next step is to loop through this table to determine the relationship. To achieve this we will be using While Loop

 

While Exists(Select * from #TempMenus Where DisplayOrder is Null AND ParentMenuID = @ParentMenuID)

 

In short, this while loop will continue on till all records do have some DisplayOrder value. When we return the resultset it is going to be sorted based on the displayOrder.

 

Now we work with single record at a time. We are interested in getting the child record which does not have any value for DisplayOrder.

 

SELECT TOP 1 @MenuID = TabID FROM #TempMenus Where DisplayOrder is Null AND ParentMenuID = @ParentMenuID Order by MenuSubOrder

 

Next Step is to get the next displayorder and the view level

 

SET @DisplayOrder = @DisplayOrder + 1

SELECT @ViewLevel = ViewLevel + 1 FROM #TempTabs WHERE TabID = @ParentTabID

 

Once, we have the next display order and view level we update the temp table.

 

UPDATE #TempMenus

SET    DisplayOrder = @DisplayOrder,

            ViewLevel = @ViewLevel

WHERE         MenuID = @MenuID

 

Now we have the current record that was modified. So, we need to check if the current menu is parent to some other menu. We can check it as follows –

 

If Exists(Select * from #TempMenus Where DisplayOrder is Null AND ParentMenuID = @MenuID)

 

If any records were found then we use that MenuID as the PArentMenuID now

SET @ParentMenuID = @MenuID

 

If no child records are present for the current menuID then we should go up the tree to find the parent menu ID which might have child records that does not have display order. This is achieved by –

 

SET @ParentMenuID = @MenuID

While (Not Exists(Select * from #TempMenus Where DisplayOrder is Null AND ParentMenuID = @ParentMenuID)) And @parentMenuID <> 0

Begin

            SELECT @ParentMenuID = ParentMenuID FROM #TempMenus Where MenuID = @ParentMenuID

End

IF @ParentMenuID = 0

            SET @ViewLevel = 0

 

All the above steps will loop through each record and assign a value to the displayorder column.

 

Finally, we are going to display all the records ordered by displayorder.

SELECT * FROM #TempMenus Order by DisplayOrder

 

Also, as it is a temp table we need to drop that temp table

Drop Table #TempMenus.

Complete SQL Script

CREATE PROCEDURE GetMenus

AS

--Variable Declaration.

declare @DisplayOrder int,

            @MenuID int,

            @ParentMenuID int,

            @ViewLevel int

 

-- Insert all records into the temp table.

 

SELECT        MenuID,

            MenuName,

            IsNull(ParentMenuId, 0) ParentMenuID,

            Null DisplayOrder,

            MenuOrder,

            Null ViewLevel

INTO   #TempMenus

FROM            Menus

 

-- Initalize the variables for display order, view level and the parent menu Id 

 

SET @DisplayOrder = 0

SET @ViewLevel = 0

SET @ParentMenuID = 0

 

--Loop through all menu Items and set the display order

 

While Exists(Select * from #TempMenus Where DisplayOrder is Null AND ParentMenuID = @ParentMenuID)

Begin

 

-- Select the top 1 menu item which does not have display order value and is child of the current Menu.

           

            SELECT TOP 1 @MenuID = MenuID FROM #TempMenus Where DisplayOrder is Null AND ParentMenuID = @ParentMenuID Order by MenuOrder

 

-- Increment the display order. 

            SET @DisplayOrder = @DisplayOrder + 1

 

-- Get the current view level and increment it by 1.

            SELECT @ViewLevel = ViewLevel + 1 FROM #TempMenus WHERE MenuID = @ParentMenuID

 

-- Update the display order and view level in the temp table.

 

            UPDATE #TempMenus

            SET    DisplayOrder = @DisplayOrder,

                        ViewLevel = @ViewLevel

            WHERE         MenuID = @MenuID

 

-- Check if there are child menus under this child menu.

            If Exists(Select * from #TempMenus Where DisplayOrder is Null AND ParentMenuID = @MenuID)

            Begin

 

-- If there are child menu items under the current child menu item then make the child menu item as the parent menu item.

 

                        SET @ParentMenuID = @MenuID

                       

            End

            Else

            Begin

-- This child item does not have any child menu items then go up level till you reach the top. If the top level has more child menu items then process those.

 

                        SET @ParentMenuID = @MenuID

                        While (Not Exists(Select * from #TempMenus Where DisplayOrder is Null AND ParentMenuID = @ParentMenuID)) And @parentMenuID <> 0

                        Begin

 

                                    SELECT @ParentMenuID = ParentMenuID FROM #TempMenus Where MenuID = @ParentMenuID

                                    --Print 'Inside While: ' + Cast(@ParentMenuID as varchar(4))

                        End

                        IF @ParentMenuID = 0

                                    SET @ViewLevel = 0

            End

End

 

-- Get the resultset ordered by display order.

 

SELECT * FROM #TempMenus Order by DisplayOrder

DROP Table #TempMenus

GO



User Comments

Title: Thanks you   
Name: Xuân Dũng
Date: 2010-08-04 3:09:04 AM
Comment:
Thanks you.
Excellent work
Title: Thank You!   
Name: Jenny J.
Date: 2009-06-12 11:39:35 AM
Comment:
Excellent work and very useful.
Title: navigation   
Name: treeview
Date: 2009-05-19 12:13:40 PM
Comment:
how to create






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2018 ASPAlliance.com  |  Page Processed at 2018-12-11 8:40:52 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search