PCB MS SQL 标量函数(CLR) 实现Socket发送消息

        /// <summary>
        /// Socket  
        /// </summary>
        /// <param name="Content"></param>
        /// <returns></returns>
        [SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.Read)]
        public static SqlString ScoketSend(string Content)
        {
            string str = "";
            var socket = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);
            try
            {
                socket.Connect("192.168.224.42", 8885);
                System.Threading.Thread.Sleep(100);
                var outputBuffer = Encoding.Unicode.GetBytes(Content);
                socket.BeginSend(outputBuffer, 0, outputBuffer.Length, SocketFlags.None, null, null);
                str = "传送成功 " + socket.RemoteEndPoint.ToString();
            }
            catch (Exception e)
            {
                str = "传送失败 " + e.Message;
            }
            finally
            {
                if (socket != null && socket.Connected)
                {
                    socket.Shutdown(SocketShutdown.Both);
                    System.Threading.Thread.Sleep(100);
                    socket.Close();
                }
            }
            retu

二.SQL服务器CLR配置（允许SQL调用.net程序）

    sp_configure ‘show advanced options‘, 1; 
    RECONFIGURE WITH override
    GO 
    sp_configure ‘clr enabled‘, 1; 
    RECONFIGURE WITH override
    GO
    Sp_changedbowner ‘sa‘,true   --sa改为当前登入用户名
    alter database [dbname] set trustworthy on    --bbname 改为自己的库名

三.注册 CLR 程序集

   create  ASSEMBLY SQLfunctionAssembly   
   FROM ‘D:\SQLClr.dll‘      --改为自己C#写的dll路径填写
   WITH PERMISSION_SET = UNSAFE;   

        创建的.net程序集数据会写入下表：

  select * from sys.assemblies 
  select  * from sys.assembly_files

四.创建标量函数

CREATE FUNCTION [dbo].[ScoketSend](@Content [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SQLfunctionAssembly].[SQLClr.SQLfunction].[ScoketSend]
GO

五.测试ScoketSend函数

SELECT dbo.ScoketSend(‘pcbren ‘ + CAST(cast( floor(rand()* 10000) as int ) AS VARCHAR(4)));

 简单创建了一个Sockect服务端，测试用SQL发送Sockect信息到服务端,查看一下效果

PCB MS SQL 标量函数(CLR) 实现Socket发送消息

标签：name   sem   dbn   get   none   address   服务器   平台   cti