如何确定哪个 SQLDependency 触发了更改函数?
我正在使用 SQLDependency
类探索查询通知.构建一个简单的工作示例很容易,但我觉得我错过了一些东西.一旦我跳过了一个简单的单表/单依赖示例,我就想知道如何确定哪个依赖触发了我的回调?
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?
我在解释时遇到了一些麻烦,所以我在下面提供了一个简单的示例.当调用 AChange()
时,我无法查看依赖项中的 sql,并且我没有对关联缓存对象的引用.
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.
那么男孩该怎么办?
- 选项 1 - 为我要跟踪的每个对象创建一个不同的函数,并在回调中硬编码缓存键(或相关信息).这感觉很脏&消除了在不部署新代码的情况下添加新缓存项的可能性——ewww.
- 选项 2 - 使用 Dependency
Id
属性和并行跟踪结构
- 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
Id
property and a parallel tracking structure
我只是错过了什么吗?这是 SQLDependency
结构的缺陷吗?我已经查看了 20 篇关于该主题的不同文章,所有文章似乎都有相同的漏洞.有什么建议吗?
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?
代码示例
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
INFO: {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.
现在关于您的问题:您应该为每个查询使用单独的回调.虽然这可能看起来很麻烦,但使用 lambda 实际上是微不足道的.类似于以下内容:
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
INFO: {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).
最后,看看 LinqtoCache 这几乎可以满足您的要求做,但对于 LINQ 查询.
Finally, have a look at LinqtoCache which does pretty much what you're trying to do, but for LINQ queries.
相关文章