ADO.NET 3.5 Cookbook, 2nd Edition

12.4. Creating an Aggregate Function

Problem

You need to create a CLR aggregate function.

Solution

This solution creates, registers, and executes a user-defined aggregate function that returns the sum of a SqlMoney column in a table. Follow these steps:

1. Create a new SQL Server project in Visual Studio and name it ClrAggregateFunction.

2. Create an aggregate item in the project. Name the item SumMoney.cs.

3. The C# code in SumMoney.cs in the project ClrAggregateFunction is shown in Example 12-7.

   Example 12-7. SumMoney.cs

   using System;
   using System.Data;
   using System.Data.SqlClient;
   using System.Data.SqlTypes;
   using Microsoft.SqlServer.Server;
   
   [Serializable]
   [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
   public struct SumMoney
   {
       private SqlMoney sum;
   
       public void Init(  )
       {
           sum = 0;
       }
   
       public void Accumulate(SqlMoney Value)
       {
           sum += Value;
       }
   
       public void Merge(SumMoney Group)
       {
           sum += Group.sum;
       }
   
       public SqlMoney Terminate(  )
       {
           return sum;
       }
   }

4. Build the solution.

5. Register the assembly and create the aggregate function by executing the following T-SQL statement in SQL Server Management Studio, replacing <path> appropriately:

   	USE AdventureWorks
   	GO
   
   	CREATE ASSEMBLY ClrAggregateFunction
   	FROM '<path>\ClrAggregateFunction\bin\Debug\ClrAggregateFunction.dll'
   	GO
   
   	CREATE AGGREGATE udfSumMoney
   	    ( @Value money )
   	RETURNS money
   	EXTERNAL NAME ClrAggregateFunction.SumMoney

6. Execute the aggregate function on the Sales.SalesOrderHeader table in AdventureWorks:

    SELECT dbo.udfSumMoney(SubTotal), dbo.udfSumMoney(TaxAmt), ...