AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=1026&pId=-1
Building a TreeView ResultSet with SQL Server 2005
page
by Ameet Phadnis
Feedback
Average Rating: 
Views (Total / Last 10 Days): 29676/ 30

Introduction

In a corporate environment we usually face coding tasks to display results in Treeview.  The tasks may include displaying corporate structure or in programs displaying the menu structure.  Around 5 to 6 years ago when I was tasked with a similar kind of assignment, my manager wanted me to get the results through the SQL Stored Procedure and then the web front end would do the rest.  I came up with a way to create the tree hierarchy in SQL Server 2000.  It was very complex and hard to understand.  We had no idea how much SQL Server 2005 would simplify our lives.  I have explained the procedure in my previous article.  You can view the article by clicking here.

Before I give you the SQL Script, I would like to explain some of the new features introduced in SQL Server 2005.

CTE

So, what is CTE?  It stands for Common Table Expression.  It allows the code to reference itself. This assists us in creating recursive CTE.  The initial CTE is repeatedly executed and it returns subsets of data.  This will continue on until the complete dataset is returned.

CTE Structure

The CTE Structure will look like the following.

Listing 1

With <CTE Name>(Column1, column2,…, column n)
AS
(
<Query Definition>- Anchor Member 
Union All
<Query Definition> - Uses the <CTE Name> for recursive member
)
SELECT *
FROM <CTE Name>

There are three elements to recursive CTE.

·         Invocation of the routine: As shown above, the first invocation is with the anchor query definition.  There can be one anchor query definition or multiple, but all anchor query definitions should be positioned before the Recursive member definition.  The anchor query definitions can be joined using Union All, Union, Except and Intersect.  To join the Recursive Member you need to use the Union All operator.

·         Recursive Invocation of the routine: This includes one or more CTE query definitions that reference the CTE itself.  The Query definitions need to be joined by Union All Operator.

·         Termination Check: Recursion stops when no rows are returned from the previous invocation.

Let us consider the CTE Structure code shown above.  How does the execution really occur in this scenario?

1.      The CTE Expression is split into anchor query members and recursive members.

2.      Run the anchor query and create a base result set.

3.      Run the recursive member with the previous output as input and generate the next output.

4.      Repeat the above step until the result set output returns empty.

5.      Return the complete resultset.  This is union of all outputs.

Table structure for hierarchy

Consider the table structure we have for our example.  We are going to a create tree structure for the departments.

 

tblDepartments

DepartmentID

DepartmentName

ParentDepartmentID

 

The sample data is as follows.

 

DepartmentID

DepartmentName

ParentDepartmentID

1

HR

<NULL>

2

IT

<NULL>

3

Networking

2

4

Development

2

5

ASP.NET Development

4

6

J2EE Development

4

7

C#.NET

5

8

VB.NET

5

 
Writing your Query

As explained above, we need to create our first anchor query.  The query will be for the root level elements which are going to be the ones that do not have any Parent Department.  The query will look like the listing below.

Listing 2

SELECT DepartmentID, DepartmentName, ParentDepartmentID FROM tblDepartments

We need to change the query to include the HeirarchyLevel.  For this example, I will call it HLevel. So, now our query will look like the following.

Listing 3

SELECT DepartmentID, DepartmentName, ParentDepartmentID, 0 as HLevel FROM tblDepartments

 Now define the CTE structure using the above defined columns.

Listing 4

WITH Hierarchy(DepartmentID, DepartmentName, ParentDepartmentID, HLevel)

The above is the definition of the CTE.  Now we need to define the AS clause for the CTE.

Listing 5

WITH Hierarchy(DepartmentID, DepartmentName, ParentDepartmentID, HLevel)
AS
(
SELECT DepartmentID, DepartmentName, ParentDepartmentID, 0 as HLevel FROM tblDepartments
UNION ALL
<Recursive Query>
)

The recursive query will add 1 to the parent Level.  The recursive query will look like Listing 6.

Listing 6

SELECT SubDepartment.DepartmentID, 
SubDepartment.DepartmentName,
 SubDepartment.ParentDepartmentID, 
HLevel + 1
    FROM tblDepartments SubDepartment
        INNER JOIN Hierarchy ParentDepartment
        ON SubDepartment.ParentDepartmentID = ParentDepartment.DepartmentID

The final query will be:

Listing 7

WITH Hierarchy(DepartmentID, DepartmentName, ParentDepartmentID, HLevel)
AS
(
SELECT DepartmentID, DepartmentName, ParentDepartmentID, 0 as HLevel FROM tblDepartments
UNION ALL
SELECT SubDepartment.DepartmentID,
 SubDepartment.DepartmentName,
 SubDepartment.ParentDepartmentID,
 HLevel + 1
    FROM tblDepartments SubDepartment
        INNER JOIN Hierarchy ParentDepartment
        ON SubDepartment.ParentDepartmentID = ParentDepartment.DepartmentID

)

Once the CTE structure is defined and we have the result set, we need to display the complete result set.

We need to add the following select statement to display the result set.

Listing 8

SELECT      DepartmentID, 
            DepartmentName = Replicate('.', HLevel) + DepartmentName, 
            ParentDepartmentID,
            HLevel
FROM  Hierarchy

The Replicate function makes sure that you add "." based on the level.

In short, what I explained in my previous article for SQL Server 2000 can be done using the following.

Listing 9

WITH Hierarchy(DepartmentID, DepartmentName, ParentDepartmentID, HLevel)
AS
(
SELECT DepartmentID, DepartmentName, ParentDepartmentID, 0 as HLevel FROM tblDepartments
UNION ALL
SELECT SubDepartment.DepartmentID
, SubDepartment.DepartmentName,
 SubDepartment.ParentDepartmentID,
 HLevel + 1
    FROM tblDepartments SubDepartment
        INNER JOIN Hierarchy ParentDepartment
        ON SubDepartment.ParentDepartmentID = ParentDepartment.DepartmentID )
SELECT      DepartmentID, 
            DepartmentName = Replicate('.', HLevel) + DepartmentName, 
            ParentDepartmentID,
            HLevel
FROM  Hierarchy

The only factor that would remain would be the sort order.  Developers can create their own logic to order the result set.

Summary

In this article I explained the use of Common Table Expressions (CTE), how you can create recursive queries and how it can be used for building treeview resultset.



©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-19 3:58:51 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search