讓SQL Server呼叫DLL檔案的方法詳解

釋迦苦僧的部落格發表於2014-09-24
SQLServer

背景

在處理資料或者分析資料時，我們常常需要加入一定的邏輯，該些處理邏輯有些sql是可以支援，有些邏輯SQL則無能為力，在這種情況下，大多數人都會編寫相關的程式來處理成自己想要的資料，但每次處理相同邏輯時，都需要執行一次程式非常麻煩。

案例

IE位址列上的地址在記入日誌表中時，其資料是通過編碼的，如果我們想要看到明文，則需要相應的解碼，可以用SQL語句來實現，如：

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','1'
go
--重新配置 就是用來更新使用sp_configure 系統儲存過程更改的配置選項的當前配置值
reconfigure
go
--建立可信賴
alter database Auth3 set trustworthy on
go
--新增關聯DLL
CREATE ASSEMBLY [System.Web] FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.dll' WITH PERMISSION_SET = UNSAFE
go
--開啟CLR整合
EXEC sp_configure 'clr enabled','1'
go
--重新配置 就是用來更新使用sp_configure 系統儲存過程更改的配置選項的當前配置值
reconfigure
--新增剛剛編譯的DLL
create 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,如下：

其方法呼叫如下圖：

如有問題歡迎指正

相關文章