在SQL中使用CRL函数
实验目标：
1. 在SQL中创建CRL函数，使之能够向指定的计算机发送消息
实验步骤
2. 在VS中创建类发送消息的类
3. 将以下代码黏贴进去
using System;
using System.Collections.Generic;
using System.Text;
using System.Net.Sockets;
using System.IO;
namespace ClassLibrary1
{
public class Class1
{
public static string classscoket(string IPaddress, string mes)
{
string msg = mes;
string IPadd = IPaddress;
TcpClient tcpc = new TcpClient(IPadd, 5656);
NetworkStream tcpStream = tcpc.GetStream();
StreamWriter reqStreamW = new StreamWriter(tcpStream);
reqStreamW.Write(msg);
reqStreamW.Flush();
tcpStream.Close();
tcpc.Close();
return (mes);
}
}
}
点击“生成ClassLibrary“
4. 新建接收消息的客户端
添加引用
using System;
using System.Collections.Generic;
using System.Text;
using System.Net.Sockets;
using System.Windows.Forms;
namespace MessClient1
{
class Program
{
static void Main(string[] args)
{
try
{
TcpListener tcpl = new TcpListener(5656);
tcpl.Start();
while (true)
{
Socket s = tcpl.AcceptSocket();
Byte[] stream = new Byte[80];
int i = s.Receive(stream);
string message = System.Text.Encoding.UTF8.GetString(stream);
MessageBox.Show(message);
}
}
catch (System.Security.SecurityException)
{
MessageBox.Show("防火墙安全错误！", "错误",
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
catch (Exception)
{
}
}
}
}
5. 在数据库中的配置
exec sp_configure 'clr enable',1
go
reconfigure
go
use personnel
go
alter database personnel set trustworthy on
go --在数据库中引入dll
create assembly [System.Net.Sockets] from 'C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\ClassLibrary1\ClassLibrary1\bin\Debug\ClassLibrary1.dll'
with permission_set=unsafe
可以看到注册的程序集
go --创建函数
create function dbo.crlHelloWord
(
@ipaddress as nvarchar(100),
@message as nvarchar(100)
)
returns nvarchar(200)
as EXTERNAL NAME [System.Net.Sockets].[ClassLibrary1.Class1].classscoket
运行客户端程序，在SQL执行以下命令，可以发现该函数能够想客户端发送消息
select dbo.crlHelloWord('10.7.10.199','你已经删除了条记录')
