如何找出哪些的SqlDependency触发改变的功能？ [英] How to figure out which SQLDependency triggered change function?
问题描述
我探索与的SqlDependency 类查询通知。构建一个简单的工作的例子很容易，但我觉得我失去了一些东西。有一次，我踩过去一个简单的表/单依赖例子中，我在想的我怎么能弄清楚哪些依赖触发我的回调？的
我有一点麻烦解释，所以我包括下面的简单例子。当 AChange（）叫我不能看的依赖内部的SQL，和我没有提及相关的缓存对象。
那么，什么是一个男孩怎么办？
- 选项1 - 创建一个独特的功能，我想跟踪和硬$ C C缓存键（或相关信息）在回调$每个对象。这种感觉脏放大器;避免增加新的缓存项的posibility无需部署新的code - EWWW
- 选项2 - 使用依赖
编号属性和一个并行跟踪结构
我是不是失去了一些东西？这是在的SqlDependency 结构的缺陷？我已经我已经看了关于这个专题的20个不同的文章，所有的人似乎有同样的洞。建议？
code样品
公共类DependencyCache {
公共静态字符串cacheName =客户端1;
公共静态的MemoryCache的memcache =新的MemoryCache（cacheName）;
公共DependencyCache（）{
SqlDependency.Start（CONNSTRING）;
}
私人静态字符串GetSQL（）{
返回选择someString从dbo.TestTable;
}
公共无效DoTest（）{
如果（MEMCACHE [TEST_KEY]！= NULL）{
的Debug.WriteLine（在高速缓存中找到资源）;
返回;
}
Cache_GetData（）;
}
私人无效Cache_GetData（）{
SqlConnection的oConn;
SqlCommand的oCmd;
的SqlDependency ODEP;
SqlDataReader的口服补液盐;
名单＆LT;字符串＆GT;东西=新的名单，其中，串＆GT;（）;
CacheItemPolicy政策=新CacheItemPolicy（）;
SqlDependency.Start（CONNSTRING）;
使用（oConn =新的SqlConnection（CONNSTRING））{
使用（oCmd =新的SqlCommand（GetSQL（），oConn））{
ODEP =新的SqlDependency（oCmd）;
oConn.Open（）;
ORS = oCmd.ExecuteReader（）;
而（oRS.Read（））{
resources.Add（oRS.GetString（0））;
}
oDep.OnChange + =新OnChangeEventHandler（AChange）;
}
}
memCache.Set（TEST_KEY的东西，政策）;
}
私人无效AChange（对象发件人，SqlNotificationEventArgs E）{
弦乐味精=依赖更改\ n信息：{0}：源{1}类型：{2};
的Debug.WriteLine（的String.Format（味精，e.Info，e.Source，e.Type））;
//如果多个查询以此为回调我怎么能计算
//什么QUERY引发了变化？
//我无法弄清楚如何告诉多个依赖对象分开
（（的SqlDependency）发送方）.OnChange - = Cache_SqlDependency_OnChange;
Cache_GetData（）; //重载数据
}
}
首先：处理程序已经被建立之前执行该命令：
ODEP =新的SqlDependency（oCmd）;
oConn.Open（）;
oDep.OnChange + =新OnChangeEventHandler（AChange）;
ORS = oCmd.ExecuteReader（）;
而（oRS.Read（））{
resources.Add（oRS.GetString（0））;
}
否则，你有一个窗口时，该通知可能会丢失，您的回调永远不会调用。
现在关于你的问题：你应该使用单独的回调为每个查询。虽然这可能显得累赘，是使用一个lambda其实很简单。类似如下：
ODEP =新的SqlDependency（oCmd）;
oConn.Open（）;
oDep.OnChange + =（发件人，E）=＆GT;
{
弦乐味精=依赖更改\ n信息：{0}：源{1}类型：{2};
的Debug.WriteLine（的String.Format（味精，e.Info，e.Source，e.Type））;
//触发通知被捕获在上下文的命令：
//为oCmd
//
//现在，您可以调用句柄传递相关信息：
//
Reload_Data（oCmd，...）;
};
ORS = oCmd.ExecuteReader（）;
...
和记住的总是的检查通知源，信息和类型。否则，你运行纺广告令人生厌的风险时，您将收到通知的原因的等的不是数据的变化，如无效的查询。作为一个方面的评论我要补充的是一个良好的缓存设计不刷新无效的缓存，而只是无效的缓存项，并允许的下一个请求的实际取一个新的项目。随着你的'积极'的方法要刷新缓存的项目，即使在不需要的时候，刷新多次在访问之前等等等等，我离开了从例子中的错误处理和正确的线程同步（两者都需要）。
最后，看看 LinqtoCache 这的确pretty的多你想做什么，但对LINQ查询。
I'm exploring query notifications with the SQLDependency class. Building a simple working example is easy, but I feel like I'm missing something. Once I step past a simple one-table/one-dependency example I'm left wondering how can I figure out which dependency triggered my callback?
I'm having a bit of trouble explaining, so I included the simple example below. When AChange() is called I cannot look at the sql inside the dependency, and i don't have a reference to the associated cache object.
So what's a boy to do?
- Option 1 - create a distinct function for each object i want to track and hard code the cache-key (or relevant information) in the callback. This feels dirty & eliminates the posibility of adding new cache items without deploying new code--ewww.
- Option 2 - Use the Dependency
Idproperty and a parallel tracking structure
Am I just missing something? Is this a deficiency in the SQLDependency structure? I've I've looked at 20 different articles on the topic and all of them seem to have the same hole. Suggestions?
Code Sample
public class DependencyCache{
public static string cacheName = "Client1";
public static MemoryCache memCache = new MemoryCache(cacheName);
public DependencyCache() {
SqlDependency.Start(connString);
}
private static string GetSQL() {
return "select someString FROM dbo.TestTable";
}
public void DoTest() {
if (memCache["TEST_KEY"] != null ) {
Debug.WriteLine("resources found in cache");
return;
}
Cache_GetData();
}
private void Cache_GetData() {
SqlConnection oConn;
SqlCommand oCmd;
SqlDependency oDep;
SqlDataReader oRS;
List<string> stuff = new List<string>();
CacheItemPolicy policy = new CacheItemPolicy();
SqlDependency.Start(connString);
using (oConn = new SqlConnection(connString) ) {
using (oCmd = new SqlCommand(GetSQL(), oConn) ) {
oDep = new SqlDependency(oCmd);
oConn.Open();
oRS = oCmd.ExecuteReader();
while(oRS.Read() ) {
resources.Add( oRS.GetString(0) );
}
oDep.OnChange += new OnChangeEventHandler (AChange);
}
}
memCache.Set("TEST_KEY", stuff, policy);
}
private void AChange( object sender, SqlNotificationEventArgs e) {
string msg= "Dependency Change \nINFO: {0} : SOURCE {1} :TYPE: {2}";
Debug.WriteLine(String.Format(msg, e.Info, e.Source, e.Type));
// If multiple queries use this as a callback how can i figure
// out WHAT QUERY TRIGGERED the change?
// I can't figure out how to tell multiple dependency objects apart
((SqlDependency)sender).OnChange -= Cache_SqlDependency_OnChange;
Cache_GetData(); //reload data
}
}
First and foremost: the handler has to be set up before the command is executed:
oDep = new SqlDependency(oCmd);
oConn.Open();
oDep.OnChange += new OnChangeEventHandler (AChange);
oRS = oCmd.ExecuteReader();
while(oRS.Read() ) {
resources.Add( oRS.GetString(0) );
}
Otherwise you have a window when the notification may be lost and your callback never invoked.
Now about your question: you should use a separate callback for each query. While this may seem cumbersome, is actually trivial by using a lambda. Something like the following:
oDep = new SqlDependency(oCmd);
oConn.Open();
oDep.OnChange += (sender, e) =>
{
string msg = "Dependency Change \nINFO: {0} : SOURCE {1} :TYPE: {2}";
Debug.WriteLine(String.Format(msg, e.Info, e.Source, e.Type));
// The command that trigger the notification is captured in the context:
// is oCmd
//
// You can now call a handler passing in the relevant info:
//
Reload_Data(oCmd, ...);
};
oRS = oCmd.ExecuteReader();
...
And remember to always check the notification source, info and type. Otherwise you run the risk of spinning ad-nauseam when you are notified for reasons other than data change, like invalid query. As a side comment I would add that a good cache design does not refresh the cache on invalidation, but simply invalidates the cached item and lets the next request actually fetch a fresh item. With your 'proactive' approach you are refreshing cached items even when not needed, refresh multiple times before they are accessed etc etc. I left out from the example error handling and proper thread synchronization (both required).
Finally, have a look at LinqtoCache which does pretty much what you're trying to do, but for LINQ queries.
这篇关于如何找出哪些的SqlDependency触发改变的功能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
