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!

Blogger SyntaxHighlight

I have been trying to use SyntaxHighlighter for months now and it has been quite cumbersome. I don't post every week so every time I have to post code, I have to go back to remember the steps to post formatted code.

Plus I thought I had added the calls to my blogger template but now they are missing? very strange. maybe for some odd reason i decided to take out something i wanted to use from my template....OR MAYBE NOT! i don't know if blogger decided to remove the calls or if they did an update that took out my calls but they are not there...

Overall, I pretty much regret selecting Blogger as my blogging tool. If I could go back in time and start with a blogging tool that was more programmer friendly, I sooooo wish I would have. But now I am stuck in the Blogger trap....

anyhow, since SyntaxHighlighter is hard for me to remember how to use, i am trying out a new solution. http://www.syntaxhighlight.in/. This has a nice option to pop-up the code or display in text. I will see in time if this option is less time consuming to use..

Jquery Tab does not work in ASP UpdatePanel

I have been using Jquery Tabs a lot lately and they usually work just fine. An issue here and there. Today I had an issue when trying to put the tabs inside an ASP Update Panel. The code did not seem to work. I found this easy fix buried in some forum....so I thought I would put it in my blog to be more accessible...and it worked really...

pop-uptext

 <script type="text/javascript">
       function pageLoad(sender, args) {
            if (args.get_isPartialLoad()) {
               //put any javascript code
                $('#myTabs').tabs();
            }
        }
</script>

 <script type="text/javascript">
       function pageLoad(sender, args) {
            if (args.get_isPartialLoad()) {
               //put any javascript code
                $('#myTabs').tabs();
            }
        }
</script>