Post
Ask Question

Create CLR Stored Procedure With And Without Namespace In C# And Install And Uninstall CLR Assembly In MSSQL Server

Introduction

In this article, we will learn how to create a CLR Stored Procedure with & without namespace in C# and install & uninstall CLR assembly in MSSQL Server.

Step 1

Create a library project in Visual Studio as below.

Visual Studio

Create two class files “SayHello.cs” & “SayHi.cs”.

In the file “SayHello.cs”, add the below line of code.

  1. using System;  
  2. using Microsoft.SqlServer.Server;  
  3.   
  4. public class SayHello  
  5. {  
  6.     [SqlProcedure]  
  7.     public static void Voice(out string message)  
  8.     {  
  9.         SqlContext.Pipe.Send("Hello world! is comming from SqlContext.Pipe.Send method." + Environment.NewLine);  
  10.         message = "Hello world! is comming from out parameter.";  
  11.     }  
  12. }  

In file “SayHi.cs”, add this line of code.

  1. using System;  
  2. using Microsoft.SqlServer.Server;  
  3.   
  4. namespace CLRProcedures  
  5. {  
  6.     public class SayHi  
  7.     {  
  8.         [SqlProcedure]  
  9.         public static void Voice(out string message)  
  10.         {  
  11.             SqlContext.Pipe.Send("Hello world! is comming from SqlContext.Pipe.Send method." + Environment.NewLine);  
  12.             message = "Hello world! is comming from out parameter.";  
  13.         }  
  14.     }  
  15. }  

Step 2

Open project property and set output path= C:\dlls as below.

Visual Studio

Step 3

Build or compile the library project and verify dll copied in output folder (C:\dlls).

Step 4

To install CLR procedure in MSSQL Server, open new query window for the targeted database in MSSQL Server Management Studio and add below SQL statement in the query window.

To create CLR procedure you must create an assembly in MSSQL server by running below SQL statement.

  1. CREATE ASSEMBLY say from 'c:\dlls\CLRProcedures.dll' WITH PERMISSION_SET = SAFE   

“SayHello” class is without namespace in the file “SayHello.cs”, To create CLR procedure without namespace run the below SQL statement.

  1. CREATE PROCEDURE SayHelloVoice    
  2. @message nchar(500) OUTPUT    
  3. AS    
  4. EXTERNAL NAME say.SayHello.Voice    

“SayHi” class is with namespace in file “SayHi.cs.” To create CLR procedure with namespace run the below SQL statement.

  1. CREATE PROCEDURE SayHiVoice    
  2. @message nchar(500) OUTPUT    
  3. AS    
  4. EXTERNAL NAME say.[CLRProcedures.SayHi].Voice    

Note: To create CLR procedure with namespace external name must be in the format as below : 

"ASSEMBLY_NAME.[NAMESPACE_OF_THE_CLASS.CLASS_NAME].METHOD".
 
For example :
  1. EXTERNAL NAME say.[CLRProcedures.SayHi].Voice    
 
Now, you can see created & installed CLR procedures in MSSQL Server Management Studio object explorer as below.
Visual Studio

If you execute CLR procedure as below:

  1. DECLARE @msg nchar(500)    
  2. EXEC SayHelloVoice @msg out    
  3. PRINT @msg    
  4.   
  5. DECLARE @msg nchar(500)    
  6. EXEC SayHiVoice @msg out    
  7. PRINT @msg    

You will get an error as below:

Visual Studio

The ability to execute common language runtime (CLR) code is set to OFF by default in SQL Server. The CLR code can be enabled by using the sp_configure system stored procedure.

To enable CLR integration, use the clr enabled option of the sp_configure stored procedure in SQL Server Management Studio,

  1. sp_configure 'show advanced options', 1;    
  2. GO    
  3. RECONFIGURE;    
  4. GO    
  5. sp_configure 'clr enabled', 1;    
  6. GO    
  7. RECONFIGURE;    
  8. GO    

To disable CLR integration, use the clr enabled option of the sp_configure stored procedure in SQL Server Management Studio,

  1. sp_configure 'show advanced options', 1;    
  2. GO    
  3. RECONFIGURE;    
  4. GO    
  5. sp_configure 'clr enabled', 0;    
  6. GO    
  7. RECONFIGURE;    
  8. GO    

Now, you can run CLR procedure successfully.

Visual Studio

Visual Studio

Step 5

To uninstall CLR procedure in MSSQL Server, open new query window for the targeted database in MSSQL Server Management Studio and add the below SQL statement in the query window.

  1. IF EXISTS (SELECT name FROM sysobjects WHERE name = 'SayHelloVoice')    
  2.    drop procedure SayHelloVoice    
  3.   
  4. IF EXISTS (SELECT name FROM sysobjects WHERE name = 'SayHiVoice')    
  5.    drop procedure SayHiVoice    
  6.   
  7. IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'say')    
  8.    drop assembly say    

Now, the clr procedures “SayHelloVoice” & “SayHiVoice” and assembly, “say’ have been removed from MSSQL Server Management Studio object explorer as below.

Visual Studio

 

To install CLR procedure again you can repeat steps 4 & 5.

Congratulation you have successfully created, installed and uninstalled CLR stored procedures. If you have any query or concern just do let me know or just put it in the comment box and I will respond as soon as possible. I am open to discussing anything even "silly" questions as well. If you have any suggestion related to this article, please let me know and I promise I will improve this article to the highest level. 

References

  • https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/clr-integration-enabling?view=sql-server-2017
  • https://docs.microsoft.com/ru-ru/previous-versions/sql/sql-server-2005/ms131052(v=sql.90)

Summary

In this article, we have learned how to create CLR Stored Procedure with & without namespace in C# & install & uninstall CLR assembly in MSSQL Server.


Similar Articles