Sql Server: Just Started Working With CLR Using C#

DiponRoy

Rate me:

4.55/5 (8 votes)

2 Jul 2019CPOL3 min read

13.9K

148

Creating CLR in SQL Server

Download source code - 13.1 KB

Introduction

A few days ago, I was working with String Join and Split Functionality in SQL Server. From that day on, I was thinking of creating custom aggregate functions. Today, we are going to do it by using the CLR feature of SQL Server.

Work Flow

What are we going to do?

Enable CLR in SQL Server
Create a CLR Assembly
Load/Reload/Remove CLR assembly to SQL Server
Create/Alter/Drop a SQL Server Aggregate/Function/Sp and reload CLR assembly

CLR Configuration

Let us check if CLR is enabled in our SQL Server or not. If not, we are going to enable it.

Enable/Disable

SQL

sp_configure 'show advanced options', 1;    /*1:enable, 0:disable*/
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;              /*1:enable, 0:disable*/
GO  
RECONFIGURE;                                /*force: RECONFIGURE WITH OVERRIDE*/
GO

To disable the feature, we have to use 0 rather than 1.

Status Check

SQL

/*value 1:enabled, 0:disabled*/
SELECT * FROM sys.configurations;
SELECT * FROM sys.configurations WHERE name = 'clr enabled';

/*config_value 1:enabled, 0:disabled*/ 
EXEC sp_configure; 
EXEC sp_configure 'clr enabled';

Build CLR Assembly

We have to create/add an empty library project under any solution and build that project in release mode. We are going to use the generated DLL from the bin folder, which will be loaded as CLR assembly to any SQL Server. If any change is made to CLR codes, that DLL need to be reloaded to SQL Server.

Load CLR Assembly or DLL

Loading/reloading/removing is as simple as given below.

Create

Give a name (ClrCommon) to the assembly or DLL.
Load the DLL from a location.

SQL

/*Create or Load ASSEMBLY or DLL*/
GO
CREATE ASSEMBLY ClrCommon
FROM 'D:\Study\Sql Server\CLR\ClrExtensions\Clr.Common\bin\Release\Clr.Common.dll' /*your clr 
                                                                                 ddl location*/
WITH PERMISSION_SET=SAFE;  
GO

Update

Alter existing assembly (ClrCommon).
Reload the DLL from a location.

SQL

/*Update or Reload ASSEMBLY or DLL*/
GO
ALTER  ASSEMBLY ClrCommon
FROM 'D:\Study\Sql Server\CLR\ClrExtensions\Clr.Common\bin\Release\Clr.Common.dll' /*your clr 
                                                                                ddl location*/ 
WITH PERMISSION_SET=SAFE;  
GO

Remove

SQL

/*Remove*/
GO
DROP ASSEMBLY ClrCommon;
GO

Aggregate Function

Tmpl

Template class which can be used to create an aggregate function.

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

/*https://docs.microsoft.com/en-us/sql/relational-databases/
clr-integration-database-objects-user-defined-functions/
clr-user-defined-aggregates-requirements?view=sql-server-2017*/
[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined,
    IsInvariantToDuplicates = false,
    IsInvariantToNulls = true,
    IsInvariantToOrder = true,
    IsNullIfEmpty = true,
    MaxByteSize = 8000,
    Name = "The name of the aggregate")]
public class AggregateFunctionTmpl : IBinarySerialize
{
    /// <summary>  
    /// Initialize the internal data structures  
    /// </summary>  
    public void Init()
    {
    }

    /// <summary>  
    /// Accumulate the next value, not if the value is null  
    /// </summary>  
    /// <param name="value">value to be aggregated</param>  
    public void Accumulate(SqlString value)
    {
    }

    /// <summary>  
    /// Merge the partially computed aggregate with this aggregate  
    /// </summary>  
    /// <param name="group">The other partial results to be merged</param>  
    public void Merge(AggregateFunctionTmpl group)
    {
    }

    /// <summary>  
    /// Called at the end of aggregation, to return the results of the aggregation 
    /// </summary>  
    public SqlString Terminate()
    {
        return new SqlString("Result");
    }

    /*IBinarySerialize: How read, write should actually work 
      https://stackoverflow.com/questions/27781904/
      what-are-ibinaryserialize-interface-methods-used-for */
    public void Read(BinaryReader r)
    {
        if (r == null)
        {
            throw new ArgumentNullException("r");
        }
    }

    public void Write(BinaryWriter w)
    {
        if (w == null)
        {
            throw new ArgumentNullException("w");
        }
    }
}

