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

小谈需求：

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

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

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

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

最后demo图:

现在我插入一条数据：

然后再看那个客户端：

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

客户端代码：

winform:

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

using System;
02 using System.Collections.Generic;
03 using System.ComponentModel;
04 using System.Data;
05 using System.Data.SqlClient;
06 using System.Drawing;
07 using System.Linq;
08 using System.Net;
09 using System.Net.Sockets;
10 using System.Text;
11 using System.Windows.Forms;
12 using System.Threading;
13  
14 namespace sql_dependency
15 {
16     public partial class Form1 : Form
17     {
18         public Form1()
19         {
20             InitializeComponent();
21         }
22  
23         System.Data.SqlClient.SqlConnection conn = null;
24         string _connstr = "Data Source = 10.6.154.251; database=Temp;user id=sa;pwd=MOcyou0543_";
25         System.Data.SqlClient.SqlCommand command = null;
26  
27         private void Form1_Load(object sender, EventArgs e)
28         {
29             conn = new System.Data.SqlClient.SqlConnection(_connstr);
30             command = conn.CreateCommand();
31             command.CommandText = "select [A],[B],[C] From [Temp].[dbo].[Simple]";
32             SqlDependency.Start(_connstr);//启动
33             Thread t = new Thread(new ThreadStart(GetData));
34             t.Start();
35         }
36  
37  
38         private void GetData()
39         {
40  
41             SetData();
42             IPAddress localAddr = IPAddress.Parse("127.0.0.1");
43             TcpListener tcplistener = new TcpListener(localAddr, 10010);
44             tcplistener.Start();
45             byte[] btServerReceive = new byte[2048];
46             string strServerReceive = string.Empty;
47             while (true)
48             {
49                 TcpClient tcp = tcplistener.AcceptTcpClient();
50                 Console.WriteLine("Connected!");
51                 NetworkStream ns = tcp.GetStream();
52                 int intReceiveLength = ns.Read(btServerReceive, 0, btServerReceive.Length);
53                 strServerReceive = Encoding.ASCII.GetString(btServerReceive, 0, intReceiveLength);
54  
55                 SetData();
56                 tcp.Close();
57             }
58  
59         }
60         private delegate void ChangeDataView();
61         private void SetData()
62         {
63             if (this.InvokeRequired)
64             {
65                 this.Invoke(new ChangeDataView(SetData));
66             }
67             else
68             {
69                 using (SqlDataAdapter adapter = new SqlDataAdapter(command)) //查询数据
70                 {
71                     System.Data.DataSet ds = new DataSet();
72                     adapter.Fill(ds, 0, 100, "Simple");
73                     dataGridView1.DataSource = ds.Tables["Simple"];
74                 }
75  
76             }
77         }
78  
79  
80  
81         private void Form1_Closed(object sender, FormClosedEventArgs e)
82         {
83             //清理现场
84             SqlDependency.Stop(_connstr);
85             conn.Close();
86             conn.Dispose();
87         }
88  
89  
90     }
91

}

 

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

01 using System;
02 using System.IO;
03 using System.Net;
04 using System.Net.Sockets;
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中新建一个查询窗口，插入语句，进行测试吧。

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

view source
print?
1 drop TRIGGER [dbo].[UserTableChangedEvent]
2 drop function WriteStringToFile
3 drop assembly SqlDependency

 

 
posted @ 2013-11-04 17:47  skynetfy  阅读(684)  评论(0)  编辑  收藏  举报