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

C#如下：

public class CLRFunctions    {        /// 
        /// 数据加密        /// 
        ///         /// 
             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;        }        /// 
        /// 数据解密        /// 
        ///         /// 
             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';goreconfigure;goexec sp_configure 'clr enabled', '1'goreconfigure;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 goCREATE FUNCTION dbo.DataEncrypt(@target nvarchar (200),@keyLen int)RETURNS nvarchar (200)ASBEGINDECLARE @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 @retEND go  if  exists(select 0 from sysobjects where name='DataDecrypt' and xtype='FN') begin drop function dbo.DataDecrypt end goCREATE FUNCTION dbo.DataDecrypt(@target nvarchar (200),@keyLen int)RETURNS nvarchar (200)ASBEGINDECLARE @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 @retEND