mysql表自动刷新代码_SqlDependency建立数据库表依赖实现自动刷新缓存

在项目开发中，我们通常会遇到这样的需求：缓存中的数据需要监听数据库表，当表的数据发生变化时，更新缓存中的数据。一般情况下，我们首先想到的可能是使用 timer，但是这种方式更新数据的实时性不够。这里我们提供另外一种方式：SqlDependency 与 SQL Server Broker Servicer 组合使用，来实现当依赖的表中的数据发生变化时，在代码中做相关的操作：刷新缓存或者其他你想要的任何处理。

在SQLServer2005中，SQL Server Service Broker 用于创建会话以交换消息。 消息交换在目标和发起方这两端之间进行。要启用数据库的该功能，需要执行如下命令：

ALTER DATABASE  DatabaseNameSET Enable_Broker;

查看命令执行是否成功，可以输入如下SQL命令以查看 Broker Service 状态：

SELECT is_broker_enabled FROM sys.databases WHERE name ='DatabaseName'

值为1，则表示启用成功。可能有的机器执行上面的代码无法启用，另外一种备用方式是执行如下命令：

ALTER DATABASE DatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE DatabaseName SET ENABLE_BROKER;

至于为何会出现上面这种情况无法，暂时我也没找到合理的解释，待完善。有知道的大虾可以帮忙完善，感激不尽。

SqlDependency 非常适用于使用缓存的情况，在这种情况下您的 ASP.NET 应用程序或中间层服务需要将某些信息缓存在内存中。SqlDependency 允许您在数据库中的原始数据发生更改时接收通知，以便刷新缓存。若要建立依赖项，需要将一个 SqlDependency 对象与一个或多个 SqlCommand 对象关联。要接收通知，需要订阅 OnChange 事件。当 sqlDependency 指定的依赖数据源发生变化时，通知应用程序，触发 onChange 事件。OnChange event is generated in the thread where the bind command object is excuted. command 指定的 commandText 的查询结果发生变化时触发 OnChange，且只触发一次，故需要在触发事件中再次建立依赖和绑定接受通知(会在示例代码中做说明)。

接着我们来补充说明一下 SqlDependency 绑定的 SqlCommand 的 commandText 的一些限制。

SQL 语句的限制：

不能用*,不能用派生表、行集函数、UNION运算符、子查询、外连接或自连接、TOP子句、DISTINCT 关键字、COUNT(*)聚合函数、AVG、MAX、MIN、STDEV、STDEVP、VAR 或 VARP 聚合函数、用户自定义的聚合函数、引用可空表达式的 SUM 函数、完全文本谓词 CONTAINS 或 FREETEXT、COMPUTE 或 COMPUTE BY 子句、INTO 子句。不能使用：临时表或表变量、其他数据库或服务器中的表或视图、所有其他视图或表值函数、任何系统表或视图、任何非确定性函数，包括评级和窗口函数

任何服务器全局变量、任何服务中介程序队列、同义词。表名之前必须加类似dbo数据库所有者这样的前缀。

例如SQL命令格式可以为：

SELECTSID,SNAME, SAGEFROMdbo.TAWHERESID='2001'ANDSNAME='zhangjie'orderbySAGE;

但是要注意：如果where 条件中有 datetime 类型的字段做筛选条件，则如下格式的SQL语句是错误的。

SELECTSID,SNAME, SAGEFROMdbo.TAWHERESdate='2011-9-4'ANDSNAME='zhangjie'orderbySAGE;

对于时间格式的字段，不能直接传入字符串，而是需要使用参数来设置。比如如下格式是正确的。只需为 sqlCommand 添加 sqlParamter即可。

SELECTSID,SNAME, SAGEFROMdbo.TAWHERESdate=@SdateANDSNAME='zhangjie'orderbySAGE;

要使用 sql server broker service ，我们需要在应用程序启动的时候建立与 sql server 的监听链接。执行代码：

SqlDependency.Start(connectionString);

程序退出的时候执行如下代码以释放这种监听。

SqlDependency.Stop(connectionString);

附上demo示例：

