Many of you are using Microsoft SQL Server to persist data. I hope that most of you are using Stored Procedures (and the reason why you should use them has been explained again and again).
If you have used SPs in your projects, you surely faced a situation where you would have been ready to do a particular process in the database but the T-SQL language was not powerful enough to do it and you had no other choice of bringing all your data to your application, process the data in a .Net application and push the resulting data back to the database.
This article will show you how to write methods in .Net and to host these methods right into the database so that they can be used directly from the database server without having to send the data to another server/computer.
This feature that was introduced with SQL Server 2005 is also known as the SQL Common Language Runtime (CLR) .Net integration.
This month code
This month, the code is only provided in VB (sorry C# guys but I encourage you to go through the article as the explanations are the same for you). The code provided here has been tested on Visual Studio 2010 and SQL Server 2008 R2.
What you need
To be able to run .Net code directly from SQL Server, you need at least Visual Studio 2005 and at least SQL Server 2005 (the Express edition is supported).
Even if you run at SQL 2005, make sure that the compatibility level of your database is also at least 2005. You can do this by either checking the “Compatibility level” from the Options tab of the properties of your database or by using this command:
exec sp_dbcmptlevel <YourDatabaseName>
The last command needs to return at least level 90.
Enabling the permission to run CLR
If you meet these minimal requirements, there is another one you will have to validate with your DBA or System Administrator. To be able to run .Net code from the database, you will need to enable a permission that is turned off by default (like most permission).
To enable the permissions, run these statements from a query window:
EXEC sp_configure 'clr enabled', 1 GO RECONFIGURE WITH OVERRIDE
These statements enable the CLR integration for the whole server. It can’t be done for a single database.
While you are in SSMS, you might want to create a new database to do your test. I have named mine DemoCLRIntegration.
Creating a new kind of project
There is a project template you might have never used in the past which will come handy for this article as it contains many settings already correctly set for you to start with.
Start your Visual Studio in an administrator mode (by right-clicking your Visual Studio icon and selecting “Run as Administrator”). Once it is opened, create a new a new project and be sure to select the New Project -> Database -> Visual Basic SQL CLR Database Project (or the C# version) template as shown in figure 1 and give it a meaningful name.
Figure 1: Creating the project
Because I use Visual Studio 2010, you might see the message shown in figure 2 (but it may only popup the first time you create one of these projects). What this warning says is that SQL Server 2008 R2 (and earlier version) is not ready to .Net assemblies targeting the .Net Framework 4.0. You need to manually open the properties of your project and select an appropriate version of the .Net Framework as stated by the message box.
Figure 2: Warning about the .Net Framework version
Once you have past these steps, and because you have selected to work with a database project template, it makes sense to connect to a database. This is the purpose of the next dialog (shown in figure 3). Notice that my database was created empty using SSMS before creating that project.
Figure 3: connecting to a database
Last but not least, if you want a great debugging experience between your database project and your database, you need to enable SQL/CLR debugging on the connection. Answering Yes to the next question (shown in figure 4) enables it.
Figure 4: Enabling SQL/CLR debugging
But be warned that you need elevated privileges on the database server if you want to debug stored procedures. I often recommend that you install a database locally and do your development on your local server so that you have all the privileges you need.
Creating the .Net method
Now that we have the project setup correctly, we are ready to start writing some code.
The first thing you need to do is to create a class. Add a plain old class to your project and give it a meaningful name (mine is cEMFunctions).
At the top of this new class, you need to add 2 imports statements:
Imports System.Runtime.InteropServices Imports Microsoft.SqlServer.Server
The first method we will write is useless but it serves well to show you how to create functions.
Consider this method:
<SqlFunction(DataAccess:=DataAccessKind.None, IsDeterministic:=True, SystemDataAccess:=SystemDataAccessKind.None)> _ Public Shared Function EM_Add(ByVal P1 As SqlTypes.SqlDecimal, ByVal P2 As SqlTypes.SqlDecimal) As SqlTypes.SqlDecimal Return P1 + P2 End Function
Have you successfully guessed what this method will do?
One thing worth mentioning is the attribute in front of the method (SqlFunction) is used to indicate that this method will return a value (as opposed to SqlProcedure).
Another important thing to mention is that the method is declared as Public and Shared. When we will use this method in SQL, we won’t (and we can’t) create an instance of the class.
It is now the time for you to compile/build the project. You shouldn’t expect any problems so far.
Testing the method from SQL
In your project, locate the “Test Scripts” folder and open the Test.sql file as shown in figure 5.
Figure 5: the Test.sql testing script file
Open it and scroll completely to the bottom and type this query (in which dbo is the default owner in my database, EM_Add is the name of the method we wrote in the class):
SELECT dbo.EM_Add(10,15)
Now hit F5. If you switch to your Output window, you should see this result confirming that you know how to code an addition method and that you know how to call it from a T-SQL query:
Column1 ----------------------------------------------------------------------------------------------------------------- 25 No rows affected. (1 row(s) returned)
Debugging your .Net code
If you were able to see the results in your Output window, you should also be able to debug your code. To test the debugger, set a breakpoint into your method and hit the F5 button again. The execution should stop on the breakpoint like it normally does.
Deploying to another server
Because we have hit F5 in Visual Studio (in any edition but Express or Standard), all the steps required to run the .Net code from T-SQL where automatically done for you. This is an easy way for testing but I doubt you will be able to deploy your assemblies to your production server easily like this.
There are 5 steps to follow in to deploy an assembly to a SQL server:
The first 2 steps shouldn’t require long explanations.
The following step is a bit strange. An assembly is a special object type that also exists in SQL. It is the way of SQL to add a reference to a DLL. For example, to create an assembly in SQL referencing my DLL I need to use this syntax:
CREATE ASSEMBLY EMAssembly FROM 'C:\EMoreauCLRDemo\bin\Debug\EMoreauCLRDemo.dll'
In which you will replace my path with your own.
Now that you have a reference, you need to create an access point (or a wrapper) in your database for each method of your .Net class. Here is the access point for my EM_Add method:
CREATE FUNCTION [dbo].[EM_Add](@P1 [numeric](18, 0), @P2 [numeric](18, 0)) RETURNS [numeric](18, 0) AS EXTERNAL NAME EMAssembly.[EMoreauCLRDemo.cEMFunctions].[EM_Add]
In this syntax, EMAssembly is the name of the reference I just created. EMoreauCLRDemo is my namespace in my .Net project and cEMFunctions is the name of my class. You can name (and I strongly suggest you do it) the SQL function the same as your .Net method.
Now that you have all this, you can use it as any regular SQL function:
Now you know why it may take so long when you hit the F5 button from within Visual Studio. All these steps are done for you. At least now you know what to do to get your .Net method to a server.
Another method of deploying the assembly
It is very possible that your DBA does not like the idea of copying a DLL to its server.
While I was deploying mine at a client site I discovered something really cool. Once you have your assembly deployed on a test server, you can script it and deploy it to another server without ever copying a DLL to the other server. How do you script exactly like you would do for any other SQL object by right-clicking the assembly (see figure 6 to see where it is hidden) and selecting the script option.
Figure 6: The assembly is hidden under Assemblies!
The generated will contain a long binary string. It can be big but at least the DBA saves the copy of the DLL and the path in the Create Assembly statement.
Using ADO.Net in the method
The EM_Add method is trivial. It takes 2 parameters, add them together and return the result. Your methods don’t have to be as dumb as my demo!
Sometime, you need to access ADO.Net from within your code. Your ADO.Net code will be perfectly standard except for one thing: your connection string.
Check this method:
<SqlFunction(DataAccess:=DataAccessKind.Read)> _ Public Shared Function EM_GetDate() As String Dim cn As SqlConnection = New SqlConnection("context connection=true") cn.Open() Dim cmd As New SqlCommand cmd.Connection = cn cmd.CommandText = "SELECT GetDate()" Dim dtmResult As Date Date.TryParse(cmd.ExecuteScalar.ToString, dtmResult) Return dtmResult.ToString("yyyy-MM-dd") End Function
First, we see that the attributes specify that we will have a read access to the data. Second, the connection is initialized with “context connection=true”. This means that the current connection will be reused to serve the command in that method. There is no need of creating a regular connection string. The current one will be inherited.
Keep your .Net code to the minimum
If the T-SQL language provides a way of processing your data, you should always use the T-SQL syntax. It will always be faster no matter how smart you are (just because the data doesn’t have to be moved in memory). But if you have no other way of processing your data, crank up Visual Studio and start building your own methods.
Think twice before referencing external libraries as you will have to deploy them to the server as well.
Conclusion
After you've mastered basic use of the CLR integration, you can start tackling a whole new world of complex data questions. When faced with a problem, your solution environment is no longer confined to T-SQL but is expanded to the full .Net toolsets.