Example: String Join

Using that template, we are creating a class to manage the string join operation. This class will be called by an aggregate function which will combine column values using a specified separator in SQL Server.

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

/*Aggregate function*/
[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined,               /*impt because we are using Queue<StringJoinModel> 
                                       _values, if StringBuilder been used, 
                                       we could use Format.Native*/
    IsInvariantToDuplicates = false,
    IsInvariantToNulls = true,
    IsInvariantToOrder = true,
    IsNullIfEmpty = true,
    MaxByteSize = 8000,               /*impt: because we used 
                                       Format.UserDefined, 8000 is max*/
    Name = "STRING_JOIN_AGG")]
public class StringJoinAgg : IBinarySerialize   /*impt: IBinarySerialize because 
                                                 we used Format.UserDefined*/
{
    public const string DefaultSeparator = ",";

    class StringJoinModel
    {
        public string Value { get; set; }
        public string Separator { get; set; }
    }

    private Queue<StringJoinModel> _values;

    /// <summary>  
    /// Initialize the internal data structures  
    /// </summary>  
    public void Init()
    {
        _values = new Queue<StringJoinModel>();
    }

    /// <summary>  
    /// Accumulate the next value, not if the value is null  
    /// </summary>  
    /// <param name="value">value to be aggregated</param>  
    /// <param name="separator">separator to be used for concatenation</param>  
    public void Accumulate(SqlString value, SqlString separator)
    {
        if (value.IsNull || String.IsNullOrEmpty(value.Value))
        {
            /*not include null or empty value */
            return;
        }
        string valueString = value.Value;
        string separatorString = separator.IsNull || 
           String.IsNullOrEmpty(separator.Value) ? DefaultSeparator : separator.Value;
        _values.Enqueue(new StringJoinModel 
               { Value = valueString, Separator = separatorString });
    }

    /// <summary>  
    /// Merge the partially computed aggregate with this aggregate  
    /// </summary>  
    /// <param name="group">The other partial results to be merged</param>  
    public void Merge(StringJoinAgg group)
    {
        while (group._values.Count != 0)
        {
            _values.Enqueue(group._values.Dequeue());
        }
    }

    /// <summary>  
    /// Called at the end of aggregation, to return the results of the aggregation.  
    /// </summary>  
    /// <returns>Concatenates the elements, 
    /// using the specified separator between each element or member.</returns>  
    public SqlString Terminate()
    {
        StringBuilder builder = new StringBuilder();

        StringJoinModel model;
        if (_values.Count != 0)
        {
            /*first time no separator*/
            model = _values.Dequeue();
            builder.Append(model.Value);
        }
        while (_values.Count != 0)
        {
            model = _values.Dequeue();
            builder.Append(model.Separator).Append(model.Value);
        }

        string value = builder.ToString(); return new SqlString(value);
    }

    /*IBinarySerialize: How read, write should actually work
     * https://stackoverflow.com/questions/27781904/
     * what-are-ibinaryserialize-interface-methods-used-for
     */
    public void Read(BinaryReader r)
    {
        if (r == null)
        {
            throw new ArgumentNullException("r");
        }

        /*
         * Read as write worked
         * --------------------
         * total
         * value1
         * separator1
         * value2
         * separator3  
         * 
         * 
         * valueN
         * separatorN
         */
        _values = new Queue<StringJoinModel>();
        var count = r.ReadInt32();
        for (int i = 0; i < count; i++)
        {
            var model = new StringJoinModel
            {
                Value = r.ReadString(),
                Separator = r.ReadString()
            };
            _values.Enqueue(model);
        }
    }

    public void Write(BinaryWriter w)
    {
        if (w == null)
        {
            throw new ArgumentNullException("w");
        }

        /*
         * Write sample
         * ------------
         * total
         * value1
         * separator1
         * value2
         * separator3  
         * 
         * 
         * valueN
         * separatorN
         */
        w.Write(_values.Count);
        while(_values.Count != 0)
        {
            StringJoinModel m = _values.Dequeue();
            w.Write(m.Value);
            w.Write(m.Separator);
        }
    }
}

Load/Reload LLD

Please check Build CLR Assembly and Load CLR Assembly Or DLL section to build/rebuild and load/reload DLL from bin to SQL Server.

Create Function in SQL Server

Creating an aggregate function in SQL Server using the CLR class:

SQL

