SQLDepedency Caching in ASP.NET

The following code allows you to use the SQL Dependency caching ASP.NET.

To be able to use SQL Dependency caching, the following queries must be executed in the SQL Server (2005 and above)

ALTER DATABASE SET ENABLE_BROKER

GO

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO

GO

The above steps enable broker in the SQL Sever and subscribe the AppPool account for query notifications (new concept from SQL Server 2005 and above)

Now its time for coding to achieve the SQL Dependency Caching.

            connString = ConfigurationManager.ConnectionStrings["AppConnectionString1"].ConnectionString;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand command = new SqlCommand("select dbo.Test.ColA,dbo.Test.ColB from dbo.Test", conn);
            SqlCacheDependency cacheDependency = new SqlCacheDependency(command);
            SqlDependency.Start(connString);
            if (Cache["Test"] == null)
            {
                Cache.Add("Test", Common.ConvertReaderToTest(command.ExecuteReader()), cacheDependency, Cache.NoAbsoluteExpiration,
Cache.NoSlidingExpiration, CacheItemPriority.Normal, new CacheItemRemovedCallback(this.RemovedCallback));
            }

public void RemovedCallback(string k, object v, CacheItemRemovedReason r)
        {
            if (r == CacheItemRemovedReason.DependencyChanged)
            {
                SqlConnection conn = new SqlConnection(connString);
                conn.Open();
                SqlCommand command = new SqlCommand("select dbo.Test.ColA,dbo.Test.ColB from dbo.Test", conn);
                SqlCacheDependency cacheDependency = new SqlCacheDependency(command);
                Cache.Add("Test", Common.ConvertReaderToTest(command.ExecuteReader()), cacheDependency, Cache.NoAbsoluteExpiration,
            Cache.NoSlidingExpiration, CacheItemPriority.Normal, new CacheItemRemovedCallback(this.RemovedCallback));
                conn.Close();
            }

        }

Please note that, for SQL Cache dependency to work; one has to use fully qualified name for column names and table names.

select dbo.Test.ColA,dbo.Test.ColB from dbo.Test