March 30, 2016

SQL 2005 - Creating a SQLCLR Function in C-Sharp

1. Introduction

One can write a function or procedure in SQL server to perform a task. However, writing those in SQL has very limited scope as the SQL deals with restricted number of in-built functions, targeting mainly towards the financial area. In Contrast, the framework has big scope 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 SQLCLR function.

2. Loading CLR binary to Database

First, database developers write the functions in Dotnet framework and then expose that through a dll. The SQL server, then, loads the dll into database engine and starts accessing functions exposed by it. In native SQL language, one can write functions, stored procedures, and triggers. In addition to those, one can write Aggregate Function and can even delineate own “User defined Types” in dotnet framework that will later used by the SQL Engine.

Using the “Surface Area Configuration” tool, one can demand SQL server to allow the injection of Dotnet Framework code in the SQL Server Database engine. This tool ships with the SQL Server installation and the below given steps show enabling the SQL-CLR integration:

Watch Video 1 (or) Follow steps given below it:

Video 1: Surface Area Configuration – Turn-on SQLCLR

1) First, invoke the “Surface Area Configuration” tool from the windows start menu. This is shown below:

Surface-Area Configuration
Fig 1. Surface-Area Configuration from Start Menu

2) From the displayed window, Select “Surface Area Configuration for Features” link as shown in the below screen shot:

Surface Area Configuration
Fig 2. Surface Area Configuration

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.

Enabling CLR Integration
Fig 3. Enabling CLR Integration

Now our SQL server is ready to load DLLs written in Dotnet Framework. Let, we explore writing a dotnet c# function and deploying that in SQL Server.

3. SQLCLR versus SQL datatypes

When we write functions in Dotnet framework and try to use that like SQL functions in the SQL server environment, the first trouble gets along in the form of datatypes. Because, the datatypes space allotment may be different in these two different domains. Therefore, to overwhelm this problem, Microsoft put in special CLR datatypes to communicate with the SQL Server database engine. The below table shows some of the important “SQL-CLR-DataTypes”:

SQL Server
Dotnet Framework
CLR Object Types
byte array
NChar, NVarchar
char array or string
Money, Decimal
Table 1: Some Frequently used SQLCLR Data Types

Note that null value in SQL Server cannot match with any primitive datatype of core dotnet. 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 datatypes.

4. Creating SQLCLR 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, computes square of it, and then returns that value to the caller. Here, the caller is SQL Server engine and the function, which we are going to write using “Dotnet Frame Work”. Let us start now.

4.1 Creating a Project for SQLCLR

To create 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” and from the displayed right pane pick “SQL Server Project”. Provide the project name as “SQLCLR_Func” and then click ok. The screen shot below shows the steps involved:

Creating SQLCLR Project in VC#
Fig 4. Creating SQLCLR Project in VC#

4.2 Adding the function

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 screen shot 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 textbox at the bottom, and then click OK.

Adding a Default User-Defined Function
Fig 5. Adding a Default User-Defined Function

Now we are in a code window and we have some default code, which looks like below one. We can see that the “Add New Item” dialog already placed adequate “using statements” required for writing the function (Marked as 1). Similarly, the IDE takes the function name from given file name (Marked as 2). We are going to change this default function soon. Also, note the attribute “SqlFunction” added to the function name and this tells us that the SQL Server engine will invoke this Dotnet CLR function.

The Default .cs File Content
Fig 6. The Default .cs File Content

Now have a look at the couple of examples written by deleting the existing function SQLCLR_Func. The below screen-shot shows the example:

C# SQL CLR Function Example - Refer Code Listing 1
Fig 7. C# SQL CLR Function Example - Refer Code Listing 1

We already saw the details of item marked as one and two in the earlier sections of this article. You can notice that we changed the function name as TestFunction and changed the return string value (Marked as three). Next, we wrote a new function called “SquareOf”, which accepts an integer as parameter (Marked as 4), squares that parameter, and returns the computed value (Marked as 5).

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

4.3 Deploying CLR Function in SQL Engine

Once the function is ready, we can deploy that function using the menu option “BuildèDeploy <name>”. The deploy menu once clicked, deploys the two functions (In our case) in SQL Server. The picture below shows the menu option required for the deployment of CLR functions.

Deploy C# CLR Function to SQL Server
Fig 8. Deploy C# 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 the Function

OK, we wrote couple of 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
Fig 9. Testing the SQLCLR Function in Visual Studio

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

Test Result in the Output Window
Fig 10. Test Result in the Output Window

Video 2: Deploying the Function

Video 2:

5. Usage of T-SQL Script for deployment

In the previous section, we deployed our SQL CLR function using visual studio IDE. Sometime, it may require deploying the application through SQL scripts. Because, the setup software have more control over these TSQL-Scripts. Have a look at the below scripts:

Configure SQL CLR and Create Assembly using T-SQL
Fig 11. Configure SQL CLR and Create Assembly using T-SQL - Refer Code Listing 2

The script enables the CLR inclusion (Marked as 2) in the SQL Server engine and the same was done using the “surface area configuration tool” as shown in section 2 of this article. To set the flag ”clr_enabled” we set the “Show Advanced Flag” to one (Marked as two). 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 functions to SQL server engine. Moreover, this was done by making use of the “Create Assembly” TSQL statement as shown in the above script. It looks for the Assembly Name (Marked as 3) and the path to the dotnet dll (Marked as 4). Once we execute the above statements, we have successfully set the dotnet assembly.

Now, have a look at the script below:

Registering DotNet function through T-SQL
Fig 12. Registering DotNet function through T-SQL - Refer Code Listing 3

Here, we registered the Dotnet Assembly functions as SQL Server functions. If you have a close look at it, we are actually creating a SQL Server function by linking it with the functions created in Dotnet C# (In our case) language. Here, we are creating two functions named CLRTestFunc (marked as 1) and ClrSquare (Not markedJ). In the above script marked as 2 and 3 shows the return type of the SQL Function. This return type should match with the return type of 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. Here also the Datatype should match with c#. Note that we used smallint (SqlServer side) to match with Int16 of C# function.

Code snippet marked as 4, 5, 6 and 7 links the Dotnet CLR function with the SQL function that we are about the create. Here, 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 (6 and 7) in c# side. Now, when we make a call to the function, say ClrSquare, SQL Server know that the function body and business logic defined in the external assembly and it know 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 refers the assembly.

6. Code Listings

Listing 1:

//Sample 01: Functions return string modified
public static SqlString TestFunction()
    // Put your code here
    return new SqlString("Test Function invoked");

//Sample 02: New function added
public static SqlInt32 SquareOf(SqlInt16 number)
    return new SqlInt32(number.Value * number.Value);

Listing 2:

sp_configure 'show advanced options', 1;

sp_configure 'clr_enabled', 1

use pubs;
Create Assembly MyCLRFunctions From
'E:\Temp\SQLCLR_Func.dll' ;

Listing 3:

Create Function CLRTestFunc()
       returns nvarchar(50) external name

Create Function ClrSquare(@param smallint )
       returns int external name
Source Code: Download

No comments:

Post a Comment

Leave your comment(s) here.

Like this site? Tell it to your Friend :)