从数据库检索分层数据

引言

分层数据的表示相对简单，但其管理和检索却相当复杂。本文介绍存储和检索分层数据的各种技术。

背景  

分层数据模型是实现分层业务实体（如（总账层级、组织层级、菜单层级等））的成熟建模技术。分层数据也可用于从数据库检索基于角色的菜单。管理数据和遍历分层数据本身是一项艰巨的任务。

使用代码 

存储和管理分层数据 

理想情况下，分层数据通过级别（深度）表示，并应考虑使用树数据结构算法。现在，重要的是使用分层数据建模设计这样的表，并创建能够以优化方式返回分层数据的查询。

CREATE TABLE [GLHierarchy](
[GUID] [int] IDENTITY(1,1) NOT NULL,
[GUCD] [varchar](10) NOT NULL,
[GUDesc] [varchar](50) NOT NULL,
[ParentGUCD] [varchar](10) NULL
) ON [PRIMARY] Create Table script for above table.  

 /* Insert queries */
 
INSERT INTO [GLHierarchy] Values ('AP0','Asia-Pacific',null)

-- Level 2 Records -- 
INSERT INTO [GLHierarchy] Values ('IND','India','AP0')
INSERT INTO [GLHierarchy] Values ('PHL','Phillipines','AP0')
INSERT INTO [GLHierarchy] Values ('CHN','China','AP0')

-- Level 3 Records -- 
INSERT INTO [GLHierarchy] Values ('MAH','Maharashtra','IND')
INSERT INTO [GLHierarchy] Values ('KAR','Karnataka','IND')
INSERT INTO [GLHierarchy] Values ('GUJ','Gujarat','IND')

INSERT INTO [GLHierarchy] Values ('MNL','Manila','PHL')
 
-- Level 4 Records --
INSERT INTO [GLHierarchy] Values ('BOM','Mumbai','MAH')
INSERT INTO [GLHierarchy] Values ('PUN','Pune','MAH')
INSERT INTO [GLHierarchy] Values ('BAN','Bangalore','KAR')  

----- create GLSubordinates function using CTE
if object_id( 'GLSubordinates', 'IF' ) is not null 
	drop function GLSubordinates
GO
 
create function GLSubordinates( @GLCD varchar(10) ) 
 
returns table as return with GLSubnodes( distance, GLID, GLCD, GLDesc, ParentGLCD, NodeSequence )
AS ( select 0, h.GLID, h.GLCD, h.GLDesc, h.ParentGLCD, convert( varchar(80), ltrim(str(h.GLID))) as NodeSequence 
from [GLHierarchy] h 
where h.GLCD = @GLCD 
union all 
select distance+1,  h.GLID, h.GLCD, h.GLDesc, h.ParentGLCD, convert( varchar(80), sn.NodeSequence+'.'+ltrim(str(h.GLID))) 
from [GLHierarchy] h inner join GLSubnodes sn on h.ParentGLCD = sn.GLCD)
select distance, GLID, GLCD, GLDesc, ParentGLCD, NodeSequence from GLSubnodes
 
GO  

从下属中选择“IND”的记录。

select * from GLSubordinates('IND') 

上述语句应返回以下结果。

上面的示例返回 GLCD = ‘IND’ 的所有子孙地理位置。NodeSequence 是可选字段，它只是显示树的深度，您可以看到 2, 2.5, 2.5.9, 2.5.10 等…

CTE 查询的性能相当高。在具有 12 个层级、24000 条记录的数据的 GLID 和 GLCD 上进行索引后，此查询的返回数据时间不到一秒。

默认情况下，CTE 查询的递归限制为 100 个级别。我认为这对于普通的分层数据来说已经足够了，但您也可以更改它。

有关更多信息，请参阅 http://msdn2.microsoft.com/en-us/library/ms181714.aspx。

您可以直接或在查询中使用此函数。通过添加级别检查，您可以自定义此函数以返回指定级别的树。

检索分层数据 

我们可以从最后一个节点到根节点检索分层数据。

您可能需要了解子元素的祖父母信息。通过实现分层数据的 Superior 函数，您可以实现相同的功能。

假设有一个需求，即某些条件适用于一个城市，则该条件也应适用于该层级的所有父节点。

为了实现此功能，我们需要创建 GLSuperiors 函数。此函数也使用 CTE（公用表表达式）查询。GLSuperiors 函数遍历分层信息，并以相反方向返回遍历后的层级。

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

现在选择“BOM”城市代码的分层数据。

SELECT * FROM GLSuperiors('BOM') 

以上 SELECT 语句返回 GLCd = 'BOM' 的所有父级祖父母地理位置。

我们已经完成了分层数据的管理和检索。我们可以使用相同的功能来填充分层菜单、公司层级或家谱等。

提高 C# 应用程序中分层数据的检索效率。 

通常，我们将非事务性的分层数据存储在数据库中，这些数据会定期更新，并且不参与事务。我们可以使用 Enterprise Library Application Block 的缓存机制。我们需要创建父子数据结构来在应用程序中存储分层数据，我们只需要将每个分层数据的根数据存储在某个地方（缓存）。

实现 HierarchicalDataType 枚举。

 public enum HierarchicalDataType
   {
     GULocation = 1,
     OrganizationHierarchy = 2,
     ProductHierarchy = 3,
     ServiceCenterHierarchy = 4
   }

     public class HierarchicalData
    {
        HierarchicalDataType dataType; 
        HierarchicalData parent;
        string hierardhicalDataCD;
        string hierardhicalDataDesc;
        int order;
        IList<HierarchicalData> child;
     }