1
00:00:00,000 --> 00:00:06,990
In order to use .NET code in our SQL Server we must first enable CLR Integration.

2
00:00:07,000 --> 00:00:10,990
CLR Integration is a default feature, but the default is that it is turned off.

3
00:00:11,000 --> 00:00:12,990
So we merely need to turn it on.

4
00:00:13,000 --> 00:00:18,990
We will do this by using the sp_configure command.

5
00:00:19,000 --> 00:00:23,990
The feature we're interested in configuring is called clr enabled.

6
00:00:24,000 --> 00:00:26,990
Notice there is a space in there, no underscore.

7
00:00:27,000 --> 00:00:31,990
We want to set that equal to a value of 1, but the syntax here rather than

8
00:00:32,000 --> 00:00:34,990
use the equal sign so we use a comma.

9
00:00:35,000 --> 00:00:39,990
So this will reconfigure our server to set clr enabled equal to 1.

10
00:00:40,000 --> 00:00:43,990
After issuing a command like that you have to issue another command called

11
00:00:44,000 --> 00:00:49,990
RECONFIGURE and that should be all we need.

12
00:00:50,000 --> 00:00:54,990
We notice at the bottom it says configuration options clr enabled change from 0 to 1.

13
00:00:55,000 --> 00:00:57,990
So in other word, it went from turned off to turn on.

14
00:00:58,000 --> 00:00:59,990
So that's the first step.

15
00:01:00,000 --> 00:01:03,990
CLR is now enabled for the entire instance of SQL Server.

16
00:01:04,000 --> 00:01:07,990
There's no way to turn this on and turn it off for one particular database.

17
00:01:08,000 --> 00:01:12,990
It is a setting that applies to the entire server, not just one database.

18
00:01:13,000 --> 00:01:14,990
Also, once it turned on it will remain on.

19
00:01:15,000 --> 00:01:19,990
So even if you reboot the server when it comes back up, this would still be on.

20
00:01:20,000 --> 00:01:26,990
The only way to turn it off would be to issue the same command and use a 0 instead of 1.

21
00:01:27,000 --> 00:01:29,990
Now that clr is enabled we can add an assembly.

22
00:01:30,000 --> 00:01:35,990
So under myDatabase we have Programmability and the fourth option below that is Assemblies.

23
00:01:36,000 --> 00:01:39,990
It should already be one assembly and there a default assembly

24
00:01:40,000 --> 00:01:41,990
Microsoft.SqlServer.Types.

25
00:01:42,000 --> 00:01:43,990
We can now add our own assembly.

26
00:01:44,000 --> 00:01:48,990
An assembly is basically a DLL that was written in any of the .NET languages.

27
00:01:49,000 --> 00:01:51,990
For now I am going to assume that DLL already exists.

28
00:01:52,000 --> 00:01:55,990
Let's say it was written by a different developer, although a few movies down

29
00:01:56,000 --> 00:01:58,990
the road we're going to be ride in our own DLLs.

30
00:01:59,000 --> 00:02:03,990
So we right-click, say New Assembly, and we will have to say where that file is

31
00:02:04,000 --> 00:02:10,990
and now have an assembly called Hello World.

32
00:02:11,000 --> 00:02:14,990
Again, that's a DLL that was written in C#.

33
00:02:15,000 --> 00:02:18,990
Compiled inside of that assembly is some code to write a stored procedure.

34
00:02:19,000 --> 00:02:22,990
So we will now need to tell SQL Server that we want create a new stored

35
00:02:23,000 --> 00:02:25,990
procedure based on the C# code.

36
00:02:26,000 --> 00:02:28,990
The beginning of the syntax is very similar to other stored procedures.

37
00:02:29,000 --> 00:02:38,990
We will need the keyword CREATE and the keyword PROC and then we have to give it a name.

38
00:02:39,000 --> 00:02:43,990
Then instead of writing code here we're going to tell the machine that this is

39
00:02:44,000 --> 00:02:45,990
external to SQL Server.

40
00:02:46,000 --> 00:02:51,990
So we say EXTERNAL name and then we have to tell it the name.

41
00:02:52,000 --> 00:02:57,990
The first part of this is going to be the name of the Assembly which is helloworld.

42
00:02:58,000 --> 00:03:01,990
The next part is the name of the class that's inside of the Assembly, which I

43
00:03:02,000 --> 00:03:07,990
happen to know is HelloWorldProc.

44
00:03:08,000 --> 00:03:12,990
Then the third part is the name of the particular method we're looking for which

45
00:03:13,000 --> 00:03:17,990
is also HelloWorld.

46
00:03:18,000 --> 00:03:20,990
So that successfully created a new stored procedure.

47
00:03:21,000 --> 00:03:23,990
Again the name in the EXTERNAL name is three parts separated by periods.

48
00:03:24,000 --> 00:03:31,990
It's the name of the assembly, dot name of the class, dot name of the method, and

49
00:03:32,000 --> 00:03:34,990
we have successfully created a stored procedure.

50
00:03:35,000 --> 00:03:39,990
We can go onto our Stored Procedure section and see a new one called test.CLR.

51
00:03:40,000 --> 00:03:48,990
That's exactly what I expected, and let's go ahead and execute that.

52
00:03:49,000 --> 00:03:49,990
And it returns the phrase Hello world!

53
00:03:50,000 --> 00:03:53,990
Again, we did not write any T-SQL there.

54
00:03:54,000 --> 00:03:59,990
All of that was implemented in C#, was compiled into a DLL, we imported the

55
00:04:00,000 --> 00:04:04,990
DLL as an assembly, we created a new stored procedure based on that DLL, and

56
00:04:05,000 --> 00:04:09,990
now we can execute that stored procedure just the exact same way as any other

57
00:04:10,000 --> 00:04:20,000
stored procedure.