View Code

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Text;5 6 usingSystem.Data;7 usingSystem.Data.SqlClient;8 9 /*10 * 会触发 SqlDependency.OnChange 事件：command 指定的 commandText 的查询结果发生变化时触发，且只触发一次，故需要在触发事件中再次建立依赖和绑定接受通知11 * 建立依赖：将 SqlDependency 对象与 SqlCommand 对象绑定12 * 接收通知：Onchange事件指定13 *14 * 可以适用的情况：15 * 简单的sql语句：select col1,col2, convert(nvarchar(10),col3,121) as sdate from dbo.tableName where cond1 and cond2 order by col116 * 使用转换函数：select col1,col2, convert(nvarchar(10),col3,121) as sdate from dbo.tableName17 * 使用 inner join18 * 给表取别名19 *20 * support21 * sql server 2005 + dotnet framework 2.0 upper22 */23 24 namespaceTestApp25 {26 publicclassSqlDependencyData27 {28 ///29 ///数据库连接字符串30 ///31 privatestring_connectionString;32 33 ///34 ///SqlDependency 依赖 SQL35 ///36 privatestring_dependencySql;37 38 ///39 ///SqlDependency 依赖 SQL 中的参数40 ///41 privateIList_sqlParamList;42 43 ///44 ///SqlDependency 对象成员45 ///46 privateSqlDependency _dependency;47 48 ///49 ///对产生变化后的表做逻辑处理50 ///51 privateAction_outputChangeShow;52 53 publicSqlDependencyData(stringconnectionString,stringdependencySql, IListsqlParams, ActionoutputChangeShow)54 {55 this._connectionString=connectionString;56 this._dependencySql=dependencySql;57 this._sqlParamList=sqlParams;58 this._outputChangeShow=outputChangeShow;59 }60 61 ///62 ///绑定依赖，并做逻辑处理63 ///64 ///65 publicDataTable CheckChange( )66 {67 DataTable dt=newDataTable();68 try69 {70 using(SqlConnection conn=newSqlConnection(this._connectionString))71 {72 SqlCommand cmd=newSqlCommand(this._dependencySql, conn);73 if(this._sqlParamList!=null)74 {75 foreach(SqlParameter pinthis._sqlParamList)76 {77 cmd.Parameters.Add(p);78 }79 }80 conn.Open();81 82 //每次执行 CheckChange 方法都需要重新绑定数据库依赖83 cmd.Notification=null;84 this._dependency=newSqlDependency(cmd);85 //this._dependency = new SqlDependency();86 //this._dependency.AddCommandDependency(cmd);87 this._dependency.OnChange+=newOnChangeEventHandler(_dependency_OnChange);88 89 90 SqlDataReader sdr=cmd.ExecuteReader();91 //此处的 Parameters.Clear 必不可少，否则执行 foreach 添加 parameter 时会报错：另一个 SqlParameterCollection 中已包含 SqlParameter92 cmd.Parameters.Clear();93 dt.Load(sdr);94 }95 if(this._outputChangeShow!=null)96 {97 this._outputChangeShow(dt);98 }99 }100 catch(Exception ex)101 {102 Console.WriteLine("Exception:"+ex.Message);103 }104 returndt;105 }106 107 ///108 ///time:2011-09-03 11:50:45109 ///Notes:110 ///The OnChange event may be generated on a different thread from the thread that initiated command execution111 ///so we must execute command then we can bind onchange event.112 ///113 ///114 ///115 privatevoid_dependency_OnChange(objectsender, SqlNotificationEventArgs e)116 {117 //该事件执行一次之后，依赖失效，故取消订阅，并调用 CheckChange 方法重新订阅依赖118 SqlDependency dep=senderasSqlDependency;119 dep.OnChange-=newOnChangeEventHandler(_dependency_OnChange);120 this.CheckChange();121 }122 }123 }

调用：

View Code

classProgram

{//static string sqlDependency = "SELECT a.SID ,SNAME,SAGE,convert(nvarchar(10),sdate,121) dat FROM dbo.TA as a inner join dbo.TB as b on a.sid=b.sid where a.sdate=@sdate order by SID";staticstringsqlDependency="SELECT a.SID ,SNAME,SAGE,convert(nvarchar(10),sdate,121) dat FROM dbo.TA as a inner join dbo.TB as b on a.sid=b.sid order by SID";//static string sqlDependency = "SELECT TA.SID ,SNAME,SAGE, CID, CNAME FROM TA LEFT JOIN TB ON TA.SID=TB.SID";staticstringconnectionString="Data Source=127.0.0.1; Initial Catalog=scc; User ID= sa; Password=sa;";staticvoidMain(string[] args)

{//string sqlDependency = "SELECT TA.SID ,SNAME,SAGE, CID, CNAME FROM TA LEFT JOIN TB ON TA.SID=TB.SID";SqlDependency.Start(connectionString);

ListsqlParamList=newList();

SqlParameter param1=newSqlParameter("@sdate", SqlDbType.DateTime);

param1.Value=Convert.ToDateTime("2011-9-1");

sqlParamList.Add(param1);

SqlDependencyData data=newSqlDependencyData(connectionString, sqlDependency,null,delegate(DataTable dt)

{

Console.WriteLine("SID\tSNAME\tSAGE");//Console.WriteLine("SID\tSNAME\tSAGE\tCID\tCNAME");foreach(DataRow drindt.Rows)

{

Console.WriteLine(string.Format("{0}\t{1}\t{2}", dr[0], dr[1], dr[2]));//Console.WriteLine(string.Format("{0}\t{1}\t{2}\t{3}\t{4}", dr[0], dr[1], dr[2],dr[3],dr[4]));}

});//SqlDependencyData data = new SqlDependencyData(connectionString, sqlDependency, null, null);data.CheckChange( );

Console.ReadLine();

SqlDependency.Stop(connectionString);

}

}