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 

在此感谢以上资料的作者