SQL Dependency with Cache (server side) For MVC and ASP.NET

Step 1 :

ALTER DATABASE [XYZ] SET ENABLE_BROKER with rollback immediate;
ALTER DATABASE [XYZ] SET TRUSTWORTHY ON;

Step 2 :


   
     
       
         
       
     
   

Step 3 :

public DataSet GetReaderByCmdCaching(string command, int keyID)
        {
            object classlock = new object();
            this.GetConnection();
            System.Data.DataSet returnValue = new DataSet();
            string strKey = "readerdata" + keyID;
            string strKeyNavigation = "readerdatanavigation" + keyID;
            if (HttpContext.Current.Cache[strKey] == null)
            {
                lock (classlock)
                {
                    System.Data.SqlClient.SqlDependency.Start(this.connectionString);
                    this.sqlCommand.CommandText = command;
                    SqlCacheDependency dependency = new SqlCacheDependency("CacheDatabaseConnection", "TableName");
                    SqlDataAdapter da = new SqlDataAdapter(this.sqlCommand);
                    da.Fill(returnValue);
                    HttpContext.Current.Cache.Insert(strKey, returnValue, dependency);
                }
            }
            else
            {
                returnValue = (System.Data.DataSet)HttpContext.Current.Cache[strKey];
            }
           
            return returnValue;
        }

Step 4 :

DataSet dsTemp = GetReaderByCmdCaching("Name of SP or Query", UniqueID);

Step 5 :

You are done, whenever any data change in Table it generate the Cache :) Happy Coding.