SQL Server CLR 使用 C# 自定义函数

--开启所有服务器配置
sp_configure 'show advanced options', 1;
RECONFIGURE WITH override
GO
--开启 CLR
sp_configure 'clr enabled', 1;
RECONFIGURE WITH override
GO

--关闭所有服务器配置
sp_configure 'show advanced options', 0;
RECONFIGURE WITH override
GO
--关闭 CLR
sp_configure 'clr enabled', 0;
RECONFIGURE WITH override
GO

--权限不够时，设置目标数据库为可信赖的，例如：Test
ALTER DATABASE [Test] SET TRUSTWORTHY ON --修改数据库所有者为当前登录的用户，也可以为其他用户，例如：sa
EXEC sp_changedbowner 'sa'

public partial class UserDefinedFunctions
{
/// <summary>
/// 10进制转16进制
/// </summary>
/// <param name="strNumber"></param>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true, Name = "ConvertToHexadecimal")]
public static SqlString ConvertToHexadecimal(SqlString strNumber)
{
SqlString result = string.Empty;
string str = strNumber.ToString();
int number = 0;
if (int.TryParse(str, out number))
{
result = number.ToString("X");
}
return result;
} /// <summary>
/// 16进制转10进制
/// </summary>
/// <param name="strNumber"></param>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true, Name = "ConvertToDecimal")]
public static SqlString ConvertToDecimal(SqlString strNumber)
{
SqlString result = string.Empty;
string str = strNumber.ToString();
int number = 0;
try
{
number = int.Parse(str, System.Globalization.NumberStyles.HexNumber);
result = Convert.ToString(number, 10);
}
catch
{
}
return result;
}
}

public partial class UserDefinedFunctions
{
/// <summary>
/// SQL Server 字符串分割方法
/// </summary>
/// <param name="separator"></param>
/// <param name="pendingString"></param>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction(
DataAccess = DataAccessKind.Read,
IsDeterministic = true,
Name = "SqlSplit",
FillRowMethodName = "SqlSplit_FillRow",
TableDefinition = "SerialNumber int,StringValue nvarchar(1024)")]
public static IEnumerable SqlSplit(SqlString separator, SqlString pendingString)
{
string _separator = string.Empty;
string _pendingString = string.Empty;
if (separator.IsNull)
{
_separator = ",";
}
else
{
_separator = separator.ToString();
if (string.IsNullOrEmpty(_separator))
{
_separator = ",";
}
} if (pendingString.IsNull)
{
return null;
}
else
{
_pendingString = pendingString.ToString();
if (string.IsNullOrEmpty(_pendingString))
{
return null;
}
} string[] strs = _pendingString.Split(new string[] { _separator }, StringSplitOptions.RemoveEmptyEntries);
if (strs.Length <= 0)
{
return null;
} List<ResultData> resultDataList = new List<ResultData>();
for (int i = 0; i < strs.Length; i++)
{
resultDataList.Add(new ResultData(i + 1, strs[i]));
}
return resultDataList;
} /// <summary>
/// 填充数据方法
/// </summary>
/// <param name="obj"></param>
/// <param name="serialNumber"></param>
/// <param name="stringValue"></param>
public static void SqlSplit_FillRow(Object obj, out SqlInt32 SerialNumber, out SqlString StringValue)
{
ResultData resultData = (ResultData)obj;
SerialNumber = resultData.SerialNumber;
StringValue = resultData.StringValue;
} /// <summary>
/// 定义返回类型
/// </summary>
public class ResultData
{
/// <summary>
/// 序号，即行号
/// </summary>
public SqlInt32 SerialNumber { get; set; } /// <summary>
/// 分割后的每个子字符串
/// </summary>
public SqlString StringValue { get; set; } public ResultData(SqlInt32 serialNumber, SqlString stringValue)
{
SerialNumber = serialNumber;
StringValue = stringValue;
}
}
}

--属性 --说明
--DataAccess --指示该函数是否涉及访问存储在SQL Server的数据
--FillRowMethodName --在同一个类的方法的名称作为表值函数(TVF)，这个参数在表值函数中才会用到，用于指定表值函数的数据填充方法
--IsDeterministic --指示用户定义的函数是否是确定性的
--IsPrecise --指示函数是否涉及不精确计算，如浮点运算
--Name --函数在SQL Server中注册时使用的函数的名称
--SystemDataAccess --指示该函数是否需要访问存储在系统目录或SQL Server虚拟系统表中的数据
--TableDefinition --如果方法作为表值函数(TVF)，则为一个字符串，该字符串表示表结构的定义

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToDuplicates = false,
IsInvariantToNulls = true,
IsInvariantToOrder = false,
MaxByteSize = 8000,
Name = "SumString")]
public struct UserDefinedSqlAggregate : IBinarySerialize
{
private StringBuilder stringBuilder; /// <summary>
/// 查询处理器使用此方法初始化聚合的计算
/// </summary>
public void Init()
{
stringBuilder = new StringBuilder();
} /// <summary>
/// 查询处理器使用此方法累计聚合值
/// </summary>
/// <param name="Value"></param>
public void Accumulate(SqlString Value)
{
stringBuilder.Append(string.Format("{0},", Value));
} /// <summary>
/// 查询处理器使用此方法合并聚合的多个部分计算的值
/// </summary>
/// <param name="Group"></param>
public void Merge(UserDefinedSqlAggregate Group)
{
stringBuilder.Append(Group.stringBuilder);
} /// <summary>
/// 此方法用于返回完成聚合计算的结果
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
return new SqlString(stringBuilder.ToString());
} #region Implement interface IBinarySerialize
/// <summary>
/// 读
/// </summary>
/// <param name="r"></param>
public void Read(System.IO.BinaryReader r)
{
stringBuilder = new StringBuilder(r.ReadString());
} /// <summary>
/// 写
/// </summary>
/// <param name="w"></param>
public void Write(System.IO.BinaryWriter w)
{
w.Write(stringBuilder.ToString());
}
#endregion
}

