<

Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

SQL CLR .Net Integration in 2015 (year not product version)

Published on
3,900 Points
800 Views
1 Endorsement
Last Modified:
Éric Moreau
Senior Consultant for .Net VB & C# developer (mostly for Windows Forms project type). Also a nominated as a Microsoft MVP from 2004 to 2017.
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still read this article from here.
 
Creating a DLL to be hosted in SQL Server let you run code directly in the database. You may think of it as a Stored Procedure written in VB or C#. it might be helpful to prevent doing large processing on the client after having transited a lot of data on the network wire.
 
Lately, I was reviewing training material on that topic to see if it was still accurate and found out that many things have changed since then. We are now using Visual Studio 2013 (or 2015 in CTP) and SQL Server 2014.


Downloadable code


This month code is available in both VB and C#. The solution has been created using Visual Studio 2105 CTP 5 but would also work the same way if you are using VS 2013.
 

You will also need Microsoft SQL Server. I have used SQL Server 2014 but should also work with SQL Server 2012.

You will also need to install Microsoft SQL Server Data Tools (aka SSDT) which are tools added to Visual Studio to provide a better interaction between the 2 products. If you don’t already have it, you can get it from https://msdn.microsoft.com/en-us/data/hh297027. SSDT is also installable right from within Visual Studio. The code can be downloaded from here.


Simple method

 

We will start by creating a very simple method that adds 2 digits together just to get our feet wet with the process and we will build up from there on a real re-usable CLR procedures.
 

So start your Visual Studio 2013 or 2015 (with SSDT installed) and create a new project. But not just any kind of project, a “SQL Server Database Project” that you will find under the SQL Server templates folder. As opposed to any other projects you created, there is no template letting you choose VB or C#. At this point, you are just creating a database project.

Figure 1: Creating a database project 
EricMoreau1.PNG
Before going further with this project, we need set some of the project properties. So open the project’s properties and go to the project Settings tab. The most important setting on that tab is the target platform. Ensure you are targeting the version of SQL Server you will be running your code from. You have a wide range of version from 2005 to 2014 and even Azure.  

Figure 2: Ensuring the targeted platform 
EricMoreau2.PNG
Now that we have the database version set properly, you can open the SQLCLR tab. There are 2 important settings here. The first one is the Target framework which you need to set to the version installed on your server. Any version from the .Net framework 2.0 up to the most current one are available.

The second important property that you can set here is the language. By default, a new project will be created to use C# but you can change it to Visual Basic here. I strongly suggest that you change it right now if it is your intent to use VB.  

Figure 3: Setting the Language 
EricMoreau3.PNG
We have now customized the most important properties and are ready to add our little function.

Lets now create a new function. Right-click the project, select “Add” and then “New item…”.  

