如何使用的SqlCacheDependency？

问题描述

我需要实现的SqlCacheDependency一个表，这将取决于此查询：
SELECT昵称FROM DBO [用户]

I need to implement SqlCacheDependency for a table which will depend on this query:SELECT Nickname FROM dbo.[User].

我已经创建用于此目的的方法：

I have created a method for this purpose:

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 表）的名单，也没有记录。

But when I run my application it doesn't work.I checked the list of subscribers (sys.dm_qn_subscriptions table) and there was no records.

我调查了很多时间和已经尝试过多种解决方案，但他们不为我工作：

I investigated much time and have already tried various solutions but they doesn't work for me:

• 使用受信任的连接，并设置一些对公共角色的权限：

• use trusted connection and set some permissions for public role:

GRANT CREATE PROCEDURE公共结果
GRANT创建队列公共结果
授予create服务到公共结果
GRANT订阅查询通知公共结果
GRANT SELECT ON OBJECT :: DBO。[用户]公共结果
GRANT收到QueryNotificationErrorsQueue公共搜索

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

使用'山'登录连接

在web.config中添加配置到system.webServer：

add configuration to system.webServer in web.config:

< ;缓存> 结果
<的SqlCacheDependency启用=真正的> 结果
<数据库> 结果
<添加名称=TMPL的pollTime =5000的connectionStringName =RepositoryContext/> 结果
< /数据库> 结果
< /的SqlCacheDependency> 结果
< /缓存> 搜索

<caching>
<sqlCacheDependency enabled="true">
<databases>
<add name="Tmpl" pollTime="5000" connectionStringName="RepositoryContext"/>
</databases>
</sqlCacheDependency>
</caching>

把SqlDependency.Start（）到在Global.asax Application_Start事件

put the SqlDependency.Start() into the Global.asax Application_Start event

在SQL Server的不同实例上运行（SQL Server 2008 Express的，SQL Server 2008中）

run at different instances of sql server (SQL Server 2008 Express, SQL Server 2008)

不过，这并没有帮助。它仍然无法正常工作。

But It didn't help. It still doesn't work.

我如何工作的？

推荐答案

我已经找到了解决方案。

I have already found solution.

首先检查一下Service Broker的是否为你的表启用，并根据需要启用它：

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

接下来，创建SQL Server中的新角色 sql_dependency_role ，授予权限，并授予角色到用户：

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>'

与的SqlCacheDependency 或的SqlDependency （大多以同样的方式）。

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;
    }

现在它工作正常。

不要忘了的SqlCacheDependency 或的SqlDependency之前创建调用 SqlDependency.Start 方法，并在年底执行你的命令。

Don't forget invoke SqlDependency.Start method before creating SqlCacheDependency or SqlDependency and execute your command at the end.

这篇关于如何使用的SqlCacheDependency？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！