--属性 --说明
--Format --选择序列化的 Format 格式，默认选择 Native，表示使用本地序列化格式。如果选择 UserDefined，则聚合类需要实现 IBinarySerialize 接口
--IsInvariantToDuplicates --指示聚合是否与重复的值相计算保持不变
--IsInvariantToNulls --指示聚合是否与空值相计算保持不变
--IsInvariantToOrder --指示聚合最后计算的结果是否与顺序无关
--IsNullIfEmpty --指示在没有对任何值进行累计时，聚合返回值是否为 null
--MaxByteSize --聚合实例的最大大小(以字节为单位)
--Name --聚合函数的名称

--注册CLR程序集方式一，指定程序集DLL的路径
USE Test
GO
CREATE ASSEMBLY UserDefinedClrAssembly
--AUTHORIZATION sa --指定数据库所有者，默认为当前用户
FROM 'C:\Users\Administrator\Desktop\CLR Assembly\UserDefinedSqlClr.dll' --指定文件路径
WITH PERMISSION_SET = UNSAFE; --指定程序集的权限
--SAFE：无法访问外部系统资源；
--EXTERNAL_ACCESS：可以访问某些外部系统资源；
--UNSAFE：可以不受限制的访问外部系统资源
GO

--注册CLR程序集方式二，指定程序集DLL的16进制文件流
USE Test
GO
CREATE ASSEMBLY UserDefinedClrAssembly
--AUTHORIZATION sa --指定数据库所有者，默认为当前用户
FROM 0x4D5A90000300000004000000FFFF0000B8000000000000004000000000 --指定DLL的16进制文件流(当然没这么少，我删掉了)
WITH PERMISSION_SET = UNSAFE; --指定程序集的权限
--SAFE：无法访问外部系统资源；
--EXTERNAL_ACCESS：可以访问某些外部系统资源；
--UNSAFE：可以不受限制的访问外部系统资源
GO

--查看程序集信息
SELECT * FROM sys.assemblies --查看模块信息，即自定义函数、视图、存储过程、触发器等等
SELECT * FROM sys.sql_modules
GO

USE Test
GO --注册标量函数 ConvertToHexadecimal
CREATE FUNCTION [dbo].[ConvertToHexadecimal](@strNumber NVARCHAR(128))
RETURNS NVARCHAR(128)
WITH EXECUTE AS CALLER --用于在用户在执行函数的时候对引用的对象进行权限检查
AS
EXTERNAL NAME [UserDefinedClrAssembly].[UserDefinedFunctions].[ConvertToHexadecimal] --EXTERNAL NAME 程序集名.类名.方法名
GO --注册标量函数 ConvertToDecimal
CREATE FUNCTION [dbo].[ConvertToDecimal](@strNumber NVARCHAR(128))
RETURNS NVARCHAR(128)
WITH EXECUTE AS CALLER --用于在用户在执行函数的时候对引用的对象进行权限检查
AS
EXTERNAL NAME [UserDefinedClrAssembly].[UserDefinedFunctions].[ConvertToDecimal] --EXTERNAL NAME 程序集名.类名.方法名
GO --注册表值函数 SqlSplit
CREATE FUNCTION [dbo].[SqlSplit](@separator NVARCHAR(32),@string NVARCHAR(MAX))
RETURNS TABLE
(
SerialNumber INT,
StringValue NVARCHAR(1024)
)
WITH EXECUTE AS CALLER --用于在用户在执行函数的时候对引用的对象进行权限检查
AS
EXTERNAL NAME [UserDefinedClrAssembly].[UserDefinedFunctions].[SqlSplit] --EXTERNAL NAME 程序集名.类名.方法名
GO --注册聚合函数 SumString
CREATE AGGREGATE [dbo].[SumString](@params NVARCHAR(128))
RETURNS NVARCHAR(MAX)
EXTERNAL NAME [UserDefinedClrAssembly].[UserDefinedSqlAggregate] --EXTERNAL NAME 程序集名.类名
GO

DECLARE @TempTable TABLE
(
Id INT NOT NULL,
Name NVARCHAR(32) NOT NULL
)
INSERT INTO @TempTable (
Id,
[Name]
)
SELECT '1','小张' UNION ALL
SELECT '2','小明' UNION ALL
SELECT '2','小丽' UNION ALL
SELECT '2','小李' UNION ALL
SELECT '3','小王' UNION ALL
SELECT '3','小舞' SELECT dbo.ConvertToHexadecimal('15')SELECT dbo.ConvertToDecimal('FC')SELECT * FROM SqlSplit(',',',123,456,789,')SELECT Id,dbo.SumString([Name]) Names
FROM @TempTable
GROUP BY Id

--删除标量函数 ConvertToHexadecimal
DROP FUNCTION dbo.ConvertToHexadecimal--删除标量函数 ConvertToDecimal
DROP FUNCTION dbo.ConvertToDecimal--删除表值函数 SqlSplit
DROP FUNCTION dbo.SqlSplit--删除聚合函数 SumString
DROP FUNCTION dbo.SumString--删除程序集 UserDefinedClrAssembly
DROP ASSEMBLY UserDefinedClrAssembly