Friday 15 June 2007

Creating and Enabling a CLR Function.

As a very simple example of CLR integration I create a CLR Assembly function to be called from SQL here.

Prerequisite to this exercise > Enabling CLR Integration on SQL server.

Step 1 : Create a basic class in .NET -

  • Start Visual Studio 2005
  • File , New , Project
  • Select 'Visual Basic' (left hand navigation pane) and 'Class Library' (right hand navigation pane).
  • Select OK.
You are presented with a blank class template , which looks like this -


Way too much .net explanation would have to go here, but basic steps are >
  • Name your class
  • Name and code a function to be called from SQL (i've chosen a Roman Numerals one here).
  • Save it!
  • Give it a more sensible name -
    Rename ClassLibrary1 to SQL_CLR_Project
    (Do this in Solution Explorer on the right hand side)
  • File , Save All

Step 2 : Compile the class to a .DLL file >

Change Project properties -
  • Project menu , SQL_CLR_Project Properties
  • Change Assembly Name - I chose 'CLRFunctionLibrary' (This will match the .dll name i.e CLRFunctionLibrary.dll will be generated).
  • Change the Root Name Space. I changed this to 'SQLCLROne' to match the project.

Compile it -
  • Build Menu - 'Build SQLCLROne'

Find the dll just created (CLRFunctionLibrary.dll). -

C:\!RD\Visual Studio 2005\Projects\Class Libraries\SQLCLROne\ClassLibrary1\obj\Debug


Step 3 : Register the assembly within SQL Server -

In TSQL -
CREATE ASSEMBLY [CLRFunctionLibrary]
FROM 'C:\!RD\Visual Studio 2005\Projects\Class Libraries\SQLCLROne\ClassLibrary1\bin\Debug\CLRFunctionLibrary.dll'
WITH PERMISSION_SET = SAFE
GO


These 2 queries verify the Assembly has been created, and will show other assemblies present -
SELECT * FROM sys.assemblies

SELECT * FROM sys.assembly_files


Step 4: Register the method within the assembly. -
CREATE FUNCTION functionName
 (@sqlVariable AS sqlDataType)
RETURNS sqlDataType
AS EXTERNAL NAME Assembly.[NameSpace.ClassName].FunctionName

CREATE FUNCTION dbo.fn_RomanNumeral
 (@number AS INTEGER)
RETURNS NVARCHAR(20)
AS EXTERNAL NAME CLRFunctionLibrary.[SQLCLROne.SQLCLROne].RomanNumeral

Notes :

  1. I had to declare the Namespace when calling from the Assembly i.e the declaration became >
  2. CREATE FUNCTION functionName
    (@sqlVariable AS sqlDataType)
    RETURNS sqlDataType
    AS EXTERNAL NAME Assembly.[NameSpace.ClassName].FunctionName

  3. 2 Use the type of NVARCHAR not VARCHAR to match with the output STRING declared in vb.net.

Now the function is created, you can see it like this -
SELECT * FROM sys.assembly_modules

and test it like this -
select dbo.fn_RomanNumeral(1066)
select dbo.fn_RomanNumeral(1976)
select dbo.fn_RomanNumeral(2007)

If you experience compatibility issues you can determine which version of .NET is installed by querying the sys.dm_os_loaded_modules DMV (dynamic management view).
SELECT *
FROM sys.dm_os_loaded_modules
WHERE [name] LIKE N'%\MSCOREE.DLL'

No comments: