I have already found solution.
At first check whether Service Broker is enabled for your table and enable it if needed:
SELECT name, is_broker_enabled FROM sys.databases WHERE name = '<databaseName>'
ALTER DATABASE <databaseName> SET enable_broker WITH ROLLBACK IMMEDIATE
Next create in SQL Server new role sql_dependency_role
, grant permissions to it and grant role to user:
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>'
After that add C# code for working with SqlCacheDependency
or SqlDependency
(mostly the same way).
I have changed my method and now it looks like this:
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;
}
Now it works fine.
Don't forget invoke SqlDependency.Start
method before creating SqlCacheDependency
or SqlDependency
and execute your command at the end.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…