At the end of this post you will find my very first attempt to use one of the new features of SQL Server 2005 -
CLR integration. It is a CLR user-defined aggregate that produces a comma-separated (comma plus space to be precise) list of values.
Column1
--------
aaa
bbb --> aaa, bbb, ccc
ccc
My code is a modified version of an example that I found
here.
Global variables
You may expect (at least I did) that if you have a global variable (e.g.
intermediateResult in my example) and you assign it a value in the
Init method then you will be able to use this value in the
Terminate method. Well, that's not true.
If you need to use a value of some variable in the
Terminate method, you have to serialize it in the
Write method (to
save the needed value) and then deserialize it in the
Read method and assign to some variable (to
restore the needed value).
You can of course save and restore more than one variable - use some kind of serializable object to store the values.
Debugging
There is an MSDN article that explains exactly how to debug a CLR user-defined aggregate -
Walkthrough: Debugging a SQL CLR User-Defined Aggregate - nonetheless, I encountered one problem.
I started with a solution that contained two projects - one project contained the aggregate code and the other was for a SQL test script. It is possible to perform debugging in such configuration (I managed to do it, although I am not sure how), but it is a better idea to have
a single SQL Server project with both the aggregate code and SQL test scripts. This is what the IDE expects, so following this advice can spare you lots of hassle.
For example,
MSDN says that to debug a CLR user-defined aggregate you need to enable
CLR Debugging, but it doesn't say that
Application Debugging must also be enabled.
When I worked with a single project, Visual Studio enabled this option
automatically when needed, while with the two-project solution I had to do it myself.
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
[Serializable()]
[SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToNulls=true,
IsInvariantToDuplicates=false,
IsInvariantToOrder=false,
MaxByteSize=8000)]
public class Concat : IBinarySerialize
{
#region Private fields
private string separator;
private StringBuilder intermediateResult;
#endregion
#region IBinarySerialize members
public void Read(BinaryReader r)
{
this.intermediateResult = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
#endregion
#region Aggregation contract methods
public void Init()
{
this.separator = ", ";
this.intermediateResult = new StringBuilder();
}
public void Accumulate(SqlString pValue)
{
if (pValue.IsNull)
{
return;
}
if (this.intermediateResult.Length > 0)
{
this.intermediateResult.Append(this.separator);
}
this.intermediateResult.Append(pValue.Value);
}
public void Merge(Concat pOtherAggregate)
{
this.intermediateResult.Append(pOtherAggregate.intermediateResult);
}
public SqlString Terminate()
{
return this.intermediateResult.ToString();
}
#endregion
}
