Как использовать SqlCacheDependency?

Мне нужно реализовать 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 table) и не было записей.

Я исследовал много времени и уже пробовал различные решения, но они не работают для меня:

  • использовать доверенное соединение и установить некоторые разрешения для публичной роли:

    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

  • выполняется в разных экземплярах SQL Server (SQL Server 2008 Express, SQL Server 2008)

Но это не помогло. Это все еще не работает.

Как мне заставить работать?

Ответы

Ответ 1

Я уже нашел решение.

Сначала проверьте, включен ли сервис-брокер для вашей таблицы и включите его, если необходимо:

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 (в основном таким же образом).

Я изменил свой метод, и теперь он выглядит так:

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 перед созданием SqlCacheDependency или SqlDependency и выполнить команду в конце.