Programming Examples

Are you a Programmer or Application Developer or a DBA? Take a cup of coffee, sit back and spend few minutes here :)

SQL-CLR Function – Creating & Using it

1. Introduction to SQL-CLR Function

One can write a function or procedure in SQL server to perform a task. However, writing those in SQL has limited scope as the SQL deals with a restricted number of in-built functions, targeting mainly towards the financial area. In Contrast, the framework has a big scope of functionality area, and one can write nearly any kind of function and/or procedure. We claim a function as SQL-CLR Function when we write it using DotNet framework and deploy it to run on SQL Server database engine. In this article, we are going to see how to write a SQL-CLR function.

2. Load Dot .NET DLL Into SQL Server

First, database developers write the functions in Dotnet framework and then expose that via a DLL. The SQL server, then loads the DLL into DB engine and starts picking up functions exposed by it. In native SQL language, one can write functions, stored procedures, and triggers. In Dotnet framework, besides these, one can write custom Aggregate Functions and can even define own User-defined Types.

Using the Surface Area Configuration tool, one can ask SQL server to allow the injection of Dotnet Framework code in its core engine. This tool ships with the SQL Server installer. The below given steps show enabling the SQL-CLR integration via this tool.

2.1 CLR Config SQL 2005

Step 1

First, we should invoke the Surface Area Configuration tool from the windows start menu. This is shown below:

Accessing Surface Area Configuration in SQL 2005
Accessing Surface Area Configuration in SQL 2005

Step 2

From the displayed window, select Surface Area Configuration for Features link as shown in the below screen shot:

Select Surface Area Config for Feature SQl 2005
Select Surface Area Config for Feature SQL 2005

Step 3

In the Surface Area Configuration dialog box, select the node CLR integration, which comes under the Database Engine group. Now, from the right pane turn-on the checkbox which says, Enable CLR Integration. After that, click the apply button and close the dialog by hitting OK.

Configure CLR Integration SQL 2005
Configure CLR Integration SQL 2005

Now our SQL server is ready to load DLLs written in Dotnet Framework. Let us explore writing a DotNet C# function and deploying that in SQL Server.

Video 1: Enable SQL-CLR Integration in SQL 2005

2.2 CLR Config SQL 2016

Enabling SQL-CLR integration in SQL Server 2016 is slightly different. Here, we must do that through Facets of SQL Server Management Studio. The below picture shows the steps:

Accessing Surface Area Configuration in SQL 2016
Accessing Surface Area Configuration in SQL 2016

3. SQL-CLR & Dot.Net Compatible Data Types

When we write functions in Dotnet framework to use it like SQL functions in the SQL server environment, the first concern gets along in the form of data types. Because of the data types space quota difference in these two distinct domains, Microsoft put in special CLR data types to get across with the SQL Server database engine. The below table shows some crucial SQL-CLR-data types:

SQL ServerDotnet FrameworkCLR Object Types
Binarybyte arraySQLBinary
NChar, NVarcharchar array (or) stringSQLString
Money, DecimalDecimalSQLMoney

Note, the null value in SQL Server cannot match with any primitive data type of core Dot.Net. The matching CLR object for that is DBNull. The above table shows some crucial data types rather than listing them all. Refer MSDN for knowing the variety of other data types.

4. Creating C# SQL-CLR Function

In this section, we will create two functions. The first function does not accept any parameter and returns a string to the caller. The second function takes an integer value, calculates square of it, and then returns that value to the caller. Here, the caller is SQL Server engine and we write the function in C#. Let us start now.

4.1 Open SQL-CLR Project

To create the project, open Microsoft Visual Studio 2005 from start menu. Then open the New Project dialog using the File->New->Project menu. Select database project type from the tree view by navigating to Visual C#->Database. From the displayed right pane pick SQL Server Project. Provide the project name as SQL-CLR_Func and then click ok. The screen shot below shows the steps involved:

Create New Database Project
Create New Database Project

After you click OK, provide connection details to the SQL Server. Now, Visual studio will create a database project for this example.

4.2 Adding SQL-CLR Function

4.1.1 The Default IDE Generated SQL-CLR

After opening the project, Right click on the project name to invoke the context menu and select Add->User-Defined function as shown in the screenshot below. This will open an Add New Item dialog box displaying the all the installed templates. From the template list, select User-Defined Function, provide the C# filename (Say testfunction.cs) in the name text box at the bottom, and then click Add.

Adding User-Defined CLR Function
Adding User-Defined CLR Function

Now we are in a code window and we have default code, which looks like below one. We can see the Add New Item dialog already placed adequate using statements required for writing the function (Marker 1). Also, the IDE takes the function name from filename, which we gave in the User-Defined function dialog (Marker 2). We are going to change this default function soon. We can also see the attribute  SqlFunction added to the function name, and this tells us that the SQL Server engine will invoke this Dotnet CLR function from its core execution engine.

Default SQL-CLR Functions in C# Code Window
Default SQL-CLR Functions in C# Code Window

4.1.2 Modify Default SQL-CLR

First, we will delete the IDE provided function name SQLCLR_Func. The below screenshot shows the examples for SQL-CLR function. You can also refer code Listing -1 from the bottom of this page.

SQL-CLR Functions in C# Code Window
SQL-CLR Functions in C# Code Window

