.NET CLR Programming with SQL Server 2005 Made Simple - .NET CLR user-defined aggregate in SQL Server 2005 database
(Page 6 of 6 )
Now we shall extend the previous sections to add a stored function to our existing solution.
- Using the “Solution explorer,” right click on the “SampleAllInOne” Project and go to Add-> Aggregate (to create a new aggregate function) (Fig 16).
- You will be presented with the “Add New Item” dialog box. Within that dialog box, select “Aggregate” as the template, provide the name “NetSal” and finally click on “Add” (Fig 17).
Once the “NetSal.vb” is created, modify your code in such a way that it looks something like the following:
ImportsSystem
ImportsSystem.Data
ImportsSystem.Data.SqlClient
ImportsSystem.Data.SqlTypes
ImportsMicrosoft.SqlServer.Server
<Serializable()> _
<Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)> _
PublicStructure NetSal
Public SumNetSal As SqlDouble
Public Sub Init()
' Put your code here
SumNetSal = 0
End Sub
Public Sub Accumulate(ByVal value As SqlDouble)
' Put your code here
Dim sal As Double = CType(value, Double)
Dim hra As Double = sal * 10 / 100
Dim ta As Double = 200
Dim gross As Double = sal + hra + ta
Dim epf As Double = sal * 5 / 100
Dim net As Double = gross - epf
SumNetSal += net
End Sub
Public Sub Merge(ByVal obj As NetSal)
' Put your code here
SumNetSal += obj.SumNetSal
End Sub
Public Function Terminate() As SqlDouble
' Put your code here
Return SumNetSal
End Function
EndStructure
To test the above stored procedure, follow the following steps:
- Using “Solution Explorer,” open “test.sql” in “test scripts.”
- Go to the last line and modify the code as follows:
--select 'To run your project, please edit the Test.sql file in
your project. This file is located in the Test Scripts folder in
the Solution Explorer.'
--exec IncSalaries 50
--select dbo.getNetSalary(empno) from sample.dbo.emp
select dbo.NetSal(sal) from sample.dbo.emp
Press F5 to execute the function and you should be able to see the result.
The next article will follow with .NET CLR based User-Defined Types (UDT) and triggers in SQL Server 2005. This entire solution was developed using Microsoft SQL Server 2005 Developer edition along with Microsoft Visual Studio 2005 Professional Edition on Microsoft Windows Server 2003 Enterprise Edition. I didn’t really test the solution in any other version.
Any feedback, bugs, errors, suggestions, doubts etc., are highly appreciated at jag_chat@yahoo.com.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
