Simple SqlCacheDependency

Question

Almost every tutorial I have read seems to incorrectly setup SqlCacheDependency. I believe they normally mix up the outdated polling method with the query notification method.

Here are two of many examples:

• Web Caching with SqlCacheDependency Simplified (non-microsoft)
• SqlCacheDependency Class (Microsoft)

---

Based on my testing, if you are using the broker (MSSQL 2015+) you don't need to make any .config changes nor do you need to make any SqlCacheDependencyAdmin calls (Don't need to define tables, etc).

I simplify just do this...

SqlDependency.Start(connString)
...
queryString = "SELECT ...";
cacheName = "SqlCache" + queryString.GetHashCode();
...
using (var connection = new SqlConnection(connString))
{
    connection.Open();
    var cmd = new SqlCommand(queryString, connection)
    {
        Notification = null, 
        NotificationAutoEnlist = true
    };

    var dependency = new SqlCacheDependency(cmd);

    SqlDataReader reader = cmd.ExecuteReader();
    try
    {
        while (reader.Read())
        {
            // Set the result you want to cache
            data = ...
        }
    }
    finally
    {
        reader.Close();
    }

    HostingEnvironment.Cache.Insert(cacheName, data, dependency);
}

(The code that checks if the cache is null or not is not included, as that's all just setup. I just want to show the setting of the cache)

This seems to work without the need to define which tables are involved in the query and make complicated triggers on each table. It just works.

More surprising to me is that the rules for making a query have notification :

• Creating a Query for Notification (Can't find documentation newer than 2008) don't seem to apply. I purpose to do a TOP in my SQL and it still works.

For a test, I have it run a query 1000 times involving a table named "Settings". Then I update a value in the table and repeat the query.

I watch the Profiler for any queries involving the word "Settings" and I see the query is executed just 1 time (to set the cache) and then the update statement occurs, and then the query is re-executed one more time (the cache was invalidated and the query ran again)

I am worried that in my 2-3 hours of struggling with the proper way to do this I am missing something and it really is this simple?

Can I really just put any query I want and it'll just work? I am looking for any pointers where I am doing something dangerous/non-standard or any small print that I am missing

Answer 1

var dependency = new SqlCacheDependency(cmd); when you write query like this you autiomatically define table name in it.Your connection already has db name. It is non explicit way to do same.

Explicit way to catch exception and to know what went wrong is this.

// Declare the SqlCacheDependency instance, SqlDep. 
        SqlCacheDependency SqlDep = null; 

        // Check the Cache for the SqlSource key. 
        // If it isn't there, create it with a dependency 
        // on a SQL Server table using the SqlCacheDependency class. 
        if (Cache["SqlSource"] == null) { 

            // Because of possible exceptions thrown when this 
            // code runs, use Try...Catch...Finally syntax. 
            try { 
                // Instantiate SqlDep using the SqlCacheDependency constructor. 
                SqlDep = new SqlCacheDependency("Northwind", "Categories"); 
            } 

            // Handle the DatabaseNotEnabledForNotificationException with 
            // a call to the SqlCacheDependencyAdmin.EnableNotifications method. 
            catch (DatabaseNotEnabledForNotificationException exDBDis) { 
                try { 
                    SqlCacheDependencyAdmin.EnableNotifications("Northwind"); 
                } 

                // If the database does not have permissions set for creating tables, 
                // the UnauthorizedAccessException is thrown. Handle it by redirecting 
                // to an error page. 
                catch (UnauthorizedAccessException exPerm) { 
                    Response.Redirect(".\\ErrorPage.htm"); 
                } 
            } 

            // Handle the TableNotEnabledForNotificationException with 
            // a call to the SqlCacheDependencyAdmin.EnableTableForNotifications method. 
            catch (TableNotEnabledForNotificationException exTabDis) { 
                try { 
                    SqlCacheDependencyAdmin.EnableTableForNotifications("Northwind", "Categories"); 
                } 

                // If a SqlException is thrown, redirect to an error page. 
                catch (SqlException exc) { 
                    Response.Redirect(".\\ErrorPage.htm"); 
                } 
            } 

            // If all the other code is successful, add MySource to the Cache 
            // with a dependency on SqlDep. If the Categories table changes, 
            // MySource will be removed from the Cache. Then generate a message 
            // that the data is newly created and added to the cache. 
            finally { 
                Cache.Insert("SqlSource", Source1, SqlDep); 
                CacheMsg.Text = "The data object was created explicitly."; 

            } 
        } 

        else { 
            CacheMsg.Text = "The data was retrieved from the Cache."; 
        }

Answer 2

As documented in https://learn.microsoft.com/en-us/dotnet/api/system.web.caching.sqlcachedependency?view=netframework-4.8 "Using a SqlCacheDependency object with SQL Server 2005 query notification does not require any explicit configuration."

So, the CMD has explicit table names in it, and ADO.net is issuing the correct Service Broker configuration commands for you. When the table is updated, SQL Server posts a Service Broker message saying the table has been updated. When ADO.net validates the CMD it checks the explicit tables in the broker for updates.

This is why the SQlCacheDependency associated CMD must use explicit tables.