Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
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.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98382 Viewers
61 Video lessons · 85669 Viewers
71 Video lessons · 69603 Viewers
56 Video lessons · 101928 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.