Calling .Net code from SQL Server
Introduction
How do you call .Net code from SQL Server and why would you want to? There are various reasons why you might want to but they all come down to a simple answer, "doing something that is easy to do in .Net but driven from a database".In my case, I want to trigger from one database and if certain changes are made, to log these and then call onto a web service to update a dependent system (that does not run on the same SQL server). Obviously the trigger is easy in SQL but logging and calling web services is much easier in .Net - it is also easier to debug from Visual Studio.
This is how to do it....
Create a Visual Studio Project
Firstly, create yourself a database project in Visual Studio. I believe some of these have changed names but in Visual Studio 2012, there is only one database project called, "SQL Server Database Project". I think the older versions had several projects with example files in them, in which case, choose the "CLR User Defined Function" project.Once this is created, you might or might not have any code but if not, choose "Add New Item" on the project and look under SQL CLR C# for the item called "SQL CLR C# User Defined Function". Give it a name and add it.
Once you see this file, it looks very similar to normal C# but with a special attribute (Microsoft.SqlServer.Server.SqlFunction) that will let it be called from SQL Server. You will also notice that the types live in the System.Data.SqlTypes namespace which ensures they are correctly marshalled between .Net and SQL Server. Otherwise, it is all pretty normal stuff.
Set the Project Properties
Right-click the project in the solution explorer and choose "properties". Here, you can set the names for your assembly (if different from the project name) and also change the target framework to 3.5 if it needs to work on SQL Server 2005/8.You can, and should, also set the Assembly properties so you can more easily keep track of your code. Pressing this button creates an AssemblyInfo file.
If your assembly does anything outside of itself like file IO or network access, it will need permission to do so. You specify this by setting the Permission Level (details are here). If you have chosen anything other than "SAFE", you will need to sign your library. Do this by pressing the Signing button and choosing to sign the assembly, if you do not have a strong key already, you can create one here in the dialog.
Add Login and User
A CLR function needs to be owned by a user (this code will become a database) so you will need to add a user without login to your project and then set this name also in the Project properties against the "Assembly owner" on the first page. If you try and create a login in the project, it will fail deployment later.Build Project
Build and deploy the project, you should get no errors. You might optionally add additional functions or other tables etc. This build should produce a .dacpac file as well as the assembly dll.Prepare the SQL Server
The SQL Server will not allow the CLR object to install or run by default.Firstly, you will need to enable CLR integration for the SQL server. Run the following query against the master database:
sp_configure 'clr enabled', 1
GO
reconfigure
GO
Note this does not require a restart.
Secondly, you need to create a login linked to the key that you used to sign your assembly with. The easiest way is to create an asymmetric key from the assembly file like this:
CREATE ASYMMETRIC KEY MyKeyName
FROM EXECUTABLE FILE = 'C:\Users\Luke\Documents\Visual Studio 2012\Projects\MyProject\bin\Release\MyAssembly.dll'
No password is required in this statement.
If you get an error here, it might be because the directory your assembly lives in does not give the SQL server user permission to read the contents of the directory, in which case, just give "Users" permission to read e.g. ...\Myproject\Bin\Release.
Next, create a server login (I used the user interface) and point it to the key you just created in the "mapped to asymmetric key" dropdown list. Once you create this login, you need to give the login external access permission like this:
USE master
GO
GRANT EXTERNAL ACCESS ASSEMBLY TO [MyLogin]