0

here i go through a article on sql cache dependency from this site http://www.dotnetcurry.com/showarticle.aspx?ID=263. specially a routine was called which cache the table data first time and when underlying table data will be changed then routine will load data again from table. i just do not understand when data change in db then how automatically cache will be null or invalidated. so here i am pasting that routine where i just do not understand how cache data is getting invalidated when data change occur in db?

just see the routine and discuss this point :how cache data is getting invalidated when data change occur in db

public static class MyExtensions
{
    public static List<T> LinqCache<T>(this Table<T> query) where T : class
    {
        string tableName = query.Context.Mapping.GetTable(typeof(T)).TableName;
        List<T> result = HttpContext.Current.Cache[tableName] as List<T>;

        if (result == null)
        {
            using (SqlConnection cn = new SqlConnection(query.Context.Connection.ConnectionString))
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand(query.Context.GetCommand(query).CommandText, cn);
                cmd.Notification = null;
                cmd.NotificationAutoEnlist = true;
                                        SqlCacheDependencyAdmin.EnableNotifications(query.Context.Connection.ConnectionString);
                if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(query.Context.Connection.ConnectionString).Contains(tableName))
                {
                        SqlCacheDependencyAdmin.EnableTableForNotifications(query.Context.Connection.ConnectionString, tableName);
                }                   

                SqlCacheDependency dependency = new SqlCacheDependency(cmd);
                cmd.ExecuteNonQuery();

                result = query.ToList();
                HttpContext.Current.Cache.Insert(tableName, result, dependency);
            }
        }
        return result;
    }
}

who will create this table AspNet_SqlCacheTablesForChangeNotification ? what is the importance of this table AspNet_SqlCacheTablesForChangeNotification ? suppose data change in the my employee table then what will happen in this table AspNet_SqlCacheTablesForChangeNotification

please discuss all my points as a result my all doubts will be clear about how sql dependency works and how automatically cache will be invalidated ?

thanks

Masoud Mohammadi
  • 1,721
  • 1
  • 23
  • 41
Thomas
  • 33,544
  • 126
  • 357
  • 626

1 Answers1

2

Since SQL Server 2005 the database implements notification mechanism to inform applications about changes - http://en.wikipedia.org/wiki/SQL_Server_Notification_Services

Before that they simply polled the database for changes periodically.

More information here - http://www.asp.net/web-forms/tutorials/data-access/caching-data/using-sql-cache-dependencies-cs

Stilgar
  • 22,354
  • 14
  • 64
  • 101
  • probably u do not understand what i am trying to know. i like to know how sql server push data to client? how cache will be invalidated? who will invalidate the cache and how ? – Thomas Aug 26 '14 at 19:08
  • i like to know what is role of this table AspNet_SqlCacheTablesForChangeNotification ? – Thomas Aug 26 '14 at 19:11
  • SQL Server has a feature to push data to the client. This is what Notification Services does. The table you are asking about is for databases which do not support Notification Services. Changes are put in this table and the client periodically checks to see if there were changes. I am adding a link to an article explaining this in detail – Stilgar Aug 27 '14 at 08:16