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
- If your procedure or function feature in complex logic.
- 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
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.