Building Tree View Resultset
page 1 of 3
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): 31236/ 105

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.

View Entire Article

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-2017 ASPAlliance.com  |  Page Processed at 2017-03-24 2:00:56 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search