首页 > 代码库 > Sql Server 调用DLL

Sql Server 调用DLL

背景

在处理数据或者分析数据时,我们常常需要加入一定的逻辑,该些处理逻辑有些sql是可以支持,有些逻辑SQL则无能为力,在这种情况下,大多数人都会编写相关的程序来处理成自己想要的数据,但每次处理相同逻辑时,都需要运行一次程序非常麻烦。

案例

IE地址栏上的地址在记入日志表中时,其数据是通过编码的,如果我们想要看到明文,则需要相应的解码,可以用SQL语句来实现,如:

摘自:http://blog.csdn.net/ruijc/article/details/6931189

CREATE FUNCTION FN_URLDecode  (   @Str VARCHAR(8000))  RETURNS VARCHAR(8000)  AS  BEGIN    DECLARE @Position  INT;          --‘%‘字符所在位置    DECLARE @Chr       CHAR(16);     --字符常量    DECLARE @Pattern   CHAR(21);    DECLARE @ParseStr  VARCHAR(8000);--解码后的字符串    DECLARE @Hex       UNIQUEIDENTIFIER;--定义16进制模板,因为GUID方便转为BYTE    DECLARE @CurrWord  INT        ;--当前字    DECLARE @BitsCount INT        ;--当前解码位数    DECLARE @HightByte TINYINT;--高位字节    DECLARE @LowByte   TINYINT;--低位字节         SET     @Chr = 0123456789abcdef;    SET     @Pattern = %[%][a-f0-9][a-f0-9]%;    SET     @ParseStr=@Str;    SET     @Hex= 00000000-0000-0000-0000-000000000000;    SET     @CurrWord=0;    SET     @BitsCount=0;    SET     @HightByte=0;    SET     @LowByte=0;        IF (@Str IS NOT NULL OR @Str<>‘‘)     BEGIN       SET    @Position = PATINDEX(@Pattern, @ParseStr);     WHILE @Position>0        BEGIN          SET @Hex=STUFF(@Hex,7,2,LEFT(RIGHT(@ParseStr,len(@ParseStr) - @Position),2));          SET @HightByte=CAST(CAST(@Hex AS BINARY(1)) AS INT);                    IF (@HightByte & 127=@HightByte)           BEGIN             SET @CurrWord=@HightByte;             SET @BitsCount=1;           END                     IF (@HightByte & 192=192)           BEGIN           SET @CurrWord=@HightByte & 31 ;             SET @BitsCount=2;           END            IF (@HightByte & 224=224)           BEGIN             SET @CurrWord = @HightByte & 15              SET @BitsCount = 3             END            IF (@HightByte & 240=240)           BEGIN            SET @CurrWord = @HightByte & 7              SET @BitsCount = 4             END            DECLARE @Index INT;                  DECLARE @NEWCHAR NVARCHAR(2);          SET @Index=1;          SET @NEWCHAR=‘‘;          WHILE @Index<@BitsCount           BEGIN                IF (LEN(@ParseStr)-@Position-3*@Index)<0                 BEGIN                     SET @ParseStr=@Str ;                        SET @Position=0;                     BREAK;                               END              SET @NEWCHAR = LEFT(RIGHT(@ParseStr,LEN(@ParseStr) - @Position - 3* @Index),2);                 IF @NEWCHAR NOT LIKE [a-f0-9][a-f0-9]               BEGIN                  SET @ParseStr = @Str                  SET @Position=0;                BREAK;               END                    SET @Hex = STUFF(@Hex, 7, 2, @NEWCHAR)                      SET @LowByte = CAST(CAST(@Hex AS BINARY(1)) AS INT);                IF @LowByte&192=192              BEGIN                  SET @ParseStr = @Str                  SET @Position=0;                BREAK;              END                             SET @CurrWord = (@CurrWord * 64) | (@LowByte & 63)                            SET @Index =@Index+ 1                                                           END                                                IF @BitsCount > 1                       SET @ParseStr = STUFF(@ParseStr, @Position, 3*(@BitsCount), NCHAR(@CurrWord))           ELSE             BEGIN              set @ParseStr = STUFF(@ParseStr, @Position, 2, NCHAR(@CurrWord))              set @ParseStr = STUFF(@ParseStr, @Position+1, 1, N‘‘)                   END           SET  @Position = PATINDEX(@Pattern, @ParseStr);        END     END     RETURN @ParseStr;  END  

其执行结果如下:

