Using CLR with SQL Server 2012
Video: Using CLR with SQL Server 2012In 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.
- Next steps
Viewers: in countries Watching now:
This course investigates several key database-programming concepts: triggers, stored procedures, functions, and .NET CLR (Common Language Runtime) assemblies. Author Martin Guidry shows how to combine these techniques and create a high-quality database using Microsoft SQL Server 2012. The course also covers real-world uses of the INSERT, UPDATE, and DELETE procedures, and how to build a basic web form to connect to your database.
- Comparing triggers, functions, and stored procedures
- Installing and configuring SQL Server
- Creating a stored procedure
- Returning data using data sets
- Creating user-defined functions
- Using "after," "instead," and nested triggers
- Modifying existing stored procedures
- Implementing logging on DELETE
- Choosing between T-SQL and CLR
- Executing a stored procedure
- Passing parameters
Using CLR with SQL Server 2012
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.
There are currently no FAQs about SQL Server: Triggers, Stored Procedures, and Functions.