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';
go
reconfigure;
go
exec sp_configure 'clr enabled', '1'
go
reconfigure;
exec sp_configure 'show advanced options', '1';
go
ALTER DATABASE vv_DB SET TRUSTWORTHY on;
go新建程序集
新建function
CREATE FUNCTION AESEncrypt(@data NVARCHAR(max))
RETURNS NVARCHAR(max)
AS
EXTERNAL NAME CryptHelper.[CryptHelper.AESHelper].Encrypt
goCREATE FUNCTION AESDecrypt(@data NVARCHAR(max))
RETURNS NVARCHAR(max)
AS
EXTERNAL NAME CryptHelper.[CryptHelper.AESHelper].Decrypt
go调用
select dbo.AESEncrypt('giao')
select dbo.AESDecrypt('encryptGiao')