如何使用C#监听SQL Server数据库



        新建一个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<Socket> socketPool = new List<Socket>();
        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;
        }
    }
}