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.

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

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

using System;
using Microsoft.SqlServer.Server;
public class SayHello
{
[SqlProcedure]
public static void Voice(out string message)
{
SqlContext.Pipe.Send("Hello world! is comming from SqlContext.Pipe.Send method." + Environment.NewLine);
message = "Hello world! is comming from out parameter.";
}
}

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

using System;
using Microsoft.SqlServer.Server;
namespace CLRProcedures
{
public class SayHi
{
[SqlProcedure]
public static void Voice(out string message)
{
SqlContext.Pipe.Send("Hello world! is comming from SqlContext.Pipe.Send method." + Environment.NewLine);
message = "Hello world! is comming from out parameter.";
}
}
}

Step 2

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

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.

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.

CREATE PROCEDURE SayHelloVoice
@message nchar(500) OUTPUT
AS
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.

CREATE PROCEDURE SayHiVoice
@message nchar(500) OUTPUT
AS
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 :

EXTERNAL NAME say.[CLRProcedures.SayHi].Voice

Now, you can see created & installed CLR procedures in MSSQL Server Management Studio object explorer as below.

If you execute CLR procedure as below:

DECLARE @msg nchar(500)
EXEC SayHelloVoice @msg out
PRINT @msg
DECLARE @msg nchar(500)
EXEC SayHiVoice @msg out
PRINT @msg

You will get an error as below:

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,

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

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

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 0;
GO
RECONFIGURE;
GO

Now, you can run CLR procedure successfully.

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.

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'SayHelloVoice')
drop procedure SayHelloVoice
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'SayHiVoice')
drop procedure SayHiVoice
IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'say')
drop assembly say

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

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.

5 Comments

Join the conversation! Your thoughts help the community grow.

Roger Da7y ago
Thanks for your article. I did notice that a "Reference" is missing as my project doesn't understand the [SQLStoredProcedure] lines in your examples. Please include a picture or statement about what references are needed in the project in your article.
Patricio V.7y ago
Thank you, very useful
Dharmendra Kumar Pandit7y ago
Thank you sir.
Hadshana Kamalanathan8y ago
Thank you for sharing
- Viknaraj Manogararajah8y ago
  Nice explanation, thank you for sharing.