我需要为表实现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表),没有记录。

我花了很多时间并且已经尝试了各种解决方案,但它们对我不起作用:
  • 使用受信任的连接并为公共(public)角色设置一些权限:
    GRANT CREATE PROCEDURE TO publicGRANT CREATE QUEUE TO publicGRANT CREATE SERVICE TO publicGRANT SUBSCRIBE QUERY NOTIFICATIONS TO publicGRANT SELECT ON OBJECT::dbo.[User] TO publicGRANT RECEIVE ON QueryNotificationErrorsQueue TO public
  • 使用“sa”登录名进行连接
  • 使用aspnet_regsql.exe(aspnet_regsql.exe -S localhost -E -ed -d TestTable -et -t User)
  • 在web.config中向system.webServer添加配置:
    <caching> <sqlCacheDependency enabled="true"> <databases> <add name="Tmpl" pollTime="5000" connectionStringName="RepositoryContext"/> </databases> </sqlCacheDependency></caching>
  • 将SqlDependency.Start()放入Global.asax Application_Start事件
  • 在SQL Server的不同实例(SQL Server 2008 Express,SQL Server 2008)上运行

  • 但这没有帮助。它仍然不起作用。

    我该如何运作?

    最佳答案

    我已经找到解决方案。

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

    之后,添加用于处理SqlCacheDependencySqlDependency的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.StartSqlCacheDependency之前调用SqlDependency方法并在最后执行命令。

    关于c# - 如何使用SqlCacheDependency?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/16383513/

    10-09 03:40