MSSQL添加程序集并调用方法

需求是数据库中有些字段数据加密了，迁移数据需要将数据解密，解密方式是用.net完成的，所以让mssql调用程序集来解决问题。

1.将.net解密类编译一个dll文件，代码如下：

namespace DecodeCLR
{
　　public class CLRClass
    {
        [Microsoft.SqlServer.Server.SqlMethod]
        public static string Decode(string source_str)
        {
            return Utils.Decode(source_str);
        }
    }
}

 

2.添加程序集

EXEC sp_configure 'show advanced options','1'; 
GO 
RECONFIGURE; 
GO 
EXEC sp_configure 'clr enabled','1' 
RECONFIGURE; 
GO 

ALTER DATABASE [DATABASE] SET TRUSTWORTHY ON 
GO 

--将dll文件添加到信任白名单
DECLARE @hash AS BINARY(64) = (SELECT HASHBYTES('SHA2_512', (SELECT * FROM OPENROWSET (BULK 'dll文件物理路径', SINGLE_BLOB) AS [Data])))
EXEC sp_add_trusted_assembly @hash
go

--创建程序集，【SQL_CLR_Decode】是创建的程序集名称，可以自定义
CREATE ASSEMBLY SQL_CLR_Decode
FROM 'dll文件物理路径' 
WITH PERMISSION_SET = unSAFE 
GO

添加成功可以看到当前数据库——可编程性——程序集下多了一个名为SQL_CLR_Decode的程序集

 　　如果程序集之前删除过，再添加时可能会报错，提示在"master数据库中记录的数据库所有者SID与数据库所有者SID不同。您应该通过使用ALTER AUTHORIZATION语句重置数据"，可以执行下面脚本重置数据库：

--DATABASE为需要重置的数据库名称
DECLARE @Command VARCHAR(MAX) = 'ALTER AUTHORIZATION ON DATABASE::[DATABASE] TO 
[sa]' 

SELECT @Command = REPLACE(REPLACE(@Command 
            , 'DATABASE', SD.Name)
            , 'sa', SL.Name)
FROM master..sysdatabases SD 
JOIN master..syslogins SL ON  SD.SID = SL.SID
WHERE  SD.Name = DB_NAME()

PRINT @Command
EXEC(@Command)

 

　　

3.调用程序集，创建一个标量值函数，在函数里调用程序集

--方法传入传出参数数量与类型须和程序集中调用方法相同
CREATE FUNCTION SqlDecode(@source_str NVARCHAR(4000)) 
RETURNS NVARCHAR(4000) 
AS 
--SQL_CLR_Decode是创建的程序集名称，DecodeCLR是程序集中命名空间的名称，CLRClass是类名，Decode是要调用的程序集中的方法名
EXTERNAL NAME SQL_CLR_Decode.[DecodeCLR.CLRClass].Decode 
GO   

 

4.使用程序集的时候直接调用方法即可看到结果

select dbo.SqlDecode('1Y3C1Y3Y')