Retrieve Hierarchical Data from Database

Introduction

Hierarchical Data representation is quite simple but it's management and retrieving is quite complex. This article describe various techniques to store and retrieve hierarchical data.

Background

Hierarchical Data Model is proven modeling technique to implement hierarchical business entities like (GL Hierarchy, Organization Hierarchy, Menu Hierarchy etc…). Hierarchical data can also use to retrieve role based menu from database. Managing data and traversing through Hierarchical data itself is tough job.

Using the code

Store and Manage Hierarchical Data

Ideally hierarchical data is representing by level (depth) and consider it should use Tree Data Structure Algorithm. Now it is important to design such table using Hierarchical Data Modeling and creating queries which will return hierarchical data in optimized manner.

Now it is important to retrieve data of this table in hierarchical format. That means if we are requesting for particular Geographic Location (say ‘India’) then query should return all child-grand child records for India. To achieve this we will use CTE (Common Table Expression). Please refer http://msdn.microsoft.com/en-us/library/ms190766.aspx for other usage of CTE.

Consider that you have requirement where you want return Geographic Location tree by passing GLCd. To achieve this you need to write CTE query. CTE query are bit complex to understand so we will create some functions to simplify this query. We will create GLSubordinates function, which accept GLCd (GL Code).

You can use this function directly or in your queries. By adding Level check, you can customize this function to return tree up to specified level.

Retrieve Hierarchical Data

We can retrieve hierarchical data from last node to root node.

It can be possible that you want to know grand parent information of a child element. By implementing Superior function for hierarchical data you can achieve same functionality.

Consider a requirement where some condition is applicable to one City then it should applicable to all parents Nodes of that hierarchy.

To achieve this functionality we need to create GLSuperiors function. This function is also using CTE (Common Table Expression) query. GLSuperiors function is traverse through hierarchical information and returns the traversed hierarchy in opposite direction.

We are done with managing and retrieving hierarchical data. We can use same functionality to populate hierarchical menu, company hierarchy or genealogy etc...

Improve retrieving of Hierarchical Data in C# application.

Usually we are storing non-transactional hierarchical data in to the database, which are updating periodically and not taking part in transactions. We can use caching mechanism from Enterprise Library Application Block. We need to create parent-child data structure to store hierarchical data in our application and we just need to store root data for individual hierarchical data at some place (Cache).

Now you just need to populate a tree using your hierarchical data. This should be one time activity you can call from Application_Start and you have to store the root node in to the cache. For better accessibility you can store node information in key-value combination (HashTable), where key is hierarchical data CD (must be a unique) and value is the main HierarchicalData object.

Consider that we have one webpage where there is some dependent drop down for Country and user is selecting 'India' (CD: 'IND') then it should directly return all the Geographic Locations (State) with 'India' as parent.

This reduces database call, and giving performance to the non-transactional hierarchical data.

You can also maintain order, which can be useful to display in order. This is useful to create menus. By manipulate such functions, you can use them in AJAX, web services etc…

Points of Interest

This is part of my fun and learn activity. I may extend this article for further use.

About the Author

Comments and Discussions

Article is good,useful. Would be good if mismatch column names like somewhere GLCD and somewhere GUCD would have been correct. Also, function GLSuperiors had incorrect closing bracket before union, which should close before last select statement.