sqlserver 触发器中调用clr

因为要在插入一条记录时，能立即调用c#的服务，想到用在sql中通过CLR发送socket实现。

1.socket 协议：选用udp。

2.在sqlserver的要监控的表中增加触发器，在触发器中调用CLR。这里并没有创建触发器类型的CLR，因为CLR属性里的表名总是在编译时报不能解析的错误。干脆就写一个普通的CLR函数。

1.socket server:

新建一个visual studiao的控件台项目，

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Net;
using System.Net.Sockets;
using System.Threading;

namespace YLCN.Con
{
    public class SocketServer
    {
       
        public void Start()
        {
            int recv;
            byte[] data = new byte[1024];

            //得到本机IP，设置TCP端口号         
            IPEndPoint ip = new IPEndPoint(IPAddress.Any, 8001);
            Socket newsock = new Socket(AddressFamily.InterNetwork, SocketType.Dgram, ProtocolType.Udp);

            //绑定网络地址
            newsock.Bind(ip);

            Console.WriteLine("This is a Server, host name is {0}", Dns.GetHostName());

            //等待客户机连接
            Console.WriteLine("Waiting for a client");

            //得到客户机IP
            IPEndPoint sender = new IPEndPoint(IPAddress.Any, 0);
            EndPoint Remote = (EndPoint)(sender);
            recv = newsock.ReceiveFrom(data, ref Remote);
            Console.WriteLine("Message received from {0}: ", Remote.ToString());
            Console.WriteLine(Encoding.ASCII.GetString(data, 0, recv));

            //客户机连接成功后，发送信息
            string welcome = "你好 ! ";

            //字符串与字节数组相互转换
            data = Encoding.ASCII.GetBytes(welcome);

            //发送信息
            newsock.SendTo(data, data.Length, SocketFlags.None, Remote);
            while (true)
            {
                data = new byte[1024];
                //发送接收信息
                recv = newsock.ReceiveFrom(data, ref Remote);
                Console.WriteLine(Encoding.ASCII.GetString(data, 0, recv));
                newsock.SendTo(data, recv, SocketFlags.None, Remote);
            }
        }
    }

       
   
}

2.创建一个visual studio的sqlserver项目

SocketClient.cs

using System;
using System.Collections.Generic;

using System.Text;
using System.Threading.Tasks;
using System.Net;
using System.Net.Sockets;
using System.Threading;

namespace YLCN.Con
{
    public class SocketClient
    {
      

        public void Start(string msg)
        {

            byte[] data = new byte[1024];


            //构建TCP 服务器
            //Console.WriteLine("This is a Client, host name is {0}", Dns.GetHostName());

            //设置服务IP，设置TCP端口号
            IPEndPoint ip = new IPEndPoint(IPAddress.Parse("127.0.0.1"), 8001);

            //定义网络类型，数据连接类型和网络协议UDP
            Socket server = new Socket(AddressFamily.InterNetwork, SocketType.Dgram, ProtocolType.Udp);

            data = Encoding.ASCII.GetBytes(msg);
            server.SendTo(data, data.Length, SocketFlags.None, ip);
            IPEndPoint sender = new IPEndPoint(IPAddress.Any, 0);
            EndPoint Remote = (EndPoint)sender;

            data = new byte[1024];
            //对于不存在的IP地址，加入此行代码后，可以在指定时间内解除阻塞模式限制
            int recv = server.ReceiveFrom(data, ref Remote);
            //Console.WriteLine("Message received from {0}: ", Remote.ToString());
            //Console.WriteLine(Encoding.ASCII.GetString(data, 0, recv));

            server.Close();
        }


    }
}

在数据库testDB中启用CLR

sp_configure 'clr enabled',1

启用unsafe调用（这点非常重要，否则不能在CLR中调用socket。授权要用sysadmin权限)

	ALTER DATABASE TestDB set TRUSTWORTHY ON;  

安装CLR

	CREATE ASSEMBLY [SqlServer.SqlClr.Functions]
FROM '*********\[dll名].dll'
 WITH PERMISSION_SET = UNSAFE

创建触发器

alter TRIGGER trg_tb_title
   ON  tb_Title
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
	DECLARE @title NVARCHAR(50)
	SELECT @title=TITLE FROM inserted
	select  dbo.clr_HelloSqlClr(@title )

END
GO

现在已开发完毕，

启动服务器端，再向表tb_Title中插入数据，可看到每插入一条，在服务端的控制台上就显示一行信息