如何使用SqlCacheDependency？

Question

我需要为依赖于以下查询的表实现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

• 使用“sa”登录连接
• 使用aspnet_regsql.exe (aspnet_regsql.exe -S localhost -E -ed -d TestTable -et -t User)
• 将配置添加到system.webServer中web.config中： <caching> <sqlCacheDependency enabled="true"> <databases> <add name="Tmpl" pollTime="5000" connectionStringName="RepositoryContext"/> </databases> </sqlCacheDependency> </caching>

• 将SqlDependency.Start()放入Global.asax Application_Start事件
• 在不同的server实例上运行(SQLServer2008Express，Server 2008)

但没什么用。还是不起作用。

我该怎么做呢？

Answer 1

我已经找到解决办法了。

首先，检查您的表是否启用了Service，并在需要时启用它：

SELECT name, is_broker_enabled FROM sys.databases WHERE name = '<databaseName>'

ALTER DATABASE <databaseName> SET enable_broker WITH ROLLBACK IMMEDIATE

接下来，在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>'

然后添加C#代码，用于使用SqlCacheDependency或SqlDependency (主要是相同的方式)。

我改变了我的方法，现在看起来是这样的：

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或SqlDependency之前调用SqlCacheDependency方法，并在最后执行命令。