Figure 4: Adding a new item 
EricMoreau4.PNG
From the “Add New Item” dialog, be sure to select “SQL CLR [VB|C#]” (depending on the language you selected in the properties) from the left pane. Then select the type of object you want to create. For my demo, I have selected a User Defined Function. This is also the place where you should set the name of your new class.

Figure 5: Adding a new UDF to the project 
EricMoreau5.PNG
After you clicked the Add button, a new class is added to your project with a sample function returning a string. I have replaced this sample method with this code:  

<Microsoft.SqlServer.Server.SqlFunction()>
Public Shared Function EM_Add(p1 As SqlInt32, p2 As SqlInt32) As SqlInt32
	Return p1.Value + p2.Value
End Function

Open in new window


You will observe 2 things. The first thing is the attribute indicating that this method is special, it is a SqlFunction. The second thing is the data type used. We are using SQL types instead of .Net native types.
 

In my demo project, I have also created in the same class, a second method using the same template called EM_Sub in which I have changed operation from a + to a -. 
 

Deploying it to the database


Now build your project to ensure your don’t have issues. When you will have your project building, right-click your project and select Publish.  

Figure 6: Publishing the project 
EricMoreau6.PNG
This dialog will show up but the target database connection will be empty. You can type the connection string that fit your environment or you can click the Edit button to have another dialog letting you enter the values that generates the connection string. You can click the Publish button to have your new database created (named DemoSqlClr as per the publish dialog) and your project gets deployed.
 

If everything went well, you will get the results shown in figure 7.

Figure 7: Project deployed 
EricMoreau7.PNG
At this point, your database has been created and your new functions are ready to be used. If you now open SQL Server Management Studio (SSMS), you will find your new database. 

Figure 8: Your new database from SSMS 
EricMoreau8.PNG
If you start drilling into it and open the Programmability folder, you will find a name you should be familiar with (DemoSqlClr in my case) and your new functions under the Scalar-value Functions folder.
 

You might be tempted to right-click one of your function and select Modify. You can do it but don’t expect to see VB (or C#) code in there. Instead, you will find this code:  

ALTER FUNCTION [dbo].[EM_Add](@p1 [int], @p2 [int])
RETURNS [int] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [DemoSqlClr].[DemoSqlClr.UserDefinedFunctions].[EM_Add]

Open in new window


What does this code means? This code acts like a proxy (or a pointer) to another method using that signature found in the name following the EXTERNAL NAME. That means that whenever you will call this SQL Scalar-value function, the external .Net code will gets called.
 

To try it, from a query window in SSMS, you can enter a queries like these: 

SELECT dbo.EM_Add(5,13)
SELECT dbo.EM_Sub(22,14)

Open in new window


If it is the first time ever you are trying to run CLR code on your server, you might get an error.

Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

Open in new window


This is because CLR is off by default on SQL Server. To enable it, just run these statements from SSMS: 

EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE WITH OVERRIDE 

Open in new window



Debugging the method


You might now want to debug your .Net code. I doubt this simple method has an error but if your method is not as trivial as mine, chances are that a debugging session would be helpful. We cannot just run into code like this, we need to create a SQL query that will call our new function.
 

Go back to your Visual Studio project, right-click on your project, click Add and New Item … (as shown in figure 4). This time, select the User Scripts tab and select “Script (Not in build)” item.  

Figure 9: Adding test script 
EricMoreau9.PNG
That will generate a new empty .sql file. You can enter queries in that file just like we did in SSMS. For example: 

select dbo.EM_Add(5,13)

Open in new window


You might get used to hit F5 at this point but don’t. Hitting F5 will just deploy your project to the server, it won’t run the query. Instead, you need to hit CTRL-SHIFT-E or hit the green arrow from the top left of you query editor. 

Figure 10: Executing a query from Visual Studio 
EricMoreau10.PNG
After it has been executed correctly, results will show up just underneath. This is not really what I call debugging! Just to the right of green arrow, there is a small arrow meaning that this command is in fact a drop down of commands: 

Figure 11: More commands available 
EricMoreau11.PNG
If you pick “Execute With Debugger”, you should be entered to the real debugging mode you are used to from which you can hit F11 until you get in your .Net code. Notice that most .Net debugging are fully working (Tooltips, Call stack, Locals, Watch Windows, …). Some features like setting the next line to be executed are not working. Edit-and-continue is also not working. 


Adding new functions


After you have created your first version of your assemble, you will probably find out more features to put into it. This is quite simple. Just continue adding methods to your class (or create a new class in the same project) and re-publish it just like we did before (see figure 6). 


Deploying to the production SQL Server


Chances are that you cannot connect to your SQL Server using Visual Studio just like we did here. Your DBA might not really to copy DLLs to the server.
 

What we’ve done here on a test server can all be scripted. You first need to create a script of the assembly itself as shown in figure 12. 

Figure 12: Scripting the assembly 
EricMoreau12.PNG
You also need to create scripts for each functions you created.
 

You finally need to append all the scripts (assembly and each function) to a big script that you add to your deployment scripts just like any database update scripts. Remember that you might have to enable the CLR on the production server as well. 


Replacing existing SQL features


I have always been one of those saying that, if the feature exists natively in SQL, you should never rewrite it. Lately, I was proven wrong on a specific feature. It is probably due to the fact that XML is used and this usage of XML is very not optimized. I would never rewrite simple T-SQL features but I might do some performance tests when dealing with more complex features.
 

Microsoft documented some advantages of using SQL-CLR integration here


Concatenating strings by group


This is something we often have to do. We want to concatenate in a single string all the values for a group. Using the STUFF T-SQL function and a bit of XML, you can achieve that. I even blogged about it here.
 

Somebody pointed me out to a CLR method somebody created and publicly available on CodePlex. I first argue that the STUFF T-SQL feature was surely faster be I was told that it was surprisingly fast. I was not convinced! I created a test database with a table of 1,000,000 rows and created to queries, one with STUFF and another one with the CLR function. I was surprised to find out that the CLR was running quite faster (3 seconds compared to 9 seconds for the STUFF method).

So I have first apologize. Now I will setup a test database to compare both methods.


Conclusion


Over the years things are changing a bit but good stuff stick around. This article is an example. Using older versions of software, you were able to do it but this feature is now still working a bit differently.
 

I really think it was worth revisiting this article 5 years later because being able to run .Net code in a database is still a very valid solution especially that it can give you a performance boost in some scenario!
1
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
6 Comments
 
LVL 70

Author Comment

by:Éric Moreau
>>the only alternative is to ask that an article be changed enough such that it doesn't appear as duplicated content to Google's search engine.

Is there an easy way for us authors to detect when changes are enough? Do you have a tool?
0
 
LVL 70

Author Comment

by:Éric Moreau
Thanks for the complete explanation. Since there is no easy way for me to detect if my article is still considered plagiarism of my own stuff, I won't continue trying to publish articles here. Too bad for EE and I. I have to decide where I will publish my articles and I decided that it will be my own site.

So how do we delete this submission?
0
 
LVL 70

Author Comment

by:Éric Moreau
Tx Jim
0
Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

 
LVL 70

Author Comment

by:Éric Moreau
Thanks. the rules are still the same as they used to be back in May?
0
 
LVL 66

Expert Comment

by:Jim Horn
We have a checker and can tell, rule is minimum 85% original content.  Two exceptions are now allowed
  • Vendor accounts can do whatever the heck they want.
  • Previously published by the author under their own name, as long as we check the NoIndex checkbox so search engines can't search to it.

That and Netminder and Todd Lherrou were shown the door, I'm apparently the only article editor left standing, and I like this article.
0
 
LVL 70

Author Comment

by:Éric Moreau
Thanks Jim. Don't give up.
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Join & Write a Comment

In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month