SqlCacheDependency with the Caching Application Block

Topics: Caching Application Block
Oct 15, 2009 at 5:28 PM

Hi all, I have scoured the web for relevant information and have so far found nothing, which in itself is mysterious to me.

My question is simple: Does anyone know how to implement a sql-dependency with the caching application block? What I'm looking for is more or less what you get with the SqlCacheDependency class in the System.Web.Cache. I would to effect cache invalidation when a change is made to a table in a sql2005 database.

Any ideas?

Oct 15, 2009 at 6:26 PM

After a little more reading, I'm now guessing that other people are just working with the SqlDependency class?

 

Oct 16, 2009 at 1:54 AM

You could implement your own ICacheItemExpiration.  Check this out - http://blogsprajeesh.blogspot.com/2008/04/creating-database-dependency-for.html

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Oct 16, 2009 at 11:22 AM

Thank you for the link Sarah.  Though the technique shown there seems quite awkward, considering that in most cases we will want to be reducing calls to the database. Maybe wrapping a SqlDependency expiration policy with the ICacheItemExpiration interface will be a better solution. Though it seems the only part of the interface that will really be relevant is the HasExpired method. Maybe I will post the code here once it's done.

 

Cheers

Joni

Oct 16, 2009 at 11:33 AM

Yes, that's just a sample so you'll easily get the idea. :)

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Oct 18, 2009 at 10:32 AM

Well, this is turning out to be more difficult than I thought. I don't suppose anyone has ever tried implementing SqlDependency with the Data Access Application Block? It seems that it has to be implanted deep within the current DAAB functionality, because starting a sql-dependency requires an open and active connection, and the DAAB manages all connecitons internally.

 

Ideas or examples? I'll post this separately as well.

 

Oct 18, 2009 at 12:31 PM
Edited Oct 18, 2009 at 12:32 PM

Never mind. For anyone whose interested, following is the code for my attempt at putting this together. This code has NOT YET BEEN TESTED.

 

 

public class SqlDependencyExpiration : ICacheItemExpiration
    {
        #region static members

        /// <summary>
        /// A list of connection-strings for which notifications have been activated.
        /// </summary>
        private static List<string> ActiveNotifcations = new List<string>(); 
        
        #endregion

        #region private members

        private SqlDependency dependency;
        private SqlCommand command;
        private bool wasCommandExecuted; 
        
        #endregion

        #region ctor

        /// <summary>
        /// 
        /// </summary>
        /// <param name="command">The command that SQL server should monitor changes to in order to determine when a change has
        /// occurred.
        /// The query encapsulated by the command must adhere to various limitations detailed here:
        /// http://msdn.microsoft.com/en-us/library/aewzkxxh.aspx
        /// </param>
        public SqlDependencyExpiration(SqlCommand command)
        {
            if (!ActiveNotifcations.Contains(command.Connection.ConnectionString))
            {
                //
                // Activate notifications for this connection
                //
                try
                {
                    command.Connection.Open();
                    SqlDependency.Start(command.Connection.ConnectionString);
                }
                finally
                {
                    command.Connection.Close();
                }

                ActiveNotifcations.Add(command.Connection.ConnectionString);
            }

            this.command = command;
            wasCommandExecuted = false;

            //Remove any existing notifications
            command.Notification = null;
            command.StatementCompleted += new System.Data.StatementCompletedEventHandler(command_StatementCompleted);

            dependency = new SqlDependency(command);
        }
 
        #endregion

        #region events

        /// <summary>
        /// Monitors the SqlCommand. If the command is not run when the this instance is applied as an expiration to a cache-item,
        /// the command will be run internally in the Initialize method.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void command_StatementCompleted(object sender, System.Data.StatementCompletedEventArgs e)
        {
            wasCommandExecuted = true;
            ((SqlCommand)sender).StatementCompleted -= command_StatementCompleted;
        } 
        
        #endregion

        #region ICacheItemExpiration Members

        public bool HasExpired()
        {
            return dependency.HasChanges;
        }

        public void Initialize(CacheItem owningCacheItem)
        {
            if (!wasCommandExecuted)
            {
                command.ExecuteNonQuery();
            }
        }

        public void Notify()
        {
        }

        #endregion
    }