How to use SqlCacheDependency?

Question

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

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

• use 'sa' login for connection

• use aspnet_regsql.exe (aspnet_regsql.exe -S localhost -E -ed -d TestTable -et -t User)

• add configuration to system.webServer in web.config:

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

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

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

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

How do I make it work?

Solution

I have already found solution.

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

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

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

Now it works fine.

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

Comments

[Jared Phelps]

Is it necessary to keep the connection open as you've done here or can you close/dispose it?

[Jared Phelps]

Answering my own question above - it is necessary to keep this connection open so the service broker can send those notifications. Which makes this a bit harder to design around if you have many cache entries that need to be refreshed.