用 C# 开发 SQL Server 2005 的自定义聚合函数

来源:互联网 发布:社交网络电影讲的什么 编辑:IT博客网 时间:2019/11/21 16:05
在 SQL 中,经常需要对数据按组进行自定义的聚合操作,比如用逗号连接一系列表示 ID 的数字,但默认只有 SUM, MAX, MIN, AVG 等聚合函数。在 SQL Server 2005 中提供了编写 CLR 的托管代码的支持,我们可以用来写自定义的聚合函数。
比如对于如下数据:

AgeName
20张三
21李四
20王二
22赵五
18钱六

我们想得到

AgeName
18钱六
20张三,王二
21李四
22赵五

需要实现一个聚合函数 StrJoin, 其功能是用逗号连接字符串。
预期的 SQL 语句如下:
select 
    Age, 
    dbo.StrJoin(Name) 
as Name
from 
    SomeTable

要实现这个函数,用 Visual Studio 2005 建立一个 C# 的 Database 项目,项目模版选择 SQL Server 数据库。在项目管理器里添加一个 Aggregate 后,输入代码如下:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, 
    IsInvariantToDuplicates
=false,
    IsInvariantToNulls
=true,
    IsInvariantToOrder
=false,
    IsNullIfEmpty
=true,
    MaxByteSize
=8000
)]
public struct StrJoin: IBinarySerialize {
    
private StringBuilder _result;

    
public void Init() {
        _result 
= new StringBuilder();
    }

    
public void Accumulate(SqlString Value) {
        
if (Value.IsNull) {
            
return;
        } 
else {
            
if (_result.Length > 0)
                _result.Append(
",");
            _result.Append(Value.Value);
        }
    }

    
public void Merge(StrJoin Group) {
        _result.Append(Group._result);
    }

    
public SqlString Terminate() {
        
if (_result.Length > 0) {
            
return new SqlString(_result.ToString());
        }
        
return new SqlString("");
    }

    
#region IBinarySerialize Members

    
public void Read(System.IO.BinaryReader r) {
        _result 
= new StringBuilder(r.ReadString());
    }

    
public void Write(System.IO.BinaryWriter w) {
        w.Write(_result.ToString());
    }

    
#endregion
}

这里不叙述详细的操作步骤,网上应该可以搜到很多。
其原理是该类中提供了几个模版方法:Init(), Accumulate(), Merge(), Terminate().
我们需要做的是在其中写自己的聚合逻辑即可。这几个方法的含义分别是初始化,扫描到一条记录时,合并,终止扫描。

需要注意以下几点:

1. 自定义聚集函数中,我们返回的数据会被序列化然后转换到 SQL Server 中,对一些数值类型 Framework 提供了默认的序列化机制,但其他一些 CLR 的类型比如 string 就必须自己实现序列化机制,也就是实现 IBinarySerialize 接口。

2. 返回值和 SQL Server 里定义的变量一样,受到 8000 字节的长度限制。

3. SQL Server 2005 必须设置兼容性级别为 "SQL Server 2005(90)", 否则会出现如下错误:
 'EXTERNAL' 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值,
以启用此功能。有关存储过程 sp_dbcmptlevel 的信息,请参见帮助。

4. 需要开启 SQL Server 2005 对 CLR 的支持(如果没有打开的话)。
执行如下命令:
EXEC sp_configure 'clr enabled'1
RECONFIGURE WITH OVERRIDE
GO
原创粉丝点击