利用Socket监听SqlServer数据库的变化,并推送消息到客户端

新建一个SQL CLR数据库项目,来对数据库的变化进行监听,如:当有数据发生变化(增删改), SQL CLR数据库项目就会获得通知,此时我们在开启一个Socket,将数据库变化的消息发送到IIS服务器,IIS服务器然后再发布出数据库已发送变化的消息,此时连接到服务器的客户端就会收到通知,就会根据服务器发来的表名,去决定是否需要重新获取数据,已达到客户端的数据是实时数据的效果:

第一步:
 
利用Socket监听SqlServer数据库的变化,并推送消息到客户端

 

第二:
 
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,需要为程序集签名:
利用Socket监听SqlServer数据库的变化,并推送消息到客户端

第四步:
 
在数据库中执行:
 
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;
 }
 }
 
 
}

 

利用Socket监听SqlServer数据库的变化,并推送消息到客户端,布布扣,bubuko.com

利用Socket监听SqlServer数据库的变化,并推送消息到客户端

上一篇:type=INNODB和engine=INNODB的区别


下一篇:PL/SQL之DBMS_SQL包使用1