How to: Create CLR stored procedure and Work with CLR database object

In this Article, you will learn how to work with CLR database object to create CLR stored procedure using c# with vs 2019. I have covered couple of simple examples in this article.

Also, I will walk you through the errors that you might encounter while you follow this article. Also, you will learn how to resolve those errors.

Requirements to Create CLR Stored Procedures

• In the common language run time (CLR), stored procedures are implemented as public static methods.
• You can declare return type either as void, or an integer.
  • Returned integer value is treated as the return code from the procedure if the return type is an integer.
    • E.g. EXECUTE @statusReturned = procedureName

Step by step implementation to create CLR stored procedure.

Database Creation

Let’s create one new Database and one table for our examples. You can use your own database and its tables for your test case.

CREATE DATABASE SqlCLR

GO
USE SqlCLR

GO

CREATE TABLE Employers 
(
Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL, 
EmployerName NVARCHAR(250) NOT NULL,
NoOfEmployees INT,
Revenue DECIMAL(7,2)
)

GO

INSERT INTO [dbo].[Employers]([EmployerName],[NoOfEmployees],[Revenue])VALUES
('karthiktechblog',10, 100),
('orange 2',140, 76655),
('apple',10000, 10080),
('android',12990, 17760)

GO

Creating C# Project for CLR stored procedure

Creating a SQL Server CLR project is different in different version of visual studio. In this Article, you will see how to create CLR stored procedure using visual studio 2019.

To get started, open visual studio 2019 IDE and choose File => New => Project as shown in the image below.

When you select “SQL CLR C#” from right side menu, you can choose “SQL CLR C# Stored Procedure” from the left side menu. After you choose the required menu items, provide a stored procedure name in the “Name” filed in the bottom. E.g. PrintUTCDate. Refer the below image for your understanding.

I have created two stored procedures.

1. “PrintUTCDate” method which do not accepts parameter.
2. “AddDaysToCurrentDate” method which accepts one parameter. E.g. (SqlInt64 noOfDaystoAdd). Note that parameter type can be SQL DataType or C# equivalent datatype. E.g. In this case I can use int instead of SqlInt64.

Below are the code for above mentioned stored procedures.

using Microsoft.SqlServer.Server;
using System;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void PrintUTCDate ()
    {
        SqlPipe sqlPipeLine = SqlContext.Pipe;
        sqlPipeLine.Send(DateTime.UtcNow.ToString());
    }   
         
}

using Microsoft.SqlServer.Server;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlTypes;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void AddDaysToCurrentDate(SqlInt64 noOfDaystoAdd)
    {
        List results = new List();

        results.Add(new SqlMetaData("Current Date", SqlDbType.NVarChar, 128));
        results.Add(new SqlMetaData("Date Output", SqlDbType.NVarChar, 128));
        // Create a record object that represents an individual row, including it's metadata.  
        SqlDataRecord record = new SqlDataRecord(results.ToArray());

        record.SetSqlString(0, DateTime.UtcNow.ToString());
        record.SetSqlString(1, DateTime.UtcNow.AddDays(noOfDaystoAdd.Value).ToString());

        // Send the record to the client.  
        SqlContext.Pipe.Send(record);
    }
}

Build and Publish project

Let’s publish our project to our database “SqlCLR”

To build this SQL CLR project, go to menu Build => Build CLR.POC (your project name) or use Shift + F6

You will be presented with a Popup then you need to choose your database connection details to deploy the project. I am using my localDb configuration for this example. E.g."LocalDb)\MSSQLLocalDB".

Feel free to use your database server details

Follow the steps specified in the below two images. Provide the database name that we created for this demo.

When publish runs, there will be script generated by the project. Publish script will look like below.

-- Only for reference, do not run this script as it will not work.
/*

Deployment script for SqlCLR

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "SqlCLR"
:setvar DefaultFilePrefix "SqlCLR"
:setvar DefaultDataPath "C:\Users\Kkannan\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB"
:setvar DefaultLogPath "C:\Users\Kkannan\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB"

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END

GO
USE [$(DatabaseName)];

GO
PRINT N'Altering [CLR.POC]...';

GO
ALTER ASSEMBLY [CLR.POC]
    DROP FILE ALL;

GO
ALTER ASSEMBLY [CLR.POC]
    FROM 01100.....; (ignored data )

GO
ALTER ASSEMBLY [CLR.POC]
    DROP FILE ALL
    ADD FILE FROM 0x4D6963726F736F667420432F432B2B204D534620372E30300D0A1A4450000...... AS N'CLR.POC.pdb'; (ignored data )

GO
PRINT N'Update complete.';

GO

Note: If you get error similar to “Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option” then run the below script to resolve.

use SqlCLR;

sp_configure 'clr enabled', 1
go
RECONFIGURE
go
sp_configure 'clr enabled'
go

This is how our database looks after publish was successful.

Verifying Results

Executing Stored Procedure

First, let’s take our first CLR stored procedure and run. Below is the script to run CLR stored procedure. After you execute the stored procedure, you can check the results that is shown below.

USE [SqlCLR]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[PrintUTCDate]

SELECT	'Return Value' = @return_value

GO

Output

If you notice carefully, the output of the CLR stored procedure is shown in the Message tab as highlighted in the image. In next example, we will see how to display the result data in Result.

Now, let’s execute our second CLR stored procedure. Below is the script to run CLR stored procedure.

USE [SqlCLR]
GO
DECLARE	@return_value int

EXEC	@return_value = [dbo].[AddDaysToCurrentDate]
		@noOfDaystoAdd = 1

SELECT	'Return Value' = @return_value
GO

Output

Bonus example

We can also send a record set using CLR stored procedure. I have created a CLR stored procedure “GetEmployers” that queries and returns all the records from the table in the database.

Use below c# code that creates a stored procedure named GetEmployers. This step is similar to the above mentioned steps.

using Microsoft.SqlServer.Server;
using System.Data.SqlClient;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetEmployers ()
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            SqlCommand command = new SqlCommand("SELECT Id, EmployerName, NoOfEmployees, Revenue FROM SqlCLR.dbo.Employers", connection);
            SqlDataReader reader = command.ExecuteReader();
            SqlContext.Pipe.Send(reader);
        }
    }
}

Now, build and Publish the project. Once you build, your newly created clr stored procedure will be available in your database.

Script to run in SQL to test this clr stored procedure.

USE [SqlCLR]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[GetEmployers]

SELECT	'Return Value' = @return_value

GO

Output

Reference Links: MSDN Docs

Conclusion

In this Article, you learned how to work with CLR database object to create CLR stored procedure using c# with vs 2019.

That’s all from this article. If you have any questions or just want to chat with me, feel free to leave a comment below. If you want to get continuous update about my blog, make sure to follow me on Facebook and Twitter.