sql server主动推送客户端更新数据

小谈需求：

最近工作上接到一个需求，做一个web展示数据的报表，最好能实时更新，不限制所用技术。

第一个问题：web服务器推送给浏览器新数据，一开始我想到的最快的最简单的方法就是 在web页面上js轮询了。因为我们的数据更新频率并不快。 后来觉得这种办法有点太土了。 或许长轮询更有效。  当然长轮询的技术很多了。 java 的dwr,c#的 signalr。c#还可以同过异步请求来自己写长轮询。

遇到的第二个问题，就是数据库如何通知web服务器更新数据，下面便是sql server2008的推送了，通过sql server的触发器，当数据表有变化时（增，删，改）就通过tcp请求服务器，服务器会在启动后开启端口一直监听，随时等待通信请求。当收到请求后，就从数据库读取新数据，推送给浏览器。整体大概就这样。

下面是数据库通知服务器。这是一个 winform的demo ,winfom就相当于我们展示数据的服务器了。

最后demo图:

现在我插入一条数据：

然后再看那个客户端：

刚插入的数据已经出现了哦。

客户端代码：

winform:

程序启动后，开启端口监听，如果有收到通信，则通知 dataview更新数据。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91

using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Linq; using System.Net; using System.Net.Sockets; using System.Text; using System.Windows.Forms; using System.Threading;   namespace sql_dependency {     public partial class Form1 : Form     {         public Form1()         {             InitializeComponent();         }           System.Data.SqlClient.SqlConnection conn = null;         string _connstr = "Data Source = 10.6.154.251; database=Temp;user id=sa;pwd=MOcyou0543_";         System.Data.SqlClient.SqlCommand command = null;           private void Form1_Load(object sender, EventArgs e)         {             conn = new System.Data.SqlClient.SqlConnection(_connstr);             command = conn.CreateCommand();             command.CommandText = "select [A],[B],[C] From [Temp].[dbo].[Simple]";             SqlDependency.Start(_connstr);//启动             Thread t = new Thread(new ThreadStart(GetData));             t.Start();         }             private void GetData()         {               SetData();             IPAddress localAddr = IPAddress.Parse("127.0.0.1");             TcpListener tcplistener = new TcpListener(localAddr, 10010);             tcplistener.Start();             byte[] btServerReceive = new byte[2048];             string strServerReceive = string.Empty;             while (true)             {                 TcpClient tcp = tcplistener.AcceptTcpClient();                 Console.WriteLine("Connected!");                 NetworkStream ns = tcp.GetStream();                 int intReceiveLength = ns.Read(btServerReceive, 0, btServerReceive.Length);                 strServerReceive = Encoding.ASCII.GetString(btServerReceive, 0, intReceiveLength);                   SetData();                 tcp.Close();             }           }         private delegate void ChangeDataView();         private void SetData()         {             if (this.InvokeRequired)             {                 this.Invoke(new ChangeDataView(SetData));             }             else             {                 using (SqlDataAdapter adapter = new SqlDataAdapter(command)) //查询数据                 {                     System.Data.DataSet ds = new DataSet();                     adapter.Fill(ds, 0, 100, "Simple");                     dataGridView1.DataSource = ds.Tables["Simple"];                 }               }         }               private void Form1_Closed(object sender, FormClosedEventArgs e)         {             //清理现场             SqlDependency.Stop(_connstr);             conn.Close();             conn.Dispose();         }         } }

数据库与clr集成，编写写dll：SqlDependency.dll，sql server将在可编程性中加载此dll,

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67

using System; using System.IO; using System.Net; using System.Net.Sockets; using Microsoft.SqlServer.Server;   using System.Net.Sockets; namespace SqlDependency {     public class Program     {           [SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.Read)]         public static String WriteStringToFile(String FileFullPath, String Contend)         {               FileInfo Fi = new FileInfo(FileFullPath);             if (!Fi.Directory.Exists)             {                 Fi.Directory.Create();             }               using (StreamWriter rw = File.CreateText(FileFullPath))             {                   rw.WriteLine(Contend);                 TcpClient tcpClient = new TcpClient();                   try                 {                     if (tcpClient == null)                     {                         tcpClient = new TcpClient();                         tcpClient.ReceiveTimeout = 20000;                     }                     if (tcpClient.Connected == false)                     {                         System.Net.IPAddress address = System.Net.IPAddress.Parse(Contend);                         System.Net.IPHostEntry ipInfor = System.Net.Dns.GetHostByAddress(address);                         string hostName = ipInfor.HostName;                         IPEndPoint serverEndPoint = new IPEndPoint(IPAddress.Parse("127.0.0.1"), 10010);                         tcpClient.Connect(serverEndPoint);                         rw.Write(hostName);                     }                     rw.Write("连接成功，先发送指令");                     // Translate the passed message into ASCII and store it as a Byte array.                     Byte[] data = System.Text.Encoding.ASCII.GetBytes("new data!");                       NetworkStream stream = tcpClient.GetStream();                       // Send the message to the connected TcpServer.                     stream.Write(data, 0, data.Length);                     stream.Close();                                   }                 catch (Exception e)                 {                     rw.Write(e.Message);                 }                 tcpClient.Close();                  rw.Flush();                 rw.Close();                 return "";             }         }     } }

接下来，便开始配置sql server啦：

首先开启sql server的clr支持：

?

1 2 3 4 5

开启数据库CLR 支持 --exec sp_configure 'clr enabled', 1; --开始数据的验证 alter database dbname set TRUSTWORTHY on; RECONFIGURE

接着在sql server 2008中，新建查询窗口。加载刚才编写的dll SqlDependency.dll，并注册方法，然后写触发器，当表数据有变化时，触发函数。：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

use Temp;--数据库名 create assembly SqlDependency FROM 'D:SqlDependency.dll'--程序集名称和地址 WITH PERMISSION_SET = UNSAFE GO   --方法名写正确，为程序集中的方法名，注意参数个数 create function WriteStringToFile(@FileFullName as nvarchar(max),  @FileContend AS  nvarchar(max)) returns nvarchar(max) with returns null on null input external name [SqlDependency].[SqlDependency.Program].[WriteStringToFile] GO   --编写触发器，传递参数以及 CREATE TRIGGER [dbo].[UserTableChangedEvent] on [dbo].[Simple]      FOR INSERT, DELETE, UPDATE          AS      BEGIN      DECLARE @Contend AS VARCHAR(100)      DECLARE @FileName AS VARCHAR(MAX)          SET @FileName ='D:\MSG\'+CONVERT(varchar(12) , getdate(), 112 )+'\'+ convert(nvarchar(50), NEWID())+'.TXT'                    SET @Contend = '127.0.0.1';         Select dbo.WriteStringToFile(@FileName, @Contend)                          END    GO

注意，我的应用程序和 数据库在一台服务器上，所以地址都是127.0.0.1.可跟据实际填写正确地址。

再次在sql server中新建一个查询窗口，插入语句，进行测试吧。

如果过程中有问题，需要更新程序，方便地删除之上所创建的几个东东：

?

1 2 3

drop TRIGGER [dbo].[UserTableChangedEvent] drop function WriteStringToFile drop assembly SqlDependency

之后将尝试在web 结合 signal实现实时推送数据给web页面。等待下篇。