开发 SQL Server CLR存储过程和部署代码

C# 类库开发代码

using Microsoft.SqlServer.Server;
using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Net;
using System.Reflection;
using System.Text;
using System.Xml;
using System.Web;
using System.Net.Sockets;
using System.Collections.Generic;

public class CLRService
{
    /// <summary>
    /// 查看CLR版本
    /// </summary>
    /// <param name="I_ReturnMessage"></param>
    [SqlProcedure]
    public static void CLR_Version(out string I_ReturnMessage)
    {
        I_ReturnMessage = Assembly.GetExecutingAssembly().GetName().Version.ToString();
    }

   [SqlProcedure]
    public static void SocketSend(string ServerIP, int Port, string SenderMessage, out int ReturnValue, out string I_ReturnMessage)
    {
        ReturnValue = -1;
        I_ReturnMessage = "";
        Socket mySocket = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);
        try
        {
            ////域名解析
            //IPHostEntry ipHostInfo = Dns.Resolve("host.contoso.com");
            //IPAddress ipAddress = ipHostInfo.AddressList[0];

            IPAddress ipAddress = IPAddress.Parse(ServerIP);
            IPEndPoint ipEndPoint = new IPEndPoint(ipAddress, Port);
            mySocket.Connect(ipEndPoint);
            byte[] byteSend = Encoding.UTF8.GetBytes(SenderMessage);
            mySocket.Send(byteSend, byteSend.Length, 0);
            
            byte[] byteReceive = new byte[1024];
            int length = mySocket.Receive(byteReceive);    
            I_ReturnMessage = Encoding.UTF8.GetString(byteReceive, 0, length);
            ReturnValue = 0;

            mySocket.Close();
        }
        catch (ArgumentNullException ae)
        {
            if (mySocket.Connected)
            {
                mySocket.Shutdown(SocketShutdown.Both);
                mySocket.Close();
            }
            I_ReturnMessage = "Socket:Fail ArgumentNullException : "+ ae.ToString();
        }
        catch (SocketException se)
        {
            if (mySocket.Connected)
            {
                mySocket.Shutdown(SocketShutdown.Both);
                mySocket.Close();
            }
            I_ReturnMessage = "Socket:Fail SocketException : "+ se.ToString();
        }
        catch (Exception e)
        {
            if (mySocket.Connected)
            {
                mySocket.Shutdown(SocketShutdown.Both);
                mySocket.Close();
            }
            I_ReturnMessage = "Socket:Fail Unexpected exception : "+ e.ToString();
        }
        finally
        {
            if (mySocket.Connected)
            {
                mySocket.Shutdown(SocketShutdown.Both);
                mySocket.Close();
            }
        }
    }

}

SQL Server 部署代码：

/*文件名：SqlCLRProcedure
*
*2019年10月22日
*================================================================
* 描述：SQL Server CLR 存储过程
* 内容：1、版本检查
	2、发送套接字数据SocketSend
*================================================================
部署脚本:
数据库查询设计器中执行
-- C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\vsdiag_regwcf.exe -u
-- 如不执行以上操作，安装后续服务时将报machine.config的错误
-- 执行上述操作后，服务器需要重启.*/

--部署.NET4.0程序集
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO

ALTER DATABASE [OrBitClr] SET Trustworthy ON;
GO

use [OrBitClr];
exec sp_changedbowner 'sa';
GO


CREATE ASSEMBLY [System.Web]
from
    'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Web.dll'
with
    permission_set = UNSAFE; --Fails if not 64 on 64 bit machines  
GO

--部署本程序集
CREATE ASSEMBLY  
[SqlCLRProcedure] from
'D:\SqlCLRProcedure.dll'
with permission_set = UNSAFE  
GO

--[CLR_Version]
CREATE PROCEDURE [dbo].[CLR_Version] 
( 
@I_ReturnMessage nvarchar(4000)='' output
) 
WITH EXECUTE AS CALLER 
AS
EXTERNAL NAME [SqlCLRProcedure].[CLRService].[CLR_Version] 
GO 


--SocketSend
CREATE PROCEDURE [dbo].[SocketSend] 
( 
@ServerIP nvarchar(100)='',
@Port int= 8007 ,
@SenderMessage nvarchar(max)='',
@ReturnValue int=0 output,
@I_ReturnMessage nvarchar(4000)='' output
) 
WITH EXECUTE AS CALLER 
AS
EXTERNAL NAME [SqlCLRProcedure].[CLRService].[SocketSend] 
GO