Home > C# on .NET > SQL CLR to use C# class for SQL functions, and stored procedures

SQL CLR to use C# class for SQL functions, and stored procedures

 

This article is to introduce quick steps to use SQL CLR which is C# classes, instead of using SQL functions and stored procedures

In my case, this is a starting point of a long journey as an alternative for working with massive number of SP’s (stored procedures) and to move fowward ORM (Object Relational Mapping) using EF (Entity Framework).

Implementing ORM can be a good alternative for stored procedures? It’s different topic to figure out its result for various conditions, the real problem is I hate to keep reading hundreds of SP’s those I faced, to modify something very, very carefully.

Those SP’s, developed by former engineers for a long time with including complex logics, are like reading Rosetta Stone by myself. Even worse things are my lack of understanding of business process, and I’m less familiar with reading SQL script (TSQL).

I may fail to explain a reason why an enterprise data-driven system shall be developed using C#, to apply ORM with EF. However, I also can’t explain why not. The target system also has a packaged S/W for business logic, and data access layer in the middle, which I can’t make any change, or replace at the moment, so the only starting point is SP’s in MS SQL

…No fun can be a serious concern.

 

Creating Visual Studio Project for SQL CLR


1. Create a new project > Visual C#, and select ‘Class Library’

1.1 Set a project name properly to be used in SQL (This wil be an assembly name)

2. Change the name of C# class on your need (note that the class name is used in SSMS)

# This article is going to cover creating a SQL function and SP in a same class

 

C# code in the class


 

 

public class clsDemoSQLCLR
{

    ///////////////////////////////////////////////////////////////////////////////////////////////////////
    // SQL Stored Procedure EXAMPLE
    ///////////////////////////////////////////////////////////////////////////////////////////////////////

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetVersion()     // This is going to be a SP Name
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            SqlCommand command = new SqlCommand("select @@version as Version, getdate() as DateTime", connection);
            SqlDataReader r = command.ExecuteReader();
            SqlContext.Pipe.Send(r);
        }
    }

    ////////////////////////////////////////////////////////////////////////////////////////////////////////
    // SQL FUNCTION EXAMPLE
    ////////////////////////////////////////////////////////////////////////////////////////////////////////

    // Attribute of SQL Function 
    [SqlFunction(DataAccess = DataAccessKind.None, FillRowMethodName = "MyFillRowMethod", IsDeterministic = true)]

    public static IEnumerable Split(string stringToSplit, string delimiters)   
// This is going to be a SQL Function Name 
    {

        string[] elements = stringToSplit.Split(delimiters.ToCharArray());

        return elements;       
    }

    // FillRowMethodName, called internally to return data with type of SqlChars
    public static void MyFillRowMethod(Object theItem, out SqlChars results)
    {
        results = new SqlChars(theItem.ToString());
    }

}

 

Add using statements

 

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

 

Deploying SQL CLR (.NET Assembly) to SQL Server


 

The following commands are to deploy the assembly after bulding the above class library

After deployment, SP, and SQL function can be executed in SSMS.

The overall steps are,
‘Enable SQL CLR’ > ‘Installing .NET assembly’ > ‘Creating Function, and SP’

Prerequisites, to enable SQL CLR in TestDB

 

USE [TestDB] – Assuming it’s TestDB to deploy.

ALTER DATABASE [TestDB] SET TRUSTWORTHY ON WITH ROLLBACK IMMEDIATE
GO
exec sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
exec sp_configure ‘clr enabled’, 1
GO
RECONFIGURE;
GO

 

To deploy the .NET assembly on SQL Server (In my case, it’s DemoSQLCLRFuncAndSP.dll )

 

Create Assembly DemoSQLCLRFuncAndSP from ‘C:\work\DemoSQLCLRFuncAndSP\bin\Debug\DemoSQLCLRFuncAndSP.dll’ with Permission_set = SAFE
GO

In case that the assembly already exists, run the below command first to remove it,

      Drop assembly DemoSQLCLRFuncAndSP

To create SQL function from the assembly,

 

CREATE FUNCTION [dbo].Split(@StringToSplit nvarchar(max), @splitOnChars nvarchar(max) ) returns Table ( Results nvarchar(max) )
EXTERNAL NAME  DemoSQLCLRFuncAndSP.clsDemoSQLCLR.Split
Go

# Defnition Format – EXTERNAL NAME [AssemblyName].[ClassName].[MemberName]

To create the stored procedure from the assembly,

 

CREATE PROCEDURE GetVersion AS EXTERNAL NAME DemoSQLCLRFuncAndSP.clsDemoSQLCLR.GetVersion;
Go

 

To test SP, run ‘EXEC GetVersion’ in SSMS (TestDB)

To test SQL Function, run –
select * from dbo.Split(‘1,2,3,4,5:6:7~8~9′,’,:~’)

image

 

References


CLR Functions in SQL Server – A Tutorial

https://www.skylinetechnologies.com/Insights/Skyline-Blog/March-2013/CLR-Functions-in-SQL-Server-A-Tutorial

Advertisements
Categories: C# on .NET
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: