标题：使用C#实现SQL Server2005的扩展聚合函数
出处：大鹏天空™
时间：Tue, 18 Nov 2008 18:12:37 +0000
作者：zpgm
地址：http://www.rocsky.net/blog/read.php/472.htm

内容：
select xh, dbo.joinstr(value) from t_table group by xh


其中joinstr是一个聚合函数，功能是将每一组的某个字符串列的值首尾连接。上面的SQL也可以查询图2所示的结果。但遗憾的是，sql server2005并未提供可以连接字符串的聚合函数。下面我们就来使用C#来实现一个扩展聚合函数。

     首先用VS2008/VS2005建立一个SQL Server项目，如图6所示。

点击在新窗口中浏览此图片
http://www.rocsky.net/blog/attachment.php?fid=25

点击“确定”按钮后，SQL Server项目会要求连接一个数据库，我们可以选择一个数据库，如图7所示。

点击在新窗口中浏览此图片
http://www.rocsky.net/blog/attachment.php?fid=26

然后在工程中加入一个聚合类（joinstr.cs），如图8所示。

点击在新窗口中浏览此图片
http://www.rocsky.net/blog/attachment.php?fid=27

 joinstr.cs中的最终代码如下：

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined, //use custom serialization to serialize the intermediate result
    IsInvariantToNulls = true, //optimizer property
    IsInvariantToDuplicates = false, //optimizer property
    IsInvariantToOrder = false, //optimizer property    
    MaxByteSize = 8000) //maximum size in bytes of persisted value
]

public struct joinstr :IBinarySerialize
{
    private System.Text.StringBuilder intermediateResult;
    
    public void Init()
    {
        // 在此处放置代码
        intermediateResult = new System.Text.StringBuilder();
    }

    public void Accumulate(SqlString Value)
    {
        intermediateResult.Append(Value.Value);
    }

    public void Merge(joinstr Group)
    {
        intermediateResult.Append(Group.intermediateResult);
    }

    public SqlString Terminate()
    {
        return new SqlString(intermediateResult.ToString());
    }

    public void Read(BinaryReader r)
    {
        intermediateResult = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
        w.Write(this.intermediateResult.ToString());
    }
}



由于本例需要聚合字符串，而不是已经被序列化的类型，如int等，因此，需要实现IBinarySerialize接口来手动序列化。使用C#实现SQL Server聚合函数，也会受到字符串最大长度为8000的限制。

     在编写完上述代码后，可以使用Visual Studio来部署（右向工程，在弹出菜单上选“部署”即可）。也可以使用SQL语句来部署。假设上面的程序生成的dll为MyAggregate.dll，可以使用下面的SQL语句来部署：

CREATE ASSEMBLY MyAgg FROM 'D:\test\MyAggregate.dll'

CREATE AGGREGATE joinstr (@input nvarchar(200)) RETURNS nvarchar(max)
EXTERNAL NAME MyAgg.joinstr

要注意的是，字符串类型需要用nvarchar，而不能用varchar。

第一条SQL语句是装载dll，第二条SQL语句是注册joinstr聚合函数（每一个C#类就是一个聚合函数）

在执行上面的SQL语句之前，需要将SQL Server2005的clr功能打开。如图9所示。

点击在新窗口中浏览此图片
http://www.rocsky.net/blog/attachment.php?fid=28

或执行：
EXEC sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
GO
如果想删除上面建立的聚合函数，可以使用如下的SQL语句：
drop aggregate joinstr

在删除聚合函数后，可以将MyAggregate.dll卸载。
drop assembly MyAgg

OK，现在可以使用joinstr来聚合字符串了。




Generated by Bo-blog 2.1.1 Release