Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to use SqlCacheDependency?

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?

like image 681
Volodymyr Machula Avatar asked May 05 '13 10:05

Volodymyr Machula


People also ask

How does SQL Dependency work?

SqlDependency allows you to receive notifications when the original data in the database changes so that the cache can be refreshed. To set up a dependency, you need to associate a SqlDependency object to one or more SqlCommand objects. To receive notifications, you need to subscribe to the OnChange event.

What is SQL cache dependency?

Using SQL cache dependency, you could cache your product information and create a dependency on a database table or row change. When the data changes—and only then—the cache items based on that data are invalidated and removed from the cache.

Which is the following command to enable the cache dependency?

To enable a cache dependency on a particular database, run this command: aspnet_regsql.exe -S server -U user -P password -d database -ed This creates a new table, AspNet_SqlCacheTablesForChangeNotification, in the designated database.


1 Answers

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.

like image 122
Volodymyr Machula Avatar answered Oct 14 '22 14:10

Volodymyr Machula