基于SQL2005查询通知的数据缓存

　　基于SQL2005查询通知的数据缓存依赖，具有比轮询方式更好的性能和效率，也少了相应的设置工作。但同时，这种方式使用场景也有一定的局限性，对查询语句有较高的要求。
1 启用查询通知
　　MSDN：http://msdn2.microsoft.com/zh-cn/library/ms172133(VS.80).aspx
1.1 SQL代码

-- 启用Service Broker
USE  master
ALTER   DATABASE  DatabaseName  SET  ENABLE_BROKER

-- 验证是否开启了Service Broker，返回1表示true，返回0表示false
-- SELECT DATABASEPROPERTYEX('DatabaseName', 'IsBrokerEnabled')

-- 赋予注册查询通知的权限
USE  DatabaseName
GRANT  SUBSCRIBE QUERY NOTIFICATIONS  TO  UserName

1.2 可能需要用到的SQL代码

-- 在msdb数据库中的QueryNotificationService服务上相Guest用户授予发送权限
USE  msdb
GRANT  SEND  ON  SERVICE:: [ http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService ]   TO  guest

-- 在数据库服务器上开启CLR
-- 在一个消息到达服务队列时,一个包含.NET代码的存储过程sp_DispatcherProc将使用一个队列来派发该消息,因此必须在该sql Server实例上启用CLR
USE  master
EXEC  sp_configure  ' clr enabled ' ,  1
RECONFIGURE

2 C＃程序
2.1 Global.asa.cs
　　对于ASP.NET应用，一般在此处启动和停止用于接收依赖项更改通知的侦听器。

protected   void  Application_Start( object  sender, EventArgs e)
... {
    System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
}

protected   void  Application_End( object  sender, EventArgs e)
... {
    System.Data.SqlClient.SqlDependency.Stop(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
}

2.2 缓存应用示例

/**/ /// <summary>
/// 基于SQL2005查询通知的缓存依赖
/// </summary>
/// <returns></returns>
public  DataView GetDataViewFromCache2005()
... {
    string Key = "Cache_ Production.Product";

    if (HttpRuntime.Cache[Key] == null)
    ...{
 //取数据
 SqlConnection conn = new SqlConnection();
 conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

 SqlCommand comm = new SqlCommand("SELECT ProductID, [Name], ProductNumber FROM Production.Product WHERE ProductID BETWEEN 1 AND 200 ORDER BY ProductID", conn); //单表查询

 //制定缓存策略
 SqlCacheDependency scd = new SqlCacheDependency(comm);

 SqlDataAdapter sda = new SqlDataAdapter(comm);
 DataSet ds = new DataSet();
 conn.Open();
 sda.Fill(ds);
 DataView dv = ds.Tables[0].DefaultView;
 conn.Close();

 //插入缓存
 HttpRuntime.Cache.Insert(Key, dv, scd);

 _Source = "Database";
 return dv;
    }
    else
    ...{
 //从缓存中取值
 _Source = "Cache";
 return (DataView)HttpRuntime.Cache[Key];
    }
}

3 特殊注意事项
3.1 查询通知只支持某些 Transact-SQL 语句。
　　MSDN：http://msdn2.microsoft.com/zh-cn/library/aewzkxxh(VS.80).aspx
3.1.1 首先，要支持通知，查询中不得包含下列内容：
 派生的表。
 行集函数。
 UNION 运算符。
 子查询。
 外联接或自联接。
 TOP 子句。
 DISTINCT 关键字。
 COUNT(*) 聚合函数。
 AVG、MAX、MIN、STDEV、STDEVP、VAR 或 VARP 聚合函数。
 用户定义的聚合函数。
 引用可空表达式的 SUM 函数。
 完全文本谓词 CONTAINS 或 FREETEXT。
 COMPUTE 或 COMPUTE BY 子句。
 聚合表达式（如果在选择列表中未指定 GROUP BY）。如果指定了 GROUP BY，选择列表中必须包含 COUNT_BIG(*) 表达式，并且不能指定 HAVING、CUBE 或 ROLLUP。
 INTO 子句。
 将阻止结果更改的条件（例如 WHERE 1=0）。
 FOR BROWSE（或在 SET NO_BROWSETABLE ON 的情况下运行）。
 READPAST 锁定提示。
3.1.2 其次，查询不得引用下列内容：
 临时表或表变量。
 其他数据库或服务器中的表或视图。
 所有其他视图或表值函数。
 任何系统表或视图。
 任何非确定性函数，包括评级和窗口函数。
 任何服务器全局变量
 任何服务中介程序队列。
 同义词。
3.1.3 最后，查询必须引用基表或视图。
3.2 数据库的兼容级别必须为2005
　　目前有些应用系统虽然采用了SQL2005服务器，但实际数据库可能因为这样那样的原因，仍强制在2000的兼容级别下运行，这时查询通知是失效的，我就为此浪费了一天的时间查找原因。