How To Create Four Types Of CLR Database Procedures In Visual Studio In C#

Introduction

In this article, we will learn about CLR database procedures in C#. Let’s discuss CLR database stored procedure.

Description

Why we use CLR procedures instead of T-SQL procedures & functions.

If you'd prefer to write the code in (VB.Net, C#, etc.) than SQL.
If you want to do complex operations on data in CLR.
If you want to be close to the data (i.e. not in ASP.NET or a WinForms app etc.)
The CLR has RegEx but SQL does not.
You can call web services in CLR, but T-SQL does not.
The CLR has better performance (if you have to do a lot of math on every row.)
You can re-use code.

CLR Stored Procedures

This article will show how to create four types of CLR database stored procedures.

A stored procedure that returns a result set
A stored procedure that returns an output parameter, message, and returns code
A stored procedure that returns a dynamically created result set with a single row
A stored procedure that returns a dynamically created result set containing multiple rows.

Let’s see CLR stored procedures practically.

Step 1

Create a library project (CLRProcedures) in Visual Studio as below.

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

using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.SqlClient;
namespace CLRProcedures
{
public class CLRDynamicProcedures
{
[SqlProcedure]
public static void GetUsers()
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
SqlCommand cmd = new SqlCommand(@"SELECT * FROM [dbo].[Users]", connection);
SqlContext.Pipe.ExecuteAndSend(cmd);
}
}
[SqlProcedure]
public static int SpOutputParameter_Message_ReturnCode(string emailAddress, out int outVal)
{
outVal = 10;
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
SqlCommand cmd = new SqlCommand(@"SELECT COUNT(*) FROM [dbo].[Users] WHERE EmailAddress=@EmailAddress", connection);
SqlParameter modifiedSinceParam = cmd.Parameters.Add("@EmailAddress", SqlDbType.NVarChar);
modifiedSinceParam.Value = emailAddress;
int userCount = (int)cmd.ExecuteScalar();
SqlContext.Pipe.Send(string.Format("Total number of users having email address {0} is {1}", emailAddress, userCount));
}
return 5;
}
[SqlProcedure]
public static void GetUserByUserID(int userID)
{
SqlMetaData[] md = new SqlMetaData[2];
md[0] = new SqlMetaData("UserID", SqlDbType.Int);
md[1] = new SqlMetaData("EmailAddress", SqlDbType.NVarChar, 150);
SqlDataRecord row = new SqlDataRecord(md);
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
SqlCommand cmd = new SqlCommand(@"SELECT TOP 1 UserID,EmailAddress FROM [dbo].[Users] WHERE UserID=@UserID", connection);
SqlParameter modifiedSinceParam = cmd.Parameters.Add("@UserID", SqlDbType.NVarChar);
modifiedSinceParam.Value = userID;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
row.SetSqlInt32(0, (int)reader.GetSqlInt32(0));
row.SetSqlString(1, (string)reader.GetSqlString(1));
}
}
}
SqlContext.Pipe.Send(row);
}
[SqlProcedure]
public static void GetUsersByEmail(string emailAddress)
{
SqlMetaData[] md = new SqlMetaData[2];
md[0] = new SqlMetaData("UserID", SqlDbType.Int);
md[1] = new SqlMetaData("EmailAddress", SqlDbType.NVarChar, 150);
SqlDataRecord row = new SqlDataRecord(md);
SqlContext.Pipe.SendResultsStart(row);
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
SqlCommand cmd = new SqlCommand(@"SELECT UserID,EmailAddress FROM [dbo].[Users] WHERE EmailAddress=@EmailAddress", connection);
SqlParameter modifiedSinceParam = cmd.Parameters.Add("@EmailAddress", SqlDbType.NVarChar);
modifiedSinceParam.Value = emailAddress;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
row.SetSqlInt32(0, (int)reader.GetSqlInt32(0));
row.SetSqlString(1, (string)reader.GetSqlString(1));
SqlContext.Pipe.SendResultsRow(row);
}
}
}
SqlContext.Pipe.SendResultsEnd();
}
}
}

Step 2

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

Step 3

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

Step 4

Open new query window for the targeted database in MSSQL Server Management Studio and add the below SQL statement in the query window to create a “Users” table with some data.

CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](200) NOT NULL,
[RealName] [nvarchar](200) NOT NULL,
[EmailAddress] [varchar](150) NULL,
[ModifiedDate] [date] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Users] ON
INSERT [dbo].[Users] ([UserID], [UserName], [RealName], [EmailAddress], [ModifiedDate]) VALUES (44, N'dinesh.singh', N'Dinesh', N'[email protected]', CAST(N'2018-05-15' AS Date))
INSERT [dbo].[Users] ([UserID], [UserName], [RealName], [EmailAddress], [ModifiedDate]) VALUES (45, N'ramesh.singh', N'Ramesh', N'[email protected]', CAST(N'2018-05-15' AS Date))
SET IDENTITY_INSERT [dbo].[Users] OFF

Step 5

Let’s create a CLR database stored procedure that returns a result set.

Open SQL Management Studio query window and write & execute the below SQL statement to create the stored procedure.

