65.9K
CodeProject 正在变化。 阅读更多。
Home

SQL Server：刚开始使用 C# 的 CLR

<b>Dipon<font color="orange">Roy</font></b>
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.55/5 (8投票s)

2019年7月2日

CPOL

3分钟阅读

viewsIcon

15476

downloadIcon

177

在 SQL Server 中创建 CLR

引言

几天前，我在 SQL Server 中处理字符串的连接和分割功能。从那时起，我就一直在思考创建自定义聚合函数。今天，我们将使用 SQL Server 的 CLR 功能来实现这一点。

工作流程

我们将要做什么？

  • 在 SQL Server 中启用 CLR
  • 创建 CLR 程序集
  • 将 CLR 程序集加载/重新加载/删除到 SQL Server
  • 创建/修改/删除 SQL Server 聚合函数/函数/存储过程并重新加载 CLR 程序集

CLR 配置

让我们检查一下我们的 SQL Server 是否已启用 CLR。如果未启用，我们将启用它。

启用/禁用

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

要禁用该功能，我们必须使用 0 而不是 1。

状态检查

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

构建 CLR 程序集

我们必须在任何解决方案下创建一个/添加一个空的库项目，并在发布模式下构建该项目。我们将使用从 *bin* 文件夹生成的 DLL，该 DLL 将作为 CLR 程序集加载到任何 SQL Server 中。如果对 CLR 代码进行了任何更改，则需要将该 DLL 重新加载到 SQL Server。

加载 CLR 程序集或 DLL

加载/重新加载/删除的操作非常简单，如下所示。

Create

  1. 为程序集或 DLL 指定一个名称（ClrCommon）。
  2. 从某个位置加载 DLL。
/*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

更新

  1. 修改现有程序集（ClrCommon）。
  2. 从某个位置重新加载 DLL。
/*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*/
GO
DROP ASSEMBLY ClrCommon;
GO

聚合函数

模板

用于创建聚合函数的模板类。

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");
        }
    }
}

示例：字符串连接

使用该模板，我们正在创建一个类来管理 string join 操作。该类将由一个聚合函数调用，该函数将在 SQL Server 中使用指定的SEPARATOR合并列值。

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);
        }
    }
}

加载/重新加载 LLD

请查看 **构建 CLR 程序集** 和 **加载 CLR 程序集或 DLL** 部分，以从 *bin* 文件夹构建/重建并加载/重新加载 DLL 到 SQL Server。

在 SQL Server 中创建函数

使用 CLR 类在 SQL Server 中创建聚合函数

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

用法

检查已创建的聚合函数是否正常工作

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;

如预期那样工作，对吧！

限制

OVER(PARTITION BY GroupId) 可以正常工作，但 OVER(ORDER BY UserId DESC) 不受支持。

为什么？

但是我们可以做到

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

表值函数

模板

用于创建表值函数的模板类。

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);
    }
}

示例：字符串分割

使用该模板创建一个类来管理字符串分割操作。该类将由一个表值函数调用，该函数将在 SQL Server 中使用指定的 SEPARATOR 分割字符串。

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);
    }
}

加载/重新加载 LLD

请查看 **构建 CLR 程序集** 和 **加载 CLR 程序集或 DLL** 部分，以从 *bin* 文件夹构建/重建并加载/重新加载 DLL 到 SQL Server。

在 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

用法

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

标量值函数

模板

用于创建标量值函数的模板类。

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");
    }
}

示例：判断奇偶数

使用该模板，创建一个类，该类将由一个标量值函数调用。它将在 SQL Server 中根据逻辑和输入返回信息。我们还可以根据需要添加更多输入参数。

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);
    }
}

加载/重新加载 LLD

请查看 **构建 CLR 程序集** 和 **加载 CLR 程序集或 DLL** 部分，以从 *bin* 文件夹构建/重建并加载/重新加载 DLL 到 SQL Server。

在 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

用法

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;

参考文献

CLR
加载程序集或 DLL
Aggregate
函数
表值函数
标量值函数
YouTube

限制

这是一篇入门或学习用途的文章。代码可能会因未经测试的输入而引发意外错误。如果有任何问题，请告诉我。

请找到附带的 Visual Studio 2017 解决方案，其中包含所有模板和 SQL 文件。如果存在任何构建错误，请告知我。

历史

  • 2019年7月2日：初始版本
© . All rights reserved.