The first thing to do is create the class that will serve as the source for the aggregation. The class that we add will implement four key methods: Init, Accumulate, Merge, and Terminate. It will be decorated with the Serializable and SqlUserDefinedAggregate attributes. The latter is the key to allowing the class to be a UDA.
The next step was something left out of the book. That was implementing the IBinarySerialize interface to go along with the Format.UserDefined value for the SqlUserDefinedAggregate attribute. Using this interface is not necessary if you can use the Format.Native option. I could not, because I was using the StringBuilder internally. (The book mentioned Format.SerializedDataWithMetadata which is not an option that currently exists.)
The named parameters on the SqlUserDefinedAggregate are worth mentioning. The MaxByteSize (whos maximum is 8000) specifies the maximum size of the serialized data. Roughly, that means you can have a maximum size string of 3,999 characters ((8000 - 2 control bytes) / 2 bytes for UTF-16 encoding). The IsInvariantToNulls parameter indicates if null values are ignored. This makes sense with a string, because tagging a null to a string is not relevant. IsVariantToDuplicates determines if duplicate values will be used. False means duplicates will be used. IsInvariantToOrder specifies if the order of the incoming data is relevant. And finally IsNullIfEmpty indicates if a null value is returned if there are no values to be aggregated.
using System;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
namespace SqlAggregateFunctions
{
[Serializable()]
[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize=8000, IsInvariantToNulls=true,
IsInvariantToDuplicates=false, IsInvariantToOrder=true, IsNullIfEmpty=true)]
public class CommaListConcatenate : IBinarySerialize
{
private StringBuilder _list;
public void Init()
{
_list = new StringBuilder();
}
public void Accumulate(SqlString value)
{
if (!value.IsNull)
{
_list.Append(value.ToString());
_list.Append(",");
}
}
public void Merge(CommaListConcatenate group)
{
_list.Append(group._list.ToString());
_list.Append(",");
}
public SqlString Terminate()
{
string result = _list.ToString();
if (result.Length == 0)
return SqlString.Null;
else
return (SqlString)result.Substring(0, result.Length - 1);
}
public void Read(System.IO.BinaryReader r)
{
_list = new StringBuilder(r.ReadString());
}
public void Write(System.IO.BinaryWriter w)
{
w.Write(_list.ToString());
}
}
}
Build the assembly, and then go into Microsoft SQL Management Studio to perform the rest of the work. The first step is essentially to import the assembly into the database. The final step is to create the aggregate which uses your class. The following T-SQL statements accomplish that task:
CREATE ASSEMBLY ConcatenationFunctions
FROM 'C:\Projects\SqlAggregateFunctions\SqlAggregateFunctions\bin\Debug\SqlAggregateFunctions.dll'
CREATE AGGREGATE CommaListConcatenate(@value NVARCHAR(4000))
RETURNS NVARCHAR(4000)
EXTERNAL NAME [ConcatenationFunctions].[SqlAggregateFunctions.CommaListConcatenate]
The return value of the aggregate represents the maximum size of the aggregate in character length (well, +1 for simplicity). You get some nasty errors if you try to substitute something like TEXT instead of NVARCHAR. I thought this was interesting considering TEXT should convert directly to SqlString. Also, note the formatting of the EXTERNAL NAME which is the name of your assembly in your database followed by a dot and then the namespace, dot, and name of your class enclosed in brackets.
The following query can be used against the AdventureWorks database to test the UDA. The result? A nice comma delimited list of e-mail addresses.
SELECT dbo.CommaListConcatenate(P.EmailAddress)
|FROM HumanResources.Employee E
INNER JOIN Person.Contact P ON E.ContactID = P.ContactID
LEFT JOIN HumanResources.EmployeeDepartmentHistory ED ON E.EmployeeID = ED.EmployeeID
WHERE ED.DepartmentID = 3
I cannot say I'll ever find a really good use for the UDA. Concatenating strings using a UDA just has too many limitations to be universally helpful such as the inability to specify a delimiter. However, I thought it was an interesting subject, and I like the fact that I can; even if I never will.