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
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 “
Return DirectCast(hierarchicalData("
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
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')