We already saw the details of IDE provided code item (marker 1 and 2). You can notice that we changed the function name as TestFunction and changed the return string value (Marker 3). Next, we wrote a SquareOf function, which accepts an integer as parameter (Marker 4), squares it, and returns the computed value (Marker 5).

You can also note the usage of the CLR data types such as  SqlInt32 and  SqlString. Refer MSDN for a complete list SQL Server CLR data types. With these two new functions, we can now create and deploy the CLR DLL into the SQL Server engine.

4.3 Deploying SQL-CLR Function

Once the function is ready, we can deploy that function using the menu option Build->Deploy. The deploy menu once clicked, deploys our two functions into SQL Server. The picture below shows the menu option required for the deployment of CLR functions.

Deploy SQL-CLR Function to SQL-Server
Deploy SQL-CLR Function to SQL-Server

We should make sure that visual studio is launched as administrator. Because deployment of CLR function in SQL Server requires administration privilege.

4.4 Testing SQL-CLR Function in Visual Studio

OK, we wrote two functions in C# and deployed those functions in SQL Server. Let, we see how we can check these functions working as expected. Have a look at the below picture. The item marked as two shows that the IDE created test.sql file. We can use this file to write our SQL Scripts and execute those written scripts in the Visual Studio Development Environment itself. Remember, the function we wrote is inside the TestFunctions.cs file, which is marked as one in the below picture.

In the Test.sql, we can write SQL Script to call the CLR functions, and the call made in such a way is marked as three in the below picture. Writing SQL scripts in visual studio itself is helpful in case if we want to debug the scripts and you can see how the breakpoint is placed in the very first statement (Marked as 4). When you execute these functions in Visual Studio, you can see the results in the output window.

Testing the SQLCLR Function in Visual Studio
Testing the SQLCLR Function in Visual Studio

The screenshot below shows the output of the function in Visual Studio Debugging output window:

SQL-CLR Function Result in Output Window
SQL-CLR Function Result in Output Window

Video 2: Deploying the SQL-CLR Function to SQL Server DB Engine

5. Deploying and Mapping SQL-CLR Function

5.1 Deploy CLR Assembly DLL

In the previous section, we deployed our SQL-CLR function using visual studio IDE. Sometimes, it may require deploying the application through SQL scripts. Because the setup software requires these TSQL-Scripts while setting up the database in production DB Server. Have a look at the below script:

Configure SQL CLR and Create Assembly using T-SQL - Refer Code Listing 2
Configure SQL CLR and Create Assembly using T-SQL – Refer Code Listing 2

The script enables the CLR inclusion (Marker 2) in the SQL Server engine and the same was done using the Surface Area Configuration Tool, which we saw in section 2 of this article. To set the flag  clr_enabled we set the  Show Advanced Flag to one (Marker 1). When this flag is zero, SQL Server engine will not allow changes to the clr_enbaled.

After setting the flag, we included the Dotnet DLL, which exposes our two new C# CLR functions to SQL server engine. Moreover, this was done by making use of the  Create Assembly TSQL statement as shown in the above script. The Script looks for the Assembly Name (Marker 3) and the path to the DotNet DLL(Marker 4). Once we execute the above statements, SQL Server injects the CLR Function into its DB Engine. From here onwards these two functions can be used as they were an SQL Functions.

5.2 Mapping CLR Functions With SQL Function

Now, have a look at the script below:

Registering DotNet function through T-SQL - Refer Code Listing 3
Registering DotNet function through T-SQL – Refer Code Listing 3

5.2.1 SQL Function Signature

Here, we registered the Dotnet Assembly functions as SQL Server functions. If you have a close look at it, we are creating a SQL Server function by linking it with the functions created in Dotnet C# language. Here, we are creating two functions named CLRTestFunc (Marker 1) and ClrSquare (No Marker. Refer the blue box). In the above script markers 2 and 3 show the return type of the SQL Function. This return type should match with the return type of the C-Sharp version of the function. In the second function, we can see how the SQL sends the parameter to C# function. The Data type of the param should match with c#. See how we used smallint of SQL Server to match with the Int16 of C# function.

5.2.2 Mapping SQL Function with C# CLR Function in the Assembly

Code snippet marked as 4, 5, 6 and 7 links the Dotnet CLR function with the SQL function that we are about to create. Here in the dot notation by-parts say for example Marker 5 & 7, the first one denotes the Assembly Name of the SQL (Note: Assembly name given in Create Assembly Statement), the second part denotes (4 and 5) the C# class name that exposes the function and final one denotes the name of the function in C# side. Now, when we make a call to the function, say ClrSquare, SQL Server know that the function body and business logic are defined in the external assembly and it knows which function to call from that assembly as well.

When you want to remove the assembly from SQL Server, first remove all the functions registered through it. In our case, to remove the assembly MyCLRFunctions, we should first remove the SQL Functions CLRTestFunc and ClrSquare, which points out the assembly.

6. Code Listings

C# Listing 1

T-SQL Listing 2

T-SQL Listing 3

Source Code: Download SQL-CLR Example from Google Drive

Categories: SQL Server

Tags: , , , ,

Do you like this Example? Please comment about it for others!!

This site uses Akismet to reduce spam. Learn how your comment data is processed.