/*create*/
GO
CREATE AGGREGATE STRING_JOIN_AGG(@value NVARCHAR(MAX), @separator NVARCHAR(MAX))  
RETURNS NVARCHAR(MAX)  
EXTERNAL NAME [ClrCommon].[StringJoinAgg];        /*ASSEMBLY(name that used).ClassName*/
GO  

/*update: drop and re-create*/

/*remove*/
GO
DROP AGGREGATE STRING_JOIN_AGG;
GO

Usage

Checking the created aggregate function in action:

SQL

DECLARE @tblUserGroup TABLE (GroupId INT, UserId INT NULL);
INSERT INTO @tblUserGroup VALUES
(1, 1), (1, 2), (1, 3),
(2, 4), (2, 5), (2, 6),
(3, Null),                            /*NULL value*/
(4, 1), (4, 1), (4, 2), (4, 2);        /*DISTINCT*/
SELECT * FROM @tblUserGroup;

SELECT 
    GroupId, 
    COUNT(DISTINCT UserId) AS TotalUser,
    dbo.STRING_JOIN_AGG(DISTINCT CONVERT(NVARCHAR(MAX), UserId), ', ') AS UserIds
FROM @tblUserGroup AS g
GROUP BY GroupId;

Working as expected, right!

Limitations

OVER(PARTITION BY GroupId) works, but OVER(ORDER BY UserId DESC) is not supported.

Why?

But we can do:

SQL

/*order by*/
SELECT 
    GroupId, 
    COUNT(UserId) AS TotalUser,
    dbo.STRING_JOIN_AGG(CONVERT(NVARCHAR(MAX), UserId), ', ') AS UserIds
FROM (
    /*do ordering or condition check here*/
    SELECT TOP 99.99999 PERCENT *
    FROM @tblUserGroup
    ORDER BY GroupId DESC, UserId DESC
) AS g
GROUP BY GroupId;

/*
TOP 100 PERCENT *:                        Not working: 
TOP 99.99999 PERCENT *:                    working, not sure for large data set
TOP 2147483647:                            2005
ORDER BY refKlinik_id OFFSET 0 ROWS:    2012 >=
*/

Table-Valued Function

Tmpl

Template class which can be used to create a table-valued function.

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

/*Table-Valued Function: returns default TABLE*/
public class TableValuedFunctionTmpl
{
    [SqlFunction(
        FillRowMethodName = "FillRow",    /*IEnumerable/Result to table creator*/
        TableDefinition = "Value NVARCHAR(MAX) NULL, Position BIGINT")] /*expected 
                                                                          output table*/
    public static IEnumerable ExecutorMethod(SqlString value)
    {
        ArrayList list = new ArrayList();
        return list;                                                     /*result*/
    }

    public static void FillRow(Object obj, out SqlString value, out SqlInt64 position)
    {
        /*obj: a row from result rows*/
        var rowItem = obj;
        
        /*create table row from the obj*/
        value = new SqlString("");  
        position = new SqlInt64(1);
    }
}

Example: String Split

Creating a class using that template to manage string split operation. This class will be called by a table-valued function which will split a string using a specified separator in SQL Server.

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

/*Table-Valued Function: returns default TABLE*/
public class StringSplitFun
{
    public const string DefaultSeparator = ",";

    private class StringSplitModel
    {
        public string Value { get; }
        public long Position { get;  }
        public StringSplitModel(string value, long position)
        {
            Value = value;
            Position = position;
        }
    }

    //The SqlFunction attribute tells Visual Studio to register this 
    //code as a user defined function
    [SqlFunction(
        FillRowMethodName = "SplitFillRow",   /*output row build*/
        TableDefinition = "Value NVARCHAR(MAX) NULL, Position BIGINT")]   /*output row 
                                                               column names and types*/
    public static IEnumerable Split(SqlString values, SqlString separator)
    {
        List<StringSplitModel> list = new List<StringSplitModel>();
        if (values.IsNull || String.IsNullOrEmpty(values.Value))
        {
            return list;
        }

        string separatorString = separator.IsNull || 
          String.IsNullOrEmpty(separator.Value) ? DefaultSeparator : separator.Value;
        String[] valueList = values.Value.Split
                 (new String[] { separatorString }, StringSplitOptions.None);
        for (long i = 0; i < valueList.Length; i++)
        {
            string value = valueList[i].Trim().ToUpper().Equals("NULL") ? 
                           null : valueList[i];  /*null string value as db NULL*/
            list.Add(new StringSplitModel(value, i));
        }
        return list;
    }

