Creating a CLR user define aggregate (part 1). Simple CLR aggregate

Published Mar 23 2019 04:23 AM 104 Views
Microsoft
First published on MSDN on Jun 06, 2006

SQL Server 2005 allows creating of User Defined Aggregate in any of the .NET  languages such as C# or VB. For simple cases like SUM or MAX you probably want  to use built-in aggregates, however there are cases  where build-ins are insufficient. In such cases people used to put the business logic on a client on a middle tier. With the new version of SQL Server you can have this logic on a server.


Let’s say company XYZ wants to come up with a way of calculating a bonus for their employees. XYZ uses NWIND database (NWIND database can be downloaded from http://msdn2.microsoft.com/en-us/library/ms143221.aspx ). XYZ wants to have a business rule such that the bonus is never greater than 200% of the salary and each regular sale adds 1% to the bonus and each sale to Germany adds 3% to the bonus.


With the new Sql Server 2005 you can write your own aggregates in C# (or any .NET compatible language). Here is the aggregate.


[Serializable]


[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]


public struct Bonus


{


private int m_nRegularSales;


private int m_nGermanSales;



public void Init()


{


m_nRegularSales = 0;


m_nGermanSales = 0;


}



public void Accumulate(SqlString Country)


{


if (Country == "Germany")


{


++m_nGermanSales;


}


else


{


++m_nRegularSales;


}


}



public void Merge(Bonus Group)


{


m_nRegularSales += Group.m_nRegularSales;


m_nGermanSales += Group.m_nGermanSales;


}



public int Terminate()


{


return Math.Min(200, (m_nRegularSales + 3 * m_nGermanSales));


}


}


And here is a T-SQL query that uses this aggregate to calculate bonus for each employee.


select


Employees.FirstName, Employees.LastName, dbo.Bonus(Orders.ShipCountry)


from


Employees join Orders on Employees.EmployeeId = Orders.EmployeeId


group by


Employees.EmployeeId, Employees.FirstName, Employees.LastName

%3CLINGO-SUB%20id%3D%22lingo-sub-383084%22%20slang%3D%22en-US%22%3ECreating%20a%20CLR%20user%20define%20aggregate%20(part%201).%20Simple%20CLR%20aggregate%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-383084%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Jun%2006%2C%202006%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3ESQL%20Server%202005%20allows%20creating%20of%20User%20Defined%20Aggregate%20in%20any%20of%20the%20.NET%26nbsp%3B%20languages%20such%20as%20C%23%20or%20VB.%20For%20simple%20cases%20like%20SUM%20or%20MAX%20you%20probably%20want%26nbsp%3B%20to%20use%20built-in%20aggregates%2C%20however%20there%20are%20cases%26nbsp%3B%20where%20build-ins%20are%20insufficient.%20In%20such%20cases%20people%20used%20to%20put%20the%20business%20logic%20on%20a%20client%20on%20a%20middle%20tier.%20With%20the%20new%20version%20of%20SQL%20Server%20you%20can%20have%20this%20logic%20on%20a%20server.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ELet%E2%80%99s%20say%20company%20XYZ%20wants%20to%20come%20up%20with%20a%20way%20of%20calculating%20a%20bonus%20for%20their%20employees.%20XYZ%20uses%20NWIND%20database%20(NWIND%20database%20can%20be%20downloaded%20from%20%3CA%20href%3D%22http%3A%2F%2Fmsdn2.microsoft.com%2Fen-us%2Flibrary%2Fms143221.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20http%3A%2F%2Fmsdn2.microsoft.com%2Fen-us%2Flibrary%2Fms143221.aspx%20%3C%2FA%3E%20).%20XYZ%20wants%20to%20have%20a%20business%20rule%20such%20that%20the%20bonus%20is%20never%20greater%20than%20200%25%20of%20the%20salary%20and%20each%20regular%20sale%20adds%201%25%20to%20the%20bonus%20and%20each%20sale%20to%20Germany%20adds%203%25%20to%20the%20bonus.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWith%20the%20new%20Sql%20Server%202005%20you%20can%20write%20your%20own%20aggregates%20in%20C%23%20(or%20any%20.NET%20compatible%20language).%20Here%20is%20the%20aggregate.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%5BSerializable%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%5BMicrosoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)%5D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Epublic%20struct%20Bonus%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eprivate%20int%20m_nRegularSales%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eprivate%20int%20m_nGermanSales%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Epublic%20void%20Init()%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Em_nRegularSales%20%3D%200%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Em_nGermanSales%20%3D%200%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Epublic%20void%20Accumulate(SqlString%20Country)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eif%20(Country%20%3D%3D%20%22Germany%22)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%2B%2Bm_nGermanSales%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eelse%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%2B%2Bm_nRegularSales%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Epublic%20void%20Merge(Bonus%20Group)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Em_nRegularSales%20%2B%3D%20Group.m_nRegularSales%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Em_nGermanSales%20%2B%3D%20Group.m_nGermanSales%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Epublic%20int%20Terminate()%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ereturn%20Math.Min(200%2C%20(m_nRegularSales%20%2B%203%20*%20m_nGermanSales))%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAnd%20here%20is%20a%20T-SQL%20query%20that%20uses%20this%20aggregate%20to%20calculate%20bonus%20for%20each%20employee.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eselect%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEmployees.FirstName%2C%20Employees.LastName%2C%20dbo.Bonus(Orders.ShipCountry)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Efrom%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEmployees%20join%20Orders%20on%20Employees.EmployeeId%20%3D%20Orders.EmployeeId%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Egroup%20by%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EEmployees.EmployeeId%2C%20Employees.FirstName%2C%20Employees.LastName%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-383084%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jun%2006%2C%202006%20SQL%20Server%202005%20allows%20creating%20of%20User%20Defined%20Aggregate%20in%20any%20of%20the%20.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-383084%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerProgrammability%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 23 2019 04:23 AM
Updated by: