This article will explain how to create a SQL Server CLR function stored in a C# class library without creating a SQL Server database project. We will first briefly explain the concept of .NET common language runtime (CLR) and then build a C# class library using the .NET framework. Then, we will use the C# library to create user-defined CLR functions in SQL Server.
Introduction
The Common Language Runtime (CLR) is the heart of the Microsoft .NET Framework. It provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code.
Several services are provided by the common language to execute programs, such as the just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security.
The execution of the programs developed using .NET is managed by the Just-In-Time compiler (JIT) regardless of the .NET programming language it was created with. A language-specific compiler converts the source code to a common intermediate language (CIL). This language is then converted into machine code by the Just-In-Time (JIT) compiler. This machine code is specific to the computer environment that the JIT compiler runs on.
Figure – Working with JIT Compiler (Image source: GeeksForGeeks)
Using SQL Server CLR, we can define several objects such as stored procedures, user-defined functions, triggers, user-defined types, and user-defined aggregates in managed code.
One of the benefits of SQL Server CLR is that it can achieve significant performance increases in some since managed code compiles to native code before execution. Besides, several complex operations are implemented in .NET easier than SQL Server.
The following section explains how to create a basic C# library using the .NET framework.
Side Note: .NET core and .NET standards cannot be used in CLR integration.
Creating a .NET Class library
Our first step is to create a .NET Class library that contains the C# functions we need to use in SQL Server. To do so, open Microsoft Visual Studio and create a new project:
Figure – Creating a new project
To create a SQL Server CLR class library, we should use a .NET framework class library project.
Figure – Selecting .Net framework class library project
In this article, we will use the .NET framework 4.6 to build our class library, and we will set “SQLExternalFunctions” as a project name.
Figure – Configuring the project name and framework version
Once the Visual Studio IDE main windows are shown, go to the solution explorer tab to the right and change the name of “Class1.cs” to “SQLExternalFunctions.cs”.
Figure – Renaming the main class
Now, we need to write the functions we need to use in SQL Server. First, let us remove the unnecessary parts of the class. Double-click on SQLExternalFunctions.cs class.
In the C# class editor, remove the SQLExternalFunctions namespace curly brackets and the unnecessary imported libraries (only keep the System and System.Linq) as shown in the image below:
Figure – Removing unnecessary code
We need to add the static keyword before the class definition since the SQL Server CLR functions must be defined within a static class.
|
1 2 |
public static class SQLExternalFunctions |
After removing unnecessary parts, we are going to add three functions as follows:
First character occurrences count:
- Input: a string value
- Output: an integer value = the number of occurrences of the first character in the input string.
- Code:
|
1 2 3 4 5 6 7 8 |
public static int FirstCharacterOccurence(string str) { if (string.IsNullOrWhiteSpace(str)) return 0; char ch = str[0]; return str.Where(f => f == ch).Count(); } |
Character occurrences count:
- Input:
- A string value
- A character
- Output: an integer value = the number of occurrences of the input character within the input string
- Code:
|
1 2 3 4 5 6 7 |
public static int GetCharacterOccurence(string str, char chr) { if (string.IsNullOrWhiteSpace(str)) return 0; return str.Where(f => f == chr).Count(); } |
Get current date in ISO format:
- Input: this function does need any input
- Output: a string value of the current date in the ISO format (yyyy-MM-dd HH:mm:ss)
- Code:
|
1 2 3 4 |
public static string GetCurrentDateISO() { return DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); } |
After writing the function, we should add some metadata to our class to let the SQL Server know it contains CLR functions. This metadata is what we call in C# “attributes”. If interested, you can learn more about C# attributes in the following official documentation: Attributes (C#) | Microsoft Docs.
Before each function definition, we need to add the following attribute, as shown in the image below:
|
1 |
[Microsoft.SqlServer.Server.SqlFunction] |
Figure – Adding SqlFunction attribute before the C# function
As mentioned in the Visual Studio tooltip, the SQLFunction attribute “is used to mark a method definition of as user-defined aggregate as a function in SQL Server. The properties on the attribute reflect the physical characteristics used when the type is registered with SQL Server.”
Figure – SqlFunciton attribute description
Once done, we should build our class library. To do so, right-click on the project in the solution explorer and press “Build solution”.
Figure – Build solution
After building the solution, we should copy the created DLL file from the project directory (SQLExternalFunctions\bin\Debug\) to the location where we want to store it (in this article, we will keep it within (D:\CLR Functions\).
Figure – Opening the project directory
Figure – Class library location
Creating a SQL Server CLR user-defined function
After creating the .NET class library, the next step is to create a SQL Server CLR user-defined function that calls the functions from the .NET class library.
First, open the SQL Server Management Studio and open a new query editor.
Figure – Open a new query editor
SQL Server Configuration
Before defining the SQL Server CLR function, we should configure the Server to allow CLR integration. First, we need to enable the advanced options configuration using the following command:
|
1 2 |
EXEC sp_configure 'show advanced options' , 1; RECONFIGURE; |
Next, we should enable the CLR integration option using the following command:
|
1 2 |
EXEC sp_configure 'clr enable' ,1; RECONFIGURE; |
Since this article does not focus on CLR security, we will disable the strict CLR integration security rules already set by SQL Server using the following command:
|
1 2 3 |
EXEC sp_configure 'clr strict security', 0; RECONFIGURE; |
Warning: Turning the CLR strict security is not recommended at all, making the SQL Server vulnerable. You can read more about signing the CLR assemblies and the impact of the CLR strict security in the following article: Impact of CLR Strict Security configuration setting in SQL Server 2017.
Creating an assembly object
To map a CLR class library or application, we should create an assembly object within the SQL Server and map it to the class library file path as follows:
|
1 2 3 |
CREATE ASSEMBLY SQLCLRDemo FROM 'D:\CLR Functions\SQLExternalFunctions.dll'; GO |
Creating the user-defined functions
For each .NET function, we defined in the C# class library, we should define a related SQL Server CLR function where we must specify the same input parameters and output data type. Besides, we should mention the original function name as follows:
|
1 2 3 4 |
CREATE FUNCTION GetFirstCharacterOccurence(<input parameters>) RETURNS <output data type> EXTERNAL NAME <clr assembly>.<class name>.<Function name>; GO |
In this article, we should define the following functions:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE FUNCTION GetFirstCharacterOccurence(@str nvarchar(255)) RETURNS INT EXTERNAL NAME SQLCLRDemo.SQLExternalFunctions.FirstCharacterOccurence; GO CREATE FUNCTION GetCharacterOccurence(@str nvarchar(255), @chr nchar(1)) RETURNS INT EXTERNAL NAME SQLCLRDemo.SQLExternalFunctions.GetCharacterOccurence; GO CREATE FUNCTION GetIsoDate() RETURNS nvarchar(255) EXTERNAL NAME SQLCLRDemo.SQLExternalFunctions.GetCurrentDateISO; GO |
Using the functions
Now, let us try the following queries to test our created functions:
|
1 2 3 4 5 |
SELECT dbo.GetFirstCharacterOccurence('alialawiye'), dbo.GetFirstCharacterOccurence('hadifadlallah') SELECT dbo.GetCharacterOccurence('hadifadlallah','a') SELECT dbo.GETISODATE() |
The image below shows the results of the query execution.
Figure – Query result
Summary
In this article, we briefly explained the .NET common language runtime (CLR) and how to create user-defined SQL Server CLR functions based on a C# class library developed with a .NET framework.
Another option to create and deploy SQL Server CLR functions is by defining them within the SQL Server database project, where they will be created during the database deployment operation.
Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.
On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.
Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.
View all posts by Hadi Fadlallah
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023