新建一个SQL CLR数据库项目，来对数据库的变化进行监听，如：当有数据发生变化（增删改）， SQL CLR数据库项目就会获得通知，此时我们在开启一个Socket，将数据库变化的消息发送到IIS服务器，IIS服务器然后再发布出数据库已发送变化的消息，此时连接到服务器的客户端就会收到通知，就会根据服务器发来的表名，去决定是否需要重新获取数据，已达到客户端的数据是实时数据的效果：
第一步：
第二：
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Web.Services;
using System.Net.Sockets;
using System.Threading;
using System.Net;
using System.Text;
using System.Collections.Generic;
using System.Runtime.Remoting.Contexts;
public partial class Triggers
{
// 为目标输入现有表或视图并取消对特性行的注释
[Microsoft.SqlServer.Server.SqlTrigger(Name = "Trigger1", Target =
"Users", Event = "AFTER INSERT, UPDATE")]
public static void Trigger1()
{
SqlContext.Pipe.Send("hehuajun");
List socketPool = new List();
bool firstTime = true;
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlParameter userName = new SqlParameter("@username",
System.Data.SqlDbType.NVarChar);
if (triggContext.TriggerAction == TriggerAction.Insert)
{
using (SqlConnection conn = new SqlConnection("context
connection=true"))
{
conn.Open();
SqlCommand sqlComm = new SqlCommand();
SqlPipe sqlP = SqlContext.Pipe;
sqlComm.Connection = conn;
sqlComm.CommandText = "SELECT UserName from INSERTED";
userName.Value = sqlComm.ExecuteScalar().ToString();
sqlComm.CommandText = "INSERT UsersAudit(UserName) VALUES(‘" +
userName + "‘)";
sqlP.Send(sqlComm.CommandText);
sqlP.ExecuteAndSend(sqlComm);
Socket socket = new Socket(AddressFamily.InterNetwork,
SocketType.Stream, ProtocolType.Tcp);
IPEndPoint ipep = new IPEndPoint(IPAddress.Parse("192.168.10.11"),
4502);
//填写自己电脑的IP或者其他电脑的IP，如果是其他电脑IP的话需将ConsoleApplication_socketServer工程放在对应的电脑上。
SqlServerProjectDemo.TableNotifyProtocol p = new
SqlServerProjectDemo.TableNotifyProtocol();
p.TableName = "Users";
p.OperatorOnTable = "Update";
socket.Connect(ipep);
socket.Send(p.TableNotifyProtocolToBytes());
socket.Close();
}
}
}
//static Socket serverSocket;
// static Socket clientSocket;
// static Thread thread;
//public static void StartSocket()
//{
// IPEndPoint ipep = new IPEndPoint(IPAddress.Any, 4530);
// serverSocket = new Socket(ipep.AddressFamily,
SocketType.Stream, ProtocolType.Tcp);
// serverSocket.Bind(ipep);
// serverSocket.Listen(10);
// while (true)
// {
// Socket s = serverSocket.Accept();
// socketPool.Add(s);
// // clientSocket = s;
// // thread = new Thread(new ThreadStart(doWork));
// thread = new Thread(new ThreadStart(() =>
// {
// doWork(s, ASCIIEncoding.ASCII.GetBytes("1"));
// }));
// thread.Start();
// }
//}
//public static void doWork(Socket s, byte[] buffer)
//{
// // Socket s = clientSocket;//客户端信息
// IPEndPoint ipEndPoint = (IPEndPoint)s.RemoteEndPoint;
// String address = ipEndPoint.Address.ToString();
// String port = ipEndPoint.Port.ToString();
// Console.WriteLine(address + ":" + port + " 连接过来了");
// Byte[] inBuffer = new Byte[1024];
// Byte[] outBuffer = new Byte[1024];
// String inBufferStr;
// String outBufferStr;
// try
// {
// while (true)
// {
// // s.Receive(inBuffer, 1024, SocketFlags.None);//如果接收的消息为空 阻塞
当前循环
// // inBufferStr = Encoding.ASCII.GetString(inBuffer);
// // Console.WriteLine(address + ":" + port + "说:");
// // Console.WriteLine(inBufferStr);
// //// outBufferStr = Console.ReadLine();
// // //outBufferStr = p.TableNotifyProtocolToBytes();
// // //outBuffer = Encoding.ASCII.GetBytes(outBufferStr);
// // outBuffer = p.TableNotifyProtocolToBytes();
// s.Send(buffer, buffer.Length, SocketFlags.None);
// }
// }
// catch
// {
// Console.WriteLine("客户端已关闭！");
// }
//}
public bool IsEMailAddress(string s)
{
return Regex.IsMatch(s,
"^([\\w-]+\\.)*?[\\w-]+@[\\w-]+\\.([\\w-]+\\.)*?[\\w]+$");
}
}
第三步：为了能使用Socket，需要为程序集签名：
第四步：
在数据库中执行：
USE master
CREATE ASYMMETRIC KEY SQLCLRTestKey1
FROM EXECUTABLE FILE = ‘C:\SqlServerProjectDemo.dll‘
CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey
GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin;
GO
注意：最好不要直接使用debug目录，可以将你的项目生成的dll拷贝到另外的地方。如：C:\SqlServerProjectDemo.dll
F5启动部署：
成功后：
在数据库中执行
insert into Users(UserName,Pass) values(‘admin‘,‘admin‘)
Update Users set UserName=‘哈哈哈‘ where UserName=‘admin‘
然后在你的Socket端的服务器上就会收到 你发送过去的数据。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace SqlServerProjectDemo
{
public struct TableNotifyProtocol
{
public string TableName;
public string OperatorOnTable;
public byte[] TableNotifyProtocolToBytes()
{
string s = "TableName=" + TableName + "|" + "OperatorOnTable=" +
OperatorOnTable+"|";
byte[] bTableName = ASCIIEncoding.ASCII.GetBytes(TableName);
byte[] bOperatorOnTable =
ASCIIEncoding.ASCII.GetBytes(OperatorOnTable);
//byte[] buffer = new byte[bTableName.Length +
bOperatorOnTable.Length];
//ASCIIEncoding.ASCII.GetBytes(TableName, 0, bTableName.Length,
buffer, 0);
//ASCIIEncoding.ASCII.GetBytes(OperatorOnTable, 0,
bOperatorOnTable.Length, buffer, bTableName.Length);
byte[] buffer = ASCIIEncoding.ASCII.GetBytes(s);
return buffer;
}
public TableNotifyProtocol GetModel(byte[] buffer)
{
string s = ASCIIEncoding.ASCII.GetString(buffer);
TableNotifyProtocol model = new TableNotifyProtocol();
model.OperatorOnTable = GetString(s, "OperatorOnTable");
model.TableName = GetString(s, "TableName");
return model;
}
private string GetString(string s,string key)
{
string tmp = s.Substring(s.IndexOf(key) + key.Length + 1);
string v=tmp.Substring(0,tmp.IndexOf("|"));
return v;
}
}
}