Biegral Blog

 --1、默认情况下，SQL Server中的CLR是关闭的，所以我们需要执行如下命令打开CLR：  
exec sp_configure 'clr enabled',1     
reconfigure     
GO  

-- DROP FUNCTION dbo.fnScoketSend   --删除Function
-- drop assembly SqlDependency      --删除Dependency
-- create assembly SqlDependency FROM 'E:\SqlDependency.dll' WITH PERMISSION_SET = UNSAFE --1、创建clr依赖
CREATE FUNCTION dbo.fnScoketSend       --2、创建Function
(       
    @Content as nvarchar(MAX)       
)        
RETURNS nvarchar(MAX)      
 AS EXTERNAL name [SqlDependency].[SqlDependency.Notice].[ScoketSendContent]

 --DROP TRIGGER dbo.notify_trigger
-- 3、创建表触发器
CREATE TRIGGER notify_trigger ON  [dbo].[Sample]  
    AFTER  INSERT, DELETE, UPDATE   
AS     
BEGIN    
    /*  
        update触发器会在更新数据后，  
        将更新前的数据保存在deleted表中，更  
        新后的数据保存在inserted表中。  
    */  
    DECLARE @UpdateID NVARCHAR(20)  
    DECLARE @UpdateContent Varchar(MAX)   
    SET  @UpdateID=(SELECT  Deleted.Id FROM  Deleted)   
    SET  @UpdateContent=(SELECT  Inserted.UserAddr FROM  Inserted)   
	SELECT  dbo.fnScoketSend(@UpdateContent)   
END  

--4、测试
INSERT INTO Sample(UserName,UserAddr,AddDate) VALUES('张三','深圳市南山区蛇口',GETDATE())
SELECT * FROM Sample

using Microsoft.SqlServer.Server;
using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Sockets;
using System.Text;
using System.Threading.Tasks;

//SqlDependency.dll
namespace SqlDependency
{
    public class Notice
    {
        [SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.Read)]
        public static SqlString ScoketSendContent(SqlString Content)
        {
            string str = "";
            //创建一个Socket
            var socket = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);
            try
            {
                socket.Connect("121.77.61.65", 4530);
                //延时
                System.Threading.Thread.Sleep(100);
                var outputBuffer = Encoding.ASCII.GetBytes("new data");
                socket.BeginSend(outputBuffer, 0, outputBuffer.Length, SocketFlags.None, null, null);
                str = "传送成功";
            }
            catch (Exception e)
            {
                str = "传送失败" + e.Message;
            }
            finally
            {
                if (socket != null && socket.Connected)
                {
                    //关闭双方通信
                    socket.Shutdown(SocketShutdown.Both);
                    //延时
                    System.Threading.Thread.Sleep(100);
                    //关闭socket
                    socket.Close();
                }
            }
            return str;
        }
    }
}