如何使用sqlachedependency

本文关键字：sqlachedependency 何使用 | 更新日期: 2023-09-27 18:12:15

我需要实现一个表的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' login连接

aspnet_regsql.exe -S localhost -E -ed -d TestTable -et -t User

添加配置到系统。web.config:
<caching>
<sqlCacheDependency enabled="true">
<databases>
<add name="Tmpl" pollTime="5000" connectionStringName="RepositoryContext"/>
</databases>
</sqlCacheDependency>
</caching>
将SqlDependency.Start()放入Global. sql中。asax Application_Start事件
在不同的sql server实例下运行(sql server 2008 Express, sql server 2008)

但它没有帮助。还是不行

我如何使它工作?

如何使用sqlachedependency

我已经找到解决办法了。

首先检查是否为您的表启用了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;
    }

现在运行正常。

不要忘记在创建SqlCacheDependency或SqlDependency之前调用SqlDependency.Start方法，并在最后执行命令。