How to enable CLR feature and execute CLR function

What is CLR:

 

The common language runtime (CLR) provides the execution environment for all .NET Framework code. The CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.

 

When to use CLR integration in SQL Server

1. If your procedure or function feature in complex logic.
2. If you want to access class library which you develop using .Net programming language.

Steps to use CLR Integration in SQL Server

 

Step 1 Enable CLR in Sql Server

EXEC sp_configure 'clr enabled', '1'

Step 2 CLR re cofigure

reconfigure

Step 3 Enable trustworthy for unsafe permission on database

ALTER DATABASE <Database_Name> SET TRUSTWORTHY ON;

Step 4 Create Managed code (class library) in C# to show output with example.

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
namespace CLRLibraryFunction
{
public class UserDefinedFunctions
{
[SqlFunction(Name = "GetLongDate",
DataAccess = DataAccessKind.None)]
public static SqlString GetLongDate(SqlDateTime DateVal)
{
return DateVal.Value.ToLongDateString();
}
}
}

Step 5 Create CLR assembly in SQL Server

CREATE ASSEMBLY GetLongDateAssembly
FROM 'F:\CLRLibraryFunction.dll'WITH PERMISSION_SET = UNSAFE

Step 6 Create CLR function in SQL Server

CREATE FUNCTION GetLongDate(@d as DateTime)
RETURNS nVarchar(50)
AS EXTERNAL NAME GetLongDateAssembly.[CLRLibraryFunction.UserDefinedFunctions].GetLongDate

Step 7 Execute Function with employee table

SELECT dbo.GetLongDate(<date>)

That's all. If you have any query or suggestion, kindly revert back with comments.

 

Thanking you.