Working with Hierarchical Data Part II

Last post (Working with Hierarchical Data) we saw that how can we store and retrieve hierarchical data. This post we will see how to retrieve hierarchical data from last node to root node and how to store non-transactional (master) hierarchical data to gain maximum performance benefit.

Retrieve Hierarchical Data in Reverse Order from Database

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 will 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.

Please find create script for this function in attached SQL file.

SELECT * FROM GLSuperiors(‘BOM’)

Above example, returns all the parent-grandparent Geographic Location for GLCd = ‘BOM’.

Store non-transactional Hierarchical Data

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).

Public Enum HierarchicalDataType

     GULocation = 1

     OrganizationHierarchy = 2

     ProductHierarchy = 3

     ServiceCenterHierarchy = 4

End Enum



Public Class HierarchicalData

     Private dataType As HierarchicalDataType

     Private parent As HierarchicalData

     Private hierardhicalDataCD As String

     Private hierardhicalDataDesc As String

     Private order As Integer 'Optional

     Private child As IList(Of HierarchicalData)

End Class

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.

Return DirectCast(hierarchicalData("IND"), HierardhicalData).child

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…

To understand Enterprise Library Caching please refer: http://www.codersource.net/asp_net__enterprise_library_caching_block.aspx

Database Script

----- create the Superiors function using CTE  

if object_id( 'GLSuperiors', 'IF' ) is not null drop function GLSuperiors

GO 

create function GLSuperiors( @GUCD varchar(10))

   returns table as return with GLSupnodes

           ( distance, GUID, GUCD, GUDesc, ParentGLCD ) as

           (select 0, h.GUID, h.GUCD, h.GUDesc, h.ParentGUCD  

               from [GLHierarchy] h where h.GUCD = @GUCD

           union all  

           select distance-1, h.GUID, h.GUCD, h.GUDesc, h.ParentGUCD

               from [GLHierarchy] h inner join GLSupnodes sn on h.GUCD = sn.ParentGLCD  ) 

select distance, GUID, GUCD, GUDesc, ParentGLCD   from GLSupnodes 

GO





select * from GLSuperiors('BOM')