当前位置：首页 > news >正文

SQL2005CLR函数扩展-树的结构

news 来源：原创 2024/5/20 22:02:27

树结构广泛用在各类分级管理设计中。但他的展现方式也是很让人头疼的事情。比如展开一个靠id和parentid建立关系的分级树，SQL2005已经可以用CTE来递归查询。我们看如下测试代码：

set nocount on

print '--SQL2005 CTE 树结构测试

declare @t table ( id varchar ( 10) , pid varchar ( 10), name varchar ( 10))

insert into @t values ( 'a' ,null, '000' )

insert into @t values ( 'b' , 'a' , '111' )

insert into @t values ( 'c' , 'b' , '222' )

insert into @t values ( 'd' , 'b' , '333' )

insert into @t values ( 'f' , 'c' , '444' )

insert into @t values ( 'e' , 'c' , '555' )

; with t ( id, name , pid, path )

as (

select a. id, a. name , a. pid, cast ( a. id as varchar ( 20)) as path

from @t as a

where pid is null

union all

select a. id, a. name , a. pid, cast ( path + '>' + a. id as varchar ( 20))

from @t a

join t as b

on a. pid = b. id

)

select * from t

--SQL2005 CTE 树结构测试

id name pid path

---------- ---------- ---------- --------------------

a 000 NULL a

b 111 a a>b

c 222 b a>b>c

d 333 b a>b>d

f 444 c a>b>c>f

e 555 c a>b>c>e

set nocount off

当你还不知道这个写法的时候可能会有点激动，但是别忘了他是递归，性能并不比以前的循环+表变量好。我用他展开一个26000条数据，最深5级的权限组分级表时，10分钟后仍然没有查询结束。而我尝试用CLR写扩展函数来实现同样的效果，展开同样的结构，只需要不到2秒 .

下面我们看这个clr的写法，主要思路是提前在内存中build一个链表，可以返回任意节点的路径。当然和之前的几个clr的处理并发方式一样，需要维护一个并发key。

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

private class TreeNode

{

public TreeNode(string id)

{

this ._id = id;

}

private string _id;

public TreeNode parentNode=null ;

public override string ToString()

{

System.Text.StringBuilder path = new System.Text.StringBuilder ();

path.Append(this ._id);

TreeNode parent = this .parentNode;

while (parent != null )

{

path.Insert(0, ">" );

path.Insert(0, parent._id);

parent = parent.parentNode;

}

return path.ToString();

}

private class TreeNodeCollection

{

private System.Collections.Generic.Dictionary <string , TreeNode > _nodeList = new System.Collections.Generic.Dictionary <string , TreeNode >();

public TreeNode AddNode(string id,TreeNode node)

{

_nodeList.Add(id, node);

return node;

}

public TreeNode GetNode(string id)

{

if (_nodeList.ContainsKey(id))

return _nodeList[id];

else

return null ;

}

static System.Collections.Generic.Dictionary <string , TreeNodeCollection > _tempNodeList = new System.Collections.Generic.Dictionary <string , TreeNodeCollection >();

[Microsoft.SqlServer.Server.SqlFunction ]

public static SqlBoolean TreeBuilder(SqlString key, SqlString id,SqlString pid)

{

if (key.IsNull || id.IsNull) return true ;

TreeNodeCollection nodeList = _tempNodeList[key.Value];

TreeNode node = nodeList.GetNode(id.Value);

if (node == null )

{

node = new TreeNode (id.Value);

nodeList.AddNode(id.Value,node);