SQL Server中调用C#类中的方法实例（使用.NET程序集）

SQL Server中调用C#类中的方法实例（使用.NET程序集）
SQL Server: Could not find type in the assembly
服务器可能资源不足，或者不信任该程序集，因为它的 PERMISSION_SET 设置为 EXTERNAL_ACCESS 或 UNSAFE。

配置类库

using Microsoft.SqlServer.Server;
using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using System.Security.Cryptography;
using System.Text;

namespace CryptHelper
{
    public static class AESHelper
    {
        private const string AES_Key = "1234567890123456";

        //为什么要用base64,因为得到的密文是byte[]，所以默认用base64转成str方便查看
        // AES 加密的初始化向量,加密解密需设置相同的值。需要是16字节
        public readonly static byte[] AES_IV = Encoding.UTF8.GetBytes("Dy1;09w0x#0zR>'}");

        /// <summary>
        ///  加密
        /// </summary>
        /// <param name="key">密钥</param>
        /// <param name="data">待加密数据</param>
        /// <returns>加密后的数据</returns>
        [SqlMethod]
        public static SqlString Encrypt(string data)
        {
            string key = AES_Key;
            using (AesCryptoServiceProvider aesAlg = new AesCryptoServiceProvider())
            {
                aesAlg.Key = Encoding.UTF8.GetBytes(key);
                aesAlg.IV = AES_IV;
                ICryptoTransform encryptor = aesAlg.CreateEncryptor(aesAlg.Key, aesAlg.IV);
                using (MemoryStream msEncrypt = new MemoryStream())
                {
                    using (CryptoStream csEncrypt = new CryptoStream(msEncrypt, encryptor, CryptoStreamMode.Write))
                    {
                        using (StreamWriter swEncrypt = new StreamWriter(csEncrypt))
                        {
                            swEncrypt.Write(data);
                        }
                        byte[] bytes = msEncrypt.ToArray();
                        return new SqlString(Convert.ToBase64String(bytes));
                    }
                }
            }
        }

        /// <summary>
        /// 解密
        /// </summary>
        /// <param name="key">密钥</param>
        /// <param name="encryptData">已加密数据</param>
        /// <returns>原数据</returns>
        [SqlMethod]
        public static SqlString Decrypt(string encryptData)
        {
            string key = AES_Key;
            byte[] inputBytes = Convert.FromBase64String(encryptData);
            using (AesCryptoServiceProvider aesAlg = new AesCryptoServiceProvider())
            {
                aesAlg.Key = Encoding.UTF8.GetBytes(key);
                aesAlg.IV = AES_IV;

                ICryptoTransform decryptor = aesAlg.CreateDecryptor(aesAlg.Key, aesAlg.IV);
                using (MemoryStream msEncrypt = new MemoryStream(inputBytes))
                {
                    using (CryptoStream csEncrypt = new CryptoStream(msEncrypt, decryptor, CryptoStreamMode.Read))
                    {
                        using (StreamReader srEncrypt = new StreamReader(csEncrypt))
                        {
                            return new SqlString(srEncrypt.ReadToEnd());
                        }
                    }
                }
            }
        }
    }
}

配置数据库

更改数据库的一些配置

exec sp_configure 'show advanced options', '1';
reconfigure;
go
exec sp_configure 'clr enabled', '1'
reconfigure;
go
alter database [DB] set trustworthy on;	--The database for storing the assembly
reconfigure;
go
exec sp_configure 'show advanced options', '0';
reconfigure;
go

新建程序集

新建function

CREATE FUNCTION AESEncrypt(@data NVARCHAR(max))
RETURNS NVARCHAR(max)
AS
EXTERNAL NAME CryptHelper.[CryptHelper.AESHelper].Encrypt
go

CREATE FUNCTION AESDecrypt(@data NVARCHAR(max))
RETURNS NVARCHAR(max)
AS
EXTERNAL NAME CryptHelper.[CryptHelper.AESHelper].Decrypt
go

调用

select dbo.AESEncrypt('giao')
select dbo.AESDecrypt('encryptGiao')

另一种添加方式