Creating .NET User Defined Function in SQL Server 2005
Integration .NET into SQL Server 2005 enables developer to create UDF, stored procedure, UDA (user defined aggregate) and trigger using .NET languages such as VB.NET and C#.
.NET integration brings database objects executed in type safety, because before managed code is executed, CLR performs several checking to make sure the code is safe to run, however you can type keyword unsafe to produce unsafe code.
You can UDF object with 2 ways :
- Manually approach
Create .NET class, compile into assembly, register the assembly into SQL Server and associate UDF to that registered assembly.
- Automatic approach using Visual Studio .NET 2005 (VS 2005)
Create SQL Server project, create UDF, and deploy it onto SQL Server with click of Deploy button.
I’d like to show these 2 approach below :
Manual approach
You can create either in Notepad or in Visual Studio 2005 but using Class Library project type
Then you type reference system.data, system.data.sql, system.data.sqltypes, and Microsoft.sqlserver.server.
You can type partial keyword on the class, it’s new feature in .NET 2.0 so that several developer can work on 1 class, let the system merge it when it is in compile time.
You should add static method with attribute sqlfunction, build that solution, and it will create assembly named ClassLibrary2.dll (for example)
Next, you register the assembly, open SQL Server Management Studio and click New Query button
Register it with sql command REGISTER ASSEMBLY <ASSEMBLY NAME> FROM ‘<PHYSICAL PATH>’ WITH PERMISSION_SET = SAFE | EXTERNAL_ACCESS | UNSAFE.
SAFE = Default permission set, it uses in-process managed provider to access data only within server where the assembly is running.
EXTERNAL_ACCESS = allow to access network resource outside server, creator of this assembly should have external_access permission to create this keyword.
UNSAFE = this kind of permission set can make assembly to access all unmanaged code. Only sysadmin could create this permission set.
To drop assembly issue command : DROP ASSEMBLY <ASSEMBLY NAME>.
Last step is to create UDF associating to registered assembly by issuing command :
CREATE FUNCTION <SCHEMA NAME.FUNCTION NAME> (ARG1, ARG2, …. ARGN)
RETURNS <TYPE> AS
EXTERNAL NAME
<ASSEMBLY NAME>.<NAMESPACE.CLASSNAME>.<METHOD NAME>
Make your CLR enabled in SQL Server 2005 by issuing command :
EXEC sp_configure ‘clr enabled’,1
Reconfigure with override
Go
Second approach using VS 2005
Second approach you should select SQL SERVER PROJECT under DATABASE on left pane.
Connect to sample database AdventureWorks after selecting target server
Test connection, then click OK
Choose YES to be able to debugging SQLCLR
Add 1 class for USER DEFINED FUNCTION
Named it testUDF.cs
Example code below is to get Title from employeeID input at table HumanResouces.Employee, AdventureWorks database.
Compile it, when there is no error, you can register and deploy it to destination SQL Server with 1 click
To make sure your UDF exists at target server, open server explorer in VS 2005 and browse to user defined function
Test it by right click Execute context menu in Visual Studio.
From this context menu, you can also step into.
Regards,
Kasim W, MCSD.NET, MCDBA