无法使依赖于 sql 查询的缓存失效
我试图根据数据库行的更改使缓存无效。我在使用相同的数据库和表使用 linq 做同样的事情时取得了成功，但我无法以基本方式做到这一点：
这是我编写的代码，请帮助：
public DataSet GetData(string sqlCmd)
{
string connectionStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
string cacheKey = "test123";
DataSet ds = (DataSet)HttpRuntime.Cache.Get(cacheKey);
if (ds != null)
{
return ds;
}
SqlCacheDependency sqldep = null;
SqlConnection conn = new SqlConnection(connectionStr);
SqlCommand cmd = new SqlCommand(sqlCmd, conn);
cmd.Notification = null;
cmd.NotificationAutoEnlist = true;
SqlDataAdapter sqlAd = new SqlDataAdapter(cmd);
ds = new DataSet();
sqlAd.Fill(ds);
System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(connectionStr);
string NotificationTable = "TblMetaData";
if (!System.Web.Caching.SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connectionStr).Contains(NotificationTable))
System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(connectionStr, NotificationTable);
sqldep = new SqlCacheDependency(cmd);
HttpRuntime.Cache.Insert(cacheKey, ds, sqldep);
return ds;
}
我正在使用 Sql Server 2005 并使用可与命令通知一起使用的完全限定查询。
我在 Linq 中的其他代码可以完美地使用相同的数据库和表，因此没有必要设置 Service Broker 或其他权限。请帮助我解决这个问题。
I am trying to invalidate the cache based on the change in the rows of the database. I have achieved success while doing the same thing with linq with the same database and table but Iam not able to do it in the basic way:
Here is the code that I have written, please help:
public DataSet GetData(string sqlCmd)
{
string connectionStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
string cacheKey = "test123";
DataSet ds = (DataSet)HttpRuntime.Cache.Get(cacheKey);
if (ds != null)
{
return ds;
}
SqlCacheDependency sqldep = null;
SqlConnection conn = new SqlConnection(connectionStr);
SqlCommand cmd = new SqlCommand(sqlCmd, conn);
cmd.Notification = null;
cmd.NotificationAutoEnlist = true;
SqlDataAdapter sqlAd = new SqlDataAdapter(cmd);
ds = new DataSet();
sqlAd.Fill(ds);
System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(connectionStr);
string NotificationTable = "TblMetaData";
if (!System.Web.Caching.SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connectionStr).Contains(NotificationTable))
System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(connectionStr, NotificationTable);
sqldep = new SqlCacheDependency(cmd);
HttpRuntime.Cache.Insert(cacheKey, ds, sqldep);
return ds;
}
I am using Sql Server 2005 and using the fully qualified queries that can work with command notification.
My other code in Linq is perfectly working with the same database and table, so there is no point about the Service Broker or other permissions not set. PLease help me to get rid of this problem.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
本文提供了一些故障排除步骤神秘通知，并附有说明事物如何运作有助于理解事物为何出现故障。您应该验证每个部分是否正常工作：
sys.dm_qn_subscriptions在不相关的注释中，您可以直接使用 LINQ-to-SQL 和查询通知： LinqToCache。
This article has some troubleshooting steps The Mysterious Notification, along with an explanation how things work that helps where trying to understand why something is broken. Your should validate that each piece of the machinery works:
sys.dm_qn_subscriptionsOn an unrelated note, you can use LINQ-to-SQL and Query Notifications directly: LinqToCache.
最好的做法是：查看 sql 日志，如果通知发生任何问题，日志将显示！
错误日志位于
Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG 和 ERRORLOG.n
在我的例子中，问题是由于数据库恢复而发生的，为了解决，日志向我显示了问题，然后，我执行
The best thing to do is: Take a look on the sql log, if any problem with Notifications is happening, log will show!
the error log is located at
Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n
In my case, The problem occurred because of database restoration, to solve, log showed me the problem, then, I execute