CREATE PROCEDURE CLRGetUsers
AS EXTERNAL NAME StoredProcedures.[CLRProcedures.CLRDynamicProcedures].GetUsers

Note
Run the "create assembly" statement first and after that, run the "create procedure" statement. Don’t run both at the same time, otherwise, you will get an error as below.

Let’s see whether CLR database assembly and procedure are created in SQL Database Object Explorer or not.

From the above screen, CLR assembly and procedure have created in SQL database. Now, let’s run the created CLR procedure “CLRGetUsers” and see the output.

Now, you can see we are getting results from the SQL database through CLR stored procedure “CLRGetUsers”.

Step 5

Let’s create a CLR database stored procedure that returns an output parameter, message, and return code.

Open SQL Management Studio query window and write and execute the below SQL statement to create the stored procedure.

CREATE PROCEDURE CLRSpOutputParameter_Message_ReturnCode @emailAddress nvarchar(50), @outVal int OUT
AS EXTERNAL NAME StoredProcedures.[CLRProcedures.CLRDynamicProcedures].
SpOutputParameter_Message_ReturnCode

Let’s see whether CLR database procedure is created in SQL database object explorer or not.

Now, it is clear that procedure has been created. Now, let’s execute this procedure.

Now, you can see we are getting results from SQL database through CLR stored procedure “CLRSpOutputParameter_Message_ReturnCode”.

Step 6

Let’s create a CLR database stored procedure that returns a dynamically created result set with a single row.

Open SQL Management Studio query window and write & execute the below SQL statement to create the stored procedure.

CREATE PROCEDURE CLRGetUserByUserID @userID int
AS EXTERNAL NAME StoredProcedures.[CLRProcedures.CLRDynamicProcedures].GetUserByUserID

Let’s see whether CLR database procedure has been created in SQL database object explorer or not.

Now, it is clear that the procedure has been created. Now, let’s execute this procedure.

Now, you can see we are getting results from SQL database through CLR stored procedure “CLRGetUserByUserID”.

Step 7

Let’s create a CLR database stored procedure that returns a dynamically created result set containing multiple rows.

Open SQL Server Management Studio query window and write & execute the below SQL statement to create the stored procedure.

CREATE PROCEDURE CLRGetUsersByEmail @emailAddress nvarchar(150)
AS EXTERNAL NAME StoredProcedures.[CLRProcedures.CLRDynamicProcedures].GetUsersByEmail

Let’s see whether CLR database procedure has been created in SQL database object explorer or not.

Now, it is clear that the procedure has been created. Now, let’s execute this procedure.

Now, you can see we are getting results from SQL database through CLR stored procedure “CLRGetUsersByEmail”.

Points of interest

If you are getting an error as below while executing the CLR database stored procedure.

Then, you must run the below SQL statement to enable CLR.

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

Now, you can run CLR procedure successfully.

To disable CLR integration, use the clr enabled option of the sp_configure stored procedure.

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

To un-install CLR database procedure you must write & execute the below SQL statement in the MSSQL Server Management Studio query window.

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'CLRGetUsers')
DROP PROCEDURE CLRGetUsers
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'CLRSpOutputParameter_Message_ReturnCode')
DROP PROCEDURE CLRSpOutputParameter_Message_ReturnCode
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'CLRGetUserByUserID')
DROP PROCEDURE CLRGetUserByUserID
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'CLRGetUsersByEmail')
DROP PROCEDURE CLRGetUsersByEmail

To un-install CLR database assembly you must write & execute below SQL statement in the MSSQL Server Management Studio query window.

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'StoredProcedures')
DROP ASSEMBLY StoredProcedures

Congratulations, you have successfully created, installed, and uninstalled CLR database stored procedures. If you have any query or concern, just do let me know or just comment in the comment box. I will respond as soon as possible. If you have any suggestion related to this article, please let me know. I promise I will improve this article to the maximum level.

Summary

In this article, we have learned how to create, install, and uninstall CLR database stored procedure and assembly in C#.

8 Comments

Join the conversation! Your thoughts help the community grow.

Viknaraj Manogararajah8y ago
Nice Article, Thank you for sharing
Gopi Kishan8y ago
Hi Dinesh, the article is good. For your information, SQL CLR is not ideal for applications where security is a must.
- Dinesh Kushwaha8y ago
  If you want to understand SAFE SQLCLR you can refer link : http://www.sqlservercentral.com/articles/Stairway+Series/109905/
- Dinesh Kushwaha8y ago
  First of all sorry for late response and thanks for your interest Gopi ,
- Dinesh Kushwaha8y ago
  The bottom line: SQLCLR code can’t do anything more in a database than an equivalent T-SQL code module running under the same security context. This fact alone should take the edge off any DBAs nervousness!Microsoft had three primary design goals when designing how to host the CLR:• The CLR and the code running within it cannot compromise the security and stability of SQL Server. • SQLCLR code must follow SQL Server authentication and authorization rules, which in part means that it runs under the security context of the user session. • Admins must be able to control access to operating system resources. This means that there must be a secure way to access machine resources from within the SQL Server process.