In order to use .NET code in our SQL Server we must first enable CLR Integration.
CLR Integration is a default feature, but the default is that it is turned off.
So we merely need to turn it on.
We will do this by using the sp_configure command.
The feature we're interested in configuring is called clr enabled.
Notice there is a space in there, no underscore.
We want to set that equal to a value of 1, but the syntax here rather than
use the equal sign so we use a comma.
So this will reconfigure our server to set clr enabled equal to 1.
After issuing a command like that you have to issue another command called
RECONFIGURE and that should be all we need.
We notice at the bottom it says configuration options clr enabled change from 0 to 1.
So in other word, it went from turned off to turn on.
So that's the first step.
CLR is now enabled for the entire instance of SQL Server.
There's no way to turn this on and turn it off for one particular database.
It is a setting that applies to the entire server, not just one database.
Also, once it turned on it will remain on.
So even if you reboot the server when it comes back up, this would still be on.
The only way to turn it off would be to issue the same command and use a 0 instead of 1.
Now that clr is enabled we can add an assembly.
So under myDatabase we have Programmability and the fourth option below that is Assemblies.
It should already be one assembly and there a default assembly
Microsoft.SqlServer.Types.
We can now add our own assembly.
An assembly is basically a DLL that was written in any of the .NET languages.
For now I am going to assume that DLL already exists.
Let's say it was written by a different developer, although a few movies down
the road we're going to be ride in our own DLLs.
So we right-click, say New Assembly, and we will have to say where that file is
and now have an assembly called Hello World.
Again, that's a DLL that was written in C#.
Compiled inside of that assembly is some code to write a stored procedure.
So we will now need to tell SQL Server that we want create a new stored
procedure based on the C# code.
The beginning of the syntax is very similar to other stored procedures.
We will need the keyword CREATE and the keyword PROC and then we have to give it a name.
Then instead of writing code here we're going to tell the machine that this is
external to SQL Server.
So we say EXTERNAL name and then we have to tell it the name.
The first part of this is going to be the name of the Assembly which is helloworld.
The next part is the name of the class that's inside of the Assembly, which I
happen to know is HelloWorldProc.
Then the third part is the name of the particular method we're looking for which
is also HelloWorld.
So that successfully created a new stored procedure.
Again the name in the EXTERNAL name is three parts separated by periods.
It's the name of the assembly, dot name of the class, dot name of the method, and
we have successfully created a stored procedure.
We can go onto our Stored Procedure section and see a new one called test.CLR.
That's exactly what I expected, and let's go ahead and execute that.
And it returns the phrase Hello world!
Again, we did not write any T-SQL there.
All of that was implemented in C#, was compiled into a DLL, we imported the
DLL as an assembly, we created a new stored procedure based on that DLL, and
now we can execute that stored procedure just the exact same way as any other
stored procedure.