Creating a custom SQL aggregate function in C#

The fact that sql2005 can host the CLR was a big selling point. I kind of always knew that it was possible to extend and create new SQL functions in C# but never really took the time to do it myself.

What do we want to do?

I’d like to be able to create a function that works like the .NET String.Join static method. Here is a sample query that would demonstrate the use our new aggregate function:

select PizzaRecipeId, dbo.strjoin(IngredientName)
from PizzaRecipe
group by PizzaRecipeId

This query would output something like:

1 Cheese, Chicken, BBQ Sauce
2 Cheese, Ham
3 Cheese, Chicken, Pineapple

Let’s do it :)

The first thing is to write the c# code.
Create a new class library project and add a new class called Concatenate. Our new class must:
- have a serializable attribute
- have the SqlUserDefinedAggregate attribute
- implement IBinarySerialize
- have the following 4 methods (there is no interface for them)
o public void Init()
o public void Accumulate(SqlString value)
o public void Merge(Concatenate other)
o public SqlString Terminate()

The implementation itself is straightforward, using a string builder to store the data:

[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize=8000)]
public class Concatenate : IBinarySerialize
{
private StringBuilder sb;

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

public void Accumulate(SqlString value)
{
if (sb.Length != 0)
{
sb.Append(", ");
}

sb.Append(value);
}

public void Merge(Concatenate other)
{
sb.Append(other.sb);
}

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

#region IBinarySerialize Members

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

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

#endregion
}

If the project compiles, let’s continue with the commands that will actually load the dll into sql server and reference the aggregate function:

create assembly clr_integration from 'C:\dev\learning\sql2005\ \clr_integration\bin\debug\clr_integration.dll' with permission_set = safe
GO

create aggregate strjoin(@input nvarchar(200)) returns nvarchar(max)
external name clr_integration.Concatenate

The permission_set is safe because the assembly does not perform any interop or contains any unsafe blocks.

Let’s create a table and some dummy data:


if (object_id('PizzaRecipe') is not null) drop table PizzaRecipe
create table PizzaRecipe
(
PizzaRecipeId int NOT NULL,
IngredientName nvarchar(200)
)

insert PizzaRecipe values(1, 'Cheese')
insert PizzaRecipe values(1, 'Chicken')
insert PizzaRecipe values(1, 'BBQ Sauce')
insert PizzaRecipe values(2, 'Cheese')
insert PizzaRecipe values(2, 'Ham')
insert PizzaRecipe values(3, 'Cheese')
insert PizzaRecipe values(3, 'Chicken')
insert PizzaRecipe values(3, 'Pinnaple')

Everything should now be ready and it’s time to test it :)

Writing the aggregate function was actually simpler than what I imagined. It’s now just a matter of detecting when this type of feature can be used.

TFS: removing _svn folders

I recently had the need to clean up a project in TFS. When it was imported, little care had been put in making sure that only the required files would be on source control.

The most noticable folders that were to be removed from TFS were: '_svn', 'bin' and 'obj'.

As I'm not a big fan of doing things manually (yes, even if doing it manualy is faster - well in that case it was probably faster to write the script) I ended up trying to remember how to do those fancy FOR loops in DOS :)

First, build a list all the folders (or files) you want to remove:

dir /s /b _svn bin obj > files_to_delete

Creating that file is handy and you can edit it before running the following query:

for /f "delims=" %g in (files_to_delete.txt) do tf.exe delete "%g" /login:DOMAIN\login,password

note: make sure that tf.exe is in your path, or replace it with the full path.

you can now refresh your pending checkin in visual studio and check it in !