    public static void SplitFillRow(Object obj, out SqlString value, out SqlInt64 position)
    {
        StringSplitModel entity = (StringSplitModel)obj;
        value = new SqlString(entity.Value);
        position = new SqlInt64(entity.Position);
    }
}

Load/Reload LLD

Please check Build CLR Assembly and Load CLR Assembly Or DLL section to build/rebuild and load/reload DLL from bin to SQL Server.

Create Function in SQL Server

SQL

/*create*/
GO  
CREATE FUNCTION STRING_SPLIT_FUN(@values NVARCHAR(MAX), @separator NVARCHAR(MAX))  
RETURNS TABLE
(
    [Value] NVARCHAR(MAX) NULL,
    Position BIGINT
)  
AS   
EXTERNAL NAME ClrCommon.StringSplitFun.Split;  /*loadedAssemblyName.ClassName.MethodName*/
GO 

/*update: drop and re-create*/

/*remove*/
GO  
DROP FUNCTION STRING_SPLIT_FUN
GO

Usage

SQL

DECLARE @valueList NVARCHAR(MAX) = 'Dan, Han, Mos, Ben ,,  ,null, Null, NULL,Tom';
DECLARE @separator NVARCHAR(MAX) = ',';
--SELECT @valueList AS ListString;

SELECT 
    *, 
    LTRIM(RTRIM(Value)) AS TrimedValue
FROM STRING_SPLIT_FUN(@valueList, @separator)
WHERE Value IS NOT NULL
AND LTRIM(RTRIM(Value)) <> ''    /*avoid null or empty values*/

Scalar-Valued Function

Tmpl

Template class which can be used to create a scalar-valued function.

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

/*Scalar-Valued Function*/
public class ScalarValuedFunctionTmpl
{
    [SqlFunction]
    public static SqlString ExecutorMethod(SqlInt64 value)
    {
        return new SqlString("Result");
    }
}

Example: Number Odd or Even

Using that template, creating a class which will be called by a scalar-valued function. It will return information based on logic and inputs in SQL Server. We can also add more input parameters as needed.

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

/*Scalar-Valued Function*/
public class NumberFun
{
    [SqlFunction]
    public static SqlString OddOrEven(SqlInt64 number)
    {
        if (number.IsNull)
        {
            return new SqlString(null);
        }
        string value = number.Value%2 == 0 ? "Even" : "Odd";
        return new SqlString(value);
    }
}

Load/Reload LLD

Please check Build CLR Assembly and Load CLR Assembly Or DLL section to build/rebuild and load/reload DLL from bin to SQL Server.

Create Function In SQL Server

SQL

/*create*/
GO
CREATE FUNCTION OddOrEven(@number BIGINT NULL) RETURNS NVARCHAR(MAX)   
AS EXTERNAL NAME ClrCommon.NumberFun.OddOrEven;   
GO

/*update: drop and re-create*/

/*remove*/
GO  
DROP FUNCTION OddOrEven
GO

Usage

SQL

DECLARE @tblNumber TABLE(Value BIGINT NULL);
INSERT INTO @tblNumber(Value) VALUES (1), (NULL), (2);
--SELECT * FROM @tblNumber;

SELECT 
    Value,
    dbo.OddOrEven(Value) AS Details 
FROM @tblNumber;

References

Function

Table-Valued Function

Returns default table: Introduction to SQL Server CLR table valued functions

Scalar-Valued Function

CLR Scalar-Valued Functions
Data type mapping: Mapping CLR Parameter Data

YouTube

Limitations

This is a get started or learning purpose post. The code may throw unexpected errors for untested inputs. If any, just let me know.

Please find Visual Studio 2017 solution as an attachment, including all template and SQL files. If there is any build error, let me know.

History

2^nd July, 2019: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

DiponRoy

Bangladesh

This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Sql Server: Just Started Working With CLR Using C#

Introduction

Work Flow

CLR Configuration

Enable/Disable

Status Check

Build CLR Assembly

Load CLR Assembly or DLL

Create

Update

Remove

Aggregate Function

Tmpl

Example: String Join

Load/Reload LLD

Create Function in SQL Server

Usage

Limitations

Table-Valued Function

Tmpl

Example: String Split

Load/Reload LLD

Create Function in SQL Server

Usage

Scalar-Valued Function

Tmpl

Example: Number Odd or Even

Load/Reload LLD

Create Function In SQL Server

Usage

References

CLR

Load ASSEMBLY or DLL

Aggregate

Function

Table-Valued Function

Scalar-Valued Function

YouTube

Limitations

History

License

Comments and Discussions