利用SQL不仅需要写很复杂的函数,如果需要加入其他操作时,也需要花大量时间来修改。

如果采用程序处理此类问题那将简单的多,如下:

using System.Text;using System.Web;namespace UrlDecode{    /*code 释迦苦僧*/    public class UrlDll    {        /// <summary>        /// 获取URL的值        /// </summary>         public static string GetUrlPara(string url, string key)        {            key = key + "=";            string[] strs = url.Split(&);            foreach (string str in strs)            {                if (str.IndexOf(key) >= 0)                {                    string sub = str.Substring(str.IndexOf(key) + key.Length, str.Length - str.IndexOf(key) - key.Length);                    string sub2 = MyUrlDeCode(sub, null);                    if (sub2.IndexOf(?) >= 0)                    {                        sub2 = sub2.Substring(0, sub2.IndexOf(?));                    }                    return sub2;                }            }            return string.Empty;        }        /// <summary>        /// 解码URL.        /// </summary>        /// <param name="encoding">null为自动选择编码</param>        /// <param name="str"></param>        /// <returns></returns>        public static string MyUrlDeCode(string str, Encoding encoding)        {            if (encoding == null)            {                Encoding utf8 = Encoding.UTF8;                //首先用utf8进行解码                                     string code = HttpUtility.UrlDecode(str, utf8);                string tempcode = code;                if (code.IndexOf(?) >= 0)                {                    tempcode = code.Substring(0, code.IndexOf(?));                }                //将已经解码的字符再次进行编码.                string encode = HttpUtility.UrlEncode(tempcode, utf8).ToUpper();                if (encode.IndexOf(+) >= 0)                {                    encode = encode.Substring(0, encode.IndexOf(+));                }                if (str.ToUpper().Contains(encode.ToUpper()))                    encoding = Encoding.UTF8;                else                    encoding = Encoding.GetEncoding("gb2312");            }            string encodeing = HttpUtility.UrlDecode(str, encoding);            if (encodeing.Contains("%") && encodeing.Length > 8)            {                return MyUrlDeCode2(encodeing, null);            }            return encodeing;        }        public static string MyUrlDeCode2(string str, Encoding encoding)        {            if (encoding == null)            {                Encoding utf8 = Encoding.UTF8;                //首先用utf-8进行解码                                     string code = HttpUtility.UrlDecode(str.ToUpper(), utf8);                //将已经解码的字符再次进行编码.                string encode = HttpUtility.UrlEncode(code, utf8).ToUpper();                if (str.ToUpper() == encode)                    encoding = Encoding.UTF8;                else                    encoding = Encoding.GetEncoding("gb2312");            }            return HttpUtility.UrlDecode(str, encoding);        }    }}

在SQL中调用此类的方法,需要将其封装在DLL中,如下:
1.将类库设置为.NET Framework2.0 如下:

2.在Release下编译成dll

3.将dll添加到SQL Server中

--code 释迦苦僧--修改系统配置的存储过程当设置 show advanced options 参数为 1 时,才允许修改系统配置中的某些高级选相!!系统中这些高级选项默认是不允许修改exec sp_configure show advanced options,1go--重新配置 就是用来更新使用sp_configure 系统存储过程更改的配置选项的当前配置值reconfigurego--建立可信赖alter database Auth3 set trustworthy ongo--添加关联DLLCREATE ASSEMBLY [System.Web] FROM C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.dll WITH PERMISSION_SET = UNSAFEgo--开启CLR集成EXEC sp_configure clr enabled,1go--重新配置 就是用来更新使用sp_configure 系统存储过程更改的配置选项的当前配置值reconfigure--添加刚刚编译的DLLcreate assembly SqlUrlDecode from  D:\Test VS Project\UrlDll\UrlDecode\bin\Release\UrlDecode.dll  go--创建函数CREATE FUNCTION dbo.FunUrlDecode( @url as nvarchar(500),@key as nvarchar(120) )RETURNS nvarchar(200) AS EXTERNAL NAME SqlUrlDecode.[UrlDecode.UrlDll].GetUrlPara               --Sql命名空间   dll命名空间 dll类 dll方法

将dll添加成功后,我们可以在SQL SERVER 找到相关的Assembiles,如下:

其方法调用如下图:

 如有问题欢迎指正

 

作者:释迦苦僧   出处:http://www.cnblogs.com/woxpp/p/3990277.html 本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接。

Sql Server 调用DLL