SQL觸發器調用.NET的類方法續SQLCLR應用

SQL CLR (SQL Common Language Runtime) 是自 SQL Server 2005 才出現的新功能，它將.NET Framework中的CLR服務注入到 SQL Server 中，使得.NET代碼可在SQL Server服務器進程中執行。

通過在 Microsoft SQL Server 中托管 CLR（稱為 CLR 集成），開發人員可以在托管代碼中編寫存儲過程、觸發器、用戶定義函數、用戶定義類型和用戶定義聚合函數, 改變了以前只能通過T-SQL語言來實現這些功能的局面。因為托管代碼在執行之前會編譯為本機代碼，所以，在有些方案中可以大大提高性能。

本文記錄這兩天SQLCLR的研究使用過程，實現調用函數傳入GUID，通過命名管道發送到目標應用。數據庫是SQL Server2008R2

實現方式是將.NET DLL類庫注冊到SQL Server，從SQL Server中用戶定義函數調用.NET類庫中的方法。

★DLL類庫

namespace SQLCLRlib
{
    public class ControlActive
    {
        /// <summary>
        /// send command
        /// </summary>
        /// <param name="MBID">目標ID</param>
        /// 1:發送成功 0:發送失敗
        public static string sendControlCommand(string MBID)
        {
            try
            {
                NamedPipeClient npc = new NamedPipeClient("localhost", "jc-pipe");
                return npc.Query(MBID);
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
    }
}

若方法中需要訪問數據庫等則需要添加方法的聲明：[Microsoft.SqlServer.Server.SqlFunction(SystemDataAccess = SystemDataAccessKind.Read,DataAccess = DataAccessKind.Read)]

否則報異常：在此上下文中不允許訪問數據。此上下文可能是不帶 DataAccessKind.Read 或 SystemDataAccessKind.Read 標記的函數或方法，也可能是從表值函數的 FillRow 方法為獲取數據而進行的回調，還可能是 UDT 驗證方法。

VS中也有專門的CLR項目模板：添加新建項目，選擇模板"數據庫"->SQL Server,選擇CLR數據庫項目

★SQL Server中的配置

--選擇使用哪個數據庫

--USE DBname

--查看CLR是否開啟
--sp_configure 'clr enabled'
--更改安裝CLR 1：開啟 0：關閉
--exec sp_configure 'clr enabled',1
--reconfigure
--注冊DLL,SQL2008R2支持3.5,類庫項目的目標框架必須對應SQL Server的支持版本

--TRUSTWORTHY:SQL實例是否信任數據庫的內容,默認OFF

--ALTER DATABASE DBname SET TRUSTWORTHY ON;
--create assembly asmSQLCLRlib from 'D:\...\SQLCLRlib.dll' WITH PERMISSION_SET = UNSAFE;
--創建自定義函數
--create function dbo.clrControlActive
--(
-- @MBID as nvarchar(36)
--)
--returns nvarchar(max) as EXTERNAL NAME [asmSQLCLRlib].[程序集.類名].[方法名]
--使用自定義函數
select dbo.clrControlActive('58A3D48E-A713-49C3-8FC6-76C8DF0DFA34')

參考資料

http://www.cnblogs.com/hsrzyn/archive/2013/05/28/1976555.html

http://www.cnblogs.com/wshcn/archive/2011/12/02/2271630.html

http://www.tuicool.com/articles/fANVzmn 

在此感謝以上資料的作者