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 dot.net 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:
Step 2
From the displayed window, select Surface Area Configuration for Features link as shown in the below screen shot:
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.
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:
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 Server | Dotnet Framework | CLR Object Types |
|---|---|---|
| Binary | byte array | SQLBinary |
| NChar, NVarchar | char array (or) string | SQLString |
| Bit | boolean | SQLBoolean |
| Int | Int32 | SQLInt32 |
| Money, Decimal | Decimal | SQLMoney |
| DateTime | datetime | SQLDatetime |
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:
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.
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.
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.
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.
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.
The screenshot below shows the output of the function in Visual Studio Debugging 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:
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:
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
//Sample 01: Functions return string modified [Microsoft.SqlServer.Server.SqlFunction] public static SqlString TestFunction() { // Put your code here return new SqlString("Test Function invoked"); } //Sample 02: New function added [Microsoft.SqlServer.Server.SqlFunction] public static SqlInt32 SquareOf(SqlInt16 number) { return new SqlInt32(number.Value * number.Value); } |
T-SQL Listing 2
|
1 2 3 4 5 6 7 8 9 10 11 12 |
sp_configure 'show advanced options', 1; reconfigure; go sp_configure 'clr_enabled', 1 reconfigure; go use pubs; Create Assembly MyCLRFunctions From 'E:\Temp\SQLCLR_Func.dll' ; go |
T-SQL Listing 3
|
1 2 3 4 5 6 7 8 9 |
Create Function CLRTestFunc() returns nvarchar(50) external name MyClrFunctions.UserDefinedFunctions.TestFunction; Go Create Function ClrSquare(@param smallint ) returns int external name MyClrFunctions.UserDefinedFunctions.SquareOf; Go |
Source Code: Download SQL-CLR Example from Google Drive
Categories: SQL Server
Tags: clr_enabled, Enable CLR Integration, sp_configure, SQL-CLR Data Types, T-SQL Create Assembly
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.