Setting up a SqlCacheDepdency

The great thing about setting up a SqlCacheDepdency is that once it is working, you never really have to do anything with it. In my case, it has been roughly 5 years since I set one up...the downfall? I can't remember all the little steps it takes to set it up. And it is not easy. It has to be precisely setup....precisely!

Today, I wanted to beat my PC into oblivion. Seeing that was not really going to help, I had to research...and research. Here are the steps. Though not clear because when you try 1 million things, you don't really know what was needed and what was not...

Details, asp.net, SQLServer 2008R2

1. NEEDED, Enable Broker
ALTER DATABASE myDbName SET ENABLE_BROKER with rollback immediate;

2. [Not SQL2008R2] Needed for lesser versions...I think. I will add this just in case you need it:

GRANT CREATE PROCEDURE TO "NT AUTHORITY\NETWORK SERVICE"
GRANT CREATE QUEUE TO "NT AUTHORITY\NETWORK SERVICE"
GRANT CREATE SERVICE TO "NT AUTHORITY\NETWORK SERVICE"
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "NT AUTHORITY\NETWORK SERVICE"
GRANT SELECT ON OBJECT::dbo.TableName TO "NT AUTHORITY\NETWORK SERVICE"
GRANT RECEIVE ON QueryNotificationErrorsQueue TO "NT AUTHORITY\NETWORK SERVICE"


3. Setup Global.asax- Application_Start:

System.Data.SqlClient.SqlDependency.Start(connectionString);

4. Setup Cache code:

pop-uptext

ConfigurationInfo configInfo = null;
        System.Web.Caching.SqlCacheDependency dependency = null;
        if (Cache["Config"] == null)
        {
            try
            {
                
                 cacheConfigInfo = GetConfiguration(out dependency);
            }
            catch {}
            finally
            {
                Cache.Insert("Config", cacheConfigInfo, dependency);
         
            } 
        }
....

ConfigurationInfo configInfo = null;
        System.Web.Caching.SqlCacheDependency dependency = null;

        if (Cache["Config"] == null)
        {
            try
            {
                
                 cacheConfigInfo = GetConfiguration(out dependency);
            }
            catch {}
            finally
            {
                Cache.Insert("Config", cacheConfigInfo, dependency);
         
            } 
        }
....

5. VERY Important: You must attach the SqlCacheDependency to the SqlCommand before you execute the command:

It must be a simple query (no crazy aggregates), it can be in a stored procedure.
If it is in a stored proc qualify the db name like this :dbo.MyTableName

pop-uptext

public ConfigurationInfo GetConfiguration(out SqlCacheDependency dependency)
        {
            ConfigurationInfo configInfo = null;
          
            
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = SQLHelper.DbConnectionString;
            SqlCommand command = new SqlCommand("up_GetConfig", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            dependency = new SqlCacheDependency(command);
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.DbConnectionString, "up_GetConfig"))
            {
                if (reader.Read())
                {
                    configInfo = new ConfigurationInfo();
                    configInfo.Id  = reader["ID"] == DBNull.Value ? 0 : (int)reader["ID"];
        ....
                    
                }
            }
            return configInfo;
        }

public ConfigurationInfo GetConfiguration(out SqlCacheDependency dependency)
        {
            ConfigurationInfo configInfo = null;
          
            
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = SQLHelper.DbConnectionString;
            SqlCommand command = new SqlCommand("up_GetConfig", conn);

            command.CommandType = System.Data.CommandType.StoredProcedure;

            dependency = new SqlCacheDependency(command);

            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.DbConnectionString, "up_GetConfig"))
            {
                if (reader.Read())
                {
                    configInfo = new ConfigurationInfo();

                    configInfo.Id  = reader["ID"] == DBNull.Value ? 0 : (int)reader["ID"];
        ....
                    
                }
            }

            return configInfo;
        }

I hope that helps....I am sure in another 5 years I might need to reference this again!