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