Sql Server: Just Started Working With CLR Using C#

• 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

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

/*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

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

/*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

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

/*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

/*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:

/*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:

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?

• Is it possible to use user defined aggregates (clr) with window functions (over)?
• Can I create a Clr ud Aggregate Function with over/ order by clause?

But we can do:

/*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

/*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

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

/*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

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

CLR

• Core: Introduction to SQL Server CLR Integration
• Enable: CLR Integration - Enabling

Load ASSEMBLY or DLL

• How to update a CLR Assembly without dropping assembly from SQL Server

Aggregate

• CREATE AGGREGATE (Transact-SQL)
• aggregate-templ: CLR User-Defined Aggregate - Invoking Functions
• Format Type: Format Enum

Function

• Create CLR Functions
• CLR User-Defined Functions

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

• Getting Started with the SQL Server CLR
• CLR extensions for SQL Server. Part 1. Table-valued User Defined Functions
• Module 07-06 Using CLR Stored Procedures

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