Using a common language runtime (CLR) object in Microsoft SQL Server

Do a C# class that can be called in SQL Server:

C# class:

Code Snippet

1. public class CLRTest
2. {
3. public static string GetUrl()
4. {
5. return "http://123code.blogspot.com";
6. }
8. public static int Sum(int x, int y)
9. {
10. return x + y;
11. }
13. }

Right click on project:

- select "Output type" as "Class library"

- click on tab "Build", in Output select "Generate serialization assembly" as "On"

Build the project, we have two assemblies CLRTest.dll and CLRTest.XmlSerializers.dll.

Open Microsoft SQL Server Management Studio.

Enable CLR:

USE AdventureWorks

GO

EXEC sp_configure @configname = 'clr enabled',

@configvalue = 1 RECONFIGURE WITH OVERRIDE

RECONFIGURE

ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON

Create two assemblies:

create Assembly AsmCLRTest AUTHORIZATION dbo

from 'e:\CLRTest.dll'

with Permission_set=EXTERNAL_ACCESS

GO

create Assembly AsmCRLTest_Xml AUTHORIZATION dbo

from 'e:\CLRTest.XmlSerializers.dll'

with Permission_set=SAFE

GO

Create SQL functions:

CREATE FUNCTION [dbo].[getUrl]()

RETURNS [nvarchar](200) WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [AsmCLRTest].[CLRTest].[GetUrl]

GO

CREATE FUNCTION [dbo].[Sum](@x int, @y int)

RETURNS int WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [AsmCLRTest].[CLRTest].[Sum]

GO

Test out:

SELECT dbo.getUrl()

Result: "http://123code.blogspot.com"

SELECT dbo.Sum(4, 5)

Result: 9

Happy programming!