MSSQL 调用C#程序集 实现C#字符串到字符的转化

10多年前用过MSSQL 调用C#程序集来实现数据的加密和解密，也搞过通过字符偏移实现简单的加密和解密。这次就总结一下吧：

C#如下：

 public class CLRFunctions
    {
        /// <summary>
        /// 数据加密
        /// </summary>
        /// <param name="target"></param>
        /// <returns></returns>

        public static String DataEncrypt(string target,   int keyLen)
        {
            if (string.IsNullOrEmpty(target)) return string.Empty;
            string str = string.Empty;
            char[] arr = target.ToCharArray();
            for (int i = 0; i < arr.Length; i++)
            {
                char c = arr[i];

                str = str + ((char)(c + keyLen));
            }
            return str;
        }

        /// <summary>
        /// 数据解密
        /// </summary>
        /// <param name="target"></param>
        /// <returns></returns>

        public static String DataDecrypt(string target, int keyLen)
        {
            if (string.IsNullOrEmpty(target)) return string.Empty;
            string str = string.Empty;
            char[] arr = target.ToCharArray();
            for (int i = 0; i < arr.Length; i++)
            {
                char c = arr[i];
                str = str + ((char)(c - keyLen));
            }
            return str;
        }
    }

SQL如下，这里需要启用程序集功能，然后在创建程序集：

 
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 

if exists (select * from sys.assemblies where name='CLRFunctions')
  drop assembly CLRFunctions;
go

 CREATE  ASSEMBLY CLRFunctions  FROM  'd:\sql\xxx.dll' ;
 Go

 if  exists(select 0 from sysobjects where name='dbo.DataEncrypt' and xtype='FN')
 begin
 drop function dbo.DataEncrypt
 end

 go
 
 CREATE  FUNCTION  dbo.DataEncrypt   
 (   
      @target as nvarchar (200),
      @keyLen as int
 )    
 RETURNS   nvarchar (200)   
 AS  EXTERNAL NAME CLRFunctions.CLRFunctions.DataEncrypt  
  

 go
  if  exists(select 0 from sysobjects where name='dbo.DataDecrypt' and xtype='FN')
 begin
 drop function dbo.DataDecrypt
 end

 go
 
  CREATE  FUNCTION  dbo.DataDecrypt   
 (   
      @target as nvarchar (200),
      @keyLen as int
 )    
 RETURNS   nvarchar (200)   
 AS  EXTERNAL NAME CLRFunctions.CLRFunctions.DataDecrypt   

后来尝试用SQL实现C#的ToCharArray方法，在实现字符偏移，SQL如下：

 if  exists(select 0 from sysobjects where name='DataEncrypt' and xtype='FN')
 begin
 drop function dbo.DataEncrypt
 end

 go
CREATE FUNCTION dbo.DataEncrypt(@target nvarchar (200),@keyLen int)
RETURNS nvarchar (200)
AS
BEGIN
DECLARE @position int,@ret nvarchar(200);  
SET @position = 1;  
set @ret=''
WHILE @position <= len(@target)  
   BEGIN  
    set @ret=@ret+ nchar(unicode(SUBSTRING(@target, @position, 1))+@keylen)
    SET @position = @position + 1  
   END;  
RETURN @ret
END

 go
  if  exists(select 0 from sysobjects where name='DataDecrypt' and xtype='FN')
 begin
 drop function dbo.DataDecrypt
 end

 go

CREATE FUNCTION dbo.DataDecrypt(@target nvarchar (200),@keyLen int)
RETURNS nvarchar (200)
AS
BEGIN
DECLARE @position int,@ret nvarchar(200);  
SET @position = 1;  
set @ret=''
WHILE @position <= len(@target)  
   BEGIN  
    set @ret=@ret+ nchar(unicode(SUBSTRING(@target, @position, 1))-@keylen)
    SET @position = @position + 1  
   END;  
RETURN @ret
END