自定义聚合函数

下载安装vs2005
下载地址：http://www.52z.com/soft/7658.html
新建database Project
新建Concatenate class
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, //use clr 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 class Concatenate : IBinarySerialize
{
/// <summary>
/// The variable that holds the intermediate result of the concatenation
/// </summary>
private StringBuilder intermediateResult;

/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
this.intermediateResult = new StringBuilder();
}

/// <summary>
/// Accumulate the next value, not if the value is null
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlString value)
{
if (value.IsNull)
{
return;
}

this.intermediateResult.Append(value.Value).Append(',');
}

/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
this.intermediateResult.Append(other.intermediateResult);
}

/// <summary>
/// Called at the end of aggregation, to return the results of the aggregation.
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
string output = string.Empty;

if (this.intermediateResult != null && this.intermediateResult.Length > 0)
{
output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
}

return new SqlString(output);
}

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

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


编译 Concatenate class

注册aggregate.dll'

CREATE ASSEMBLY MyAgg FROM 'C:\aggregate.dll'
GO
CREATE AGGREGATE MyAgg (@input nvarchar(200)) RETURNS nvarchar(max)
EXTERNAL NAME MyAgg.Concatenate

其中：
第一个、第三个MyAgg 是程序集名称
Concatenate是类名
第二个MyAgg是聚合函数名称
Concatenate为类名


如果注册失败或执行前失败（禁止在 .NET Framework 中执行用户代码。启用 "clr enabled" 配置选项 ），先执行以下sql

exec sp_configure 'show advanced options', '1';
go
reconfigure;
go
exec sp_configure 'clr enabled', '1'
go
reconfigure;
exec sp_configure 'show advanced options', '1';
go

删除注册
drop AGGREGATE MyAgg
然后到程序集中将其删除