kets;
05
using Microsoft.SqlServer.Server;
06
07
using System.Net.Sockets;
08
namespace SqlDependency
09
{
10
public class Program
11
{
12
13
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.Read)]
14
public static String WriteStringToFile(String FileFullPath, String Contend)
15
{
16
17
FileInfo Fi = new FileInfo(FileFullPath);
18
if (!Fi.Directory.Exists)
19
{
20
Fi.Directory.Create();
21
}
22
23
using (StreamWriter rw = File.CreateText(FileFullPath))
24
{
25
26
rw.WriteLine(Contend);
27
TcpClient tcpClient = new TcpClient();
28
29
try
30
{
31
if (tcpClient == null)
32
{
33
tcpClient = new TcpClient();
34
tcpClient.ReceiveTimeout = 20000;
35
}
36
if (tcpClient.Connected == false)
37
{
38
System.Net.IPAddress address = System.Net.IPAddress.Parse(Contend);
39
System.Net.IPHostEntry ipInfor = System.Net.Dns.GetHostByAddress(address);
40
string hostName = ipInfor.HostName;
41
IPEndPoint serverEndPoint = new IPEndPoint(IPAddress.Parse("127.0.0.1"), 10010);
42
tcpClient.Connect(serverEndPoint);
43
rw.Write(hostName);
44
}
45
rw.Write("连接成功，先发送指令");
46
// Translate the passed message into ASCII and store it as a Byte array.
47
Byte[] data = System.Text.Encoding.ASCII.GetBytes("new data!");
48
49
NetworkStream stream = tcpClient.GetStream();
50
51
// Send the message to the connected TcpServer.
52
stream.Write(data, 0, data.Length);
53
stream.Close();
54
55
}
56
catch (Exception e)
57
{
58
rw.Write(e.Message);
59
}
60
tcpClient.Close();
61
rw.Flush();
62
rw.Close();
63
return "";
64
}
65
}
66
}
67
}
接下来，便开始配置sql server啦：
首先开启sql server的clr支持：
1
开启数据库CLR 支持
2
--exec sp_configure 'clr enabled', 1;
3
--开始数据的验证
4
alter database dbname set TRUSTWORTHY on;
5
RECONFIGURE
接着在sql server 2008中，新建查询窗口。加载刚才编写的dll SqlDependency.dll，并注册方法，然后写触发器，当表数据有变化时，触发函数。：
01
use Temp;--数据库名
02
create assembly SqlDependency FROM 'D:\SqlDependency.dll'--程序集名称和地址
03
WITH PERMISSION_SET = UNSAFE
04
GO
05
06
--方法名写正确，为程序集中的方法名，注意参数个数
07
create function WriteStringToFile(@FileFullName as nvarchar(max), @FileContend AS nvarchar(max))
08
returns nvarchar(max)
09
with returns null on null input
10
external name [SqlDependency].[SqlDependency.Program].[WriteStringToFile]
11
GO
12
13
--编写触发器，传递参数以及
14
CREATE TRIGGER [dbo].[UserTableChangedEvent] on [dbo].[Simple]
15
FOR INSERT, DELETE, UPDATE
16
17
AS
18
BEGIN
19
DECLARE @Contend AS VARCHAR(100)
20
DECLARE @FileName AS VARCHAR(MAX)
21
SET @FileName ='D:\\MSG\\'+CONVERT(varchar(12) , getdate(), 112 )+'\\'+ convert(nvarchar(50), NEWID())+'.TXT'
22
23
SET @Contend = '127.0.0.1';
24
Select dbo.WriteStringToFile(@FileName, @Contend)
25
26
END
27
GO
注意，我的应用程序和 数据库在一台服务器上，所以地址都是127.0.0.1.可跟据实际填写正确地址。
再次在sql server中新建一个查询窗口，插入语句，进行测试吧。
如果过程中有问题，需要更新程序，方便地删除之上所创建的几个东
1
drop TRIGGER [dbo].[UserTableChangedEvent]
2
drop function WriteStringToFile
3
drop assembly SqlDependency