我需要为表实现SqlCacheDependency,该表将依赖于此查询:SELECT Nickname FROM dbo.[User]
。
我为此创建了一个方法:
private IEnumerable<string> GetNicknamesFromCache()
{
const String cacheValueName = "Nicknames";
var result = HttpRuntime.Cache.Get(cacheValueName) as List<String>;
if (result == null)
{
result = _repository.GetAllNicknames();
var connectionString = ConfigurationManager.ConnectionStrings["RepositoryContext"].ConnectionString;
var sqlConnection = new SqlConnection(connectionString);
var sqlCommand = new SqlCommand("SELECT Nickname FROM dbo.[User]", sqlConnection);
var sqlDependency = new SqlCacheDependency(sqlCommand);
HttpRuntime.Cache.Insert(cacheValueName, result, sqlDependency);
}
return result;
}
但是,当我运行我的应用程序时,它不起作用。
我检查了订户列表(
sys.dm_qn_subscriptions
表),没有记录。我花了很多时间并且已经尝试了各种解决方案,但它们对我不起作用:
GRANT CREATE PROCEDURE TO public
GRANT CREATE QUEUE TO public
GRANT CREATE SERVICE TO public
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO public
GRANT SELECT ON OBJECT::dbo.[User] TO public
GRANT RECEIVE ON QueryNotificationErrorsQueue TO public
aspnet_regsql.exe -S localhost -E -ed -d TestTable -et -t User
)<caching>
<sqlCacheDependency enabled="true">
<databases>
<add name="Tmpl" pollTime="5000" connectionStringName="RepositoryContext"/>
</databases>
</sqlCacheDependency>
</caching>
但这没有帮助。它仍然不起作用。
我该如何运作?
最佳答案
我已经找到解决方案。
首先,检查是否为您的表启用了Service Broker,并在需要时启用它:
SELECT name, is_broker_enabled FROM sys.databases WHERE name = '<databaseName>'
ALTER DATABASE <databaseName> SET enable_broker WITH ROLLBACK IMMEDIATE
接下来在SQL Server中创建新角色
sql_dependency_role
,向其授予权限并将角色授予用户:EXEC sp_addrole 'sql_dependency_role'
GRANT CREATE PROCEDURE to sql_dependency_role
GRANT CREATE QUEUE to sql_dependency_role
GRANT CREATE SERVICE to sql_dependency_role
GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to sql_dependency_role
GRANT VIEW DEFINITION TO sql_dependency_role
GRANT SELECT to sql_dependency_role
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO sql_dependency_role
GRANT RECEIVE ON QueryNotificationErrorsQueue TO sql_dependency_role
EXEC sp_addrolemember 'sql_dependency_role', '<userName>'
之后,添加用于处理
SqlCacheDependency
或SqlDependency
的C#代码(大致相同)。我已经更改了方法,现在看起来像这样:
private IEnumerable<string> GetNicknamesFromCache()
{
const String cacheValueName = "Nicknames";
var result = HttpRuntime.Cache.Get(cacheValueName) as List<String>;
if (result == null)
{
result = _repository.GetAllNicknames();
using (var connection = new SqlConnection(_config.ConnectionString))
{
connection.Open();
SqlDependency.Start(_config.ConnectionString);
var command = new SqlCommand("SELECT Nickname FROM dbo.[User]", connection);
var dependency = new SqlCacheDependency(command);
HttpRuntime.Cache.Insert(cacheValueName, result, dependency);
command.ExecuteNonQuery();
}
}
return result;
}
现在工作正常。
不要忘记在创建
SqlDependency.Start
或SqlCacheDependency
之前调用SqlDependency
方法并在最后执行命令。关于c# - 如何使用SqlCacheDependency?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/16383513/