用触发器,当数据插入时,触发:CREATE TRIGGER customer_state ON Customers FOR INSERT,UPDATE AS UPDATE Customers SET cust_sate=Upper(cust_state) Where Customers.cust_id=inserted.cust_id;这样当更新某个数据,或者插入某个数据时,你都可以将记录的某个字段设置为某个值,然后你的.net程序,定时判断,如果字段发生变化,则重新读取数据。
我的例子是sql server 的。使用了扩展存储过程.-----------------------------存储过程调用COM+组件------------------------------- ALTER proc Proc_SendMsgToClient(@MsgContext varchar(4000)) /* 使用COM+发送局域网广播消息 @MsgContext varchar(4000) 消息内容 返回值: Send Message OK.表示发送成功 */ asDECLARE @object int DECLARE @hr int DECLARE @property varchar(255) DECLARE @return varchar(255) DECLARE @src varchar(255), @desc varchar(255) EXEC @hr = sp_OACreate 'SQLInterop.RMSMessageSendCOM', @object OUT IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURN END EXEC @hr = sp_OASetProperty @Object, 'MsgContext', @MsgContext IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURN END EXEC @hr = sp_OAMethod @object, 'SendMsgToClient', @return OUT IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURN END PRINT @returnEXEC @hr = sp_OADestroy @object IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURN END ------------C#写的COM+组件给数据库调用发送UDP广播通知同网段IP数据已经改变------- using System; using System.Runtime.InteropServices; using System.Reflection; using System.Runtime.CompilerServices; using System.Net; using System.Net.Sockets; using System.Text;[assembly: AssemblyTitle("Shining Res Msg Server")] [assembly: AssemblyDescription("Msg Server")] [assembly: AssemblyVersion("1.0.0.1")] [assembly: AssemblyDelaySign(false)] [assembly: AssemblyKeyFile("MsgSendKey.snk")] namespace SQLInterop { public interface IResMsg { string SendMsgToClient(); } [ClassInterface(ClassInterfaceType.AutoDual)] public class RMSMessageSendCOM : IResMsg { public string MsgContext = ""; public string SendMsgToClient() { //初始化一个Scoket实习,采用UDP传输 Socket sock = new Socket(AddressFamily.InterNetwork, SocketType.Dgram, ProtocolType.Udp); //初始化一个发送广播和指定端口的网络端口实例 IPEndPoint iep = new IPEndPoint(IPAddress.Broadcast, 6888); //设置该scoket实例的发送形式 sock.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.Broadcast, 1); IPHostEntry ipHost = Dns.Resolve(Dns.GetHostName()); IPAddress ipAddr = ipHost.AddressList[0]; //string msg = Encoding.GetEncoding("GBK").GetString(buffer,0,buffer.Length); byte[] buffer = Encoding.GetEncoding("GBK").GetBytes(MsgContext); sock.SendTo(buffer, iep); sock.Close(); return "Send Msg OK!"; } } } --------------在数据库表的触发器里调用存储过程,通知其他程序数据已经改变------------------ exec Proc_SendMsgToClient @MessageText
当然是timer了
ON Customers
FOR INSERT,UPDATE
AS
UPDATE Customers
SET cust_sate=Upper(cust_state)
Where Customers.cust_id=inserted.cust_id;这样当更新某个数据,或者插入某个数据时,你都可以将记录的某个字段设置为某个值,然后你的.net程序,定时判断,如果字段发生变化,则重新读取数据。
我的例子是sql server 的。使用了扩展存储过程.-----------------------------存储过程调用COM+组件-------------------------------
ALTER proc Proc_SendMsgToClient(@MsgContext varchar(4000))
/*
使用COM+发送局域网广播消息
@MsgContext varchar(4000) 消息内容
返回值: Send Message OK.表示发送成功
*/
asDECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)
DECLARE @src varchar(255), @desc varchar(255) EXEC @hr = sp_OACreate 'SQLInterop.RMSMessageSendCOM', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END EXEC @hr = sp_OASetProperty @Object, 'MsgContext', @MsgContext
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
EXEC @hr = sp_OAMethod @object, 'SendMsgToClient', @return OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
PRINT @returnEXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
------------C#写的COM+组件给数据库调用发送UDP广播通知同网段IP数据已经改变-------
using System;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Net;
using System.Net.Sockets;
using System.Text;[assembly: AssemblyTitle("Shining Res Msg Server")]
[assembly: AssemblyDescription("Msg Server")]
[assembly: AssemblyVersion("1.0.0.1")]
[assembly: AssemblyDelaySign(false)]
[assembly: AssemblyKeyFile("MsgSendKey.snk")]
namespace SQLInterop
{
public interface IResMsg
{
string SendMsgToClient();
} [ClassInterface(ClassInterfaceType.AutoDual)]
public class RMSMessageSendCOM : IResMsg
{
public string MsgContext = "";
public string SendMsgToClient()
{
//初始化一个Scoket实习,采用UDP传输
Socket sock = new Socket(AddressFamily.InterNetwork, SocketType.Dgram, ProtocolType.Udp);
//初始化一个发送广播和指定端口的网络端口实例
IPEndPoint iep = new IPEndPoint(IPAddress.Broadcast, 6888);
//设置该scoket实例的发送形式
sock.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.Broadcast, 1);
IPHostEntry ipHost = Dns.Resolve(Dns.GetHostName());
IPAddress ipAddr = ipHost.AddressList[0];
//string msg = Encoding.GetEncoding("GBK").GetString(buffer,0,buffer.Length);
byte[] buffer = Encoding.GetEncoding("GBK").GetBytes(MsgContext);
sock.SendTo(buffer, iep);
sock.Close();
return "Send Msg OK!";
}
}
}
--------------在数据库表的触发器里调用存储过程,通知其他程序数据已经改变------------------ exec Proc_SendMsgToClient @MessageText
用数据库缓存依赖吧,当启用了数据缓存依赖的表中数据一有变动,相应缓存项就失效,从而更新缓存,使用户能获得最新数据。