Hi everyone, I think that I'm not using my SQL Caching Dependency
approach correctly. I use .NET 2.0 and SQL Server 2000. I'm
unexpectedly getting too many events fired for my
CacheItemRemovedCallbacks and I'd like your inputs. I'll provide a
summary of the issue first then some detail that I hope might raise
some red flags for people.

First some summary context:

1. My SQL Caching is at least working - that's validated - I used
the .NET 2.0 Caching strategy with SQL Server 2000
2. When I run SQL Server Trace, I unexpectedly see that the same query
is run over and over. For example, I may see this in my trace:
  SELECT * FROM CountryCodes ORDER BY ShortName

Perhaps 100, 200 or more times. I don't have a clue why and I would
expect the query to get called one time only, since the Cache entry is
populated using the result of this query. The same thing happens with
all items that I'm caching that have a dependency with a cache item
removed callback. It also happens "later on" at some interval decided
upon by the .NET framework. I have CacheItemRemovedCallbacks defined
whenever a cached item is removed. The reason is usually
CacheItemRemovedReason.DependencyChanged.

Besides that I'm not sure if there's any other meaningful information
that I can provide other than code snippets. They follow, I really
hope someone can help me figure out what's going on here...thanks!

Relevant web.confg stuff. Note that I've added the <cache ... /> stuff
only today to debug based on some ideas I saw thrown out there on the
web. I've renamed the database <add ... /> elements below for security
reasons:

Web.config:
     <caching>
       <cache disableMemoryCollection="true" disableExpiration="true"
percentagePhysicalMemoryUsedLimit="100"/>
       <sqlCacheDependency enabled="true">
         <databases>
           <add name="db1" connectionStringName="connStr1"
pollTime="60000"/>
           <add name="db2" connectionStringName="connStr2"
pollTime="60000"/>
           <add name="db2" connectionStringName="connStr3"
pollTime="60000"/>
           <add name="db4" connectionStringName="connStr4"
pollTime="60000"/>
           <add name="db5" connectionStringName="connStr5"
pollTime="60000"/>
         </databases>
       </sqlCacheDependency>
     </caching>

Relevant Global.asax stuff. I use the Application_Start(...) event to
initial the caching. This gets called of course:
Global.asax code:

       protected void Application_Start(object sender, EventArgs e)
       {
           // Code to initial all caches...snippet (for all other
db's similar code is utilized)
           SqlCacheDependencyAdmin.EnableNotifications(
               ConfigurationManager.ConnectionStrings[
               "connStr1"].ConnectionString);

           SqlCacheDependencyAdmin.EnableTableForNotifications(
               ConfigurationManager.ConnectionStrings[
               "connStr1"].ConnectionString,
               new string[] { "table1", "table2", "table3"});


com.Web.Caching.CacheUtilities.Instance().InitializeCaches();
       }

com.Web.Caching.CacheUtiilties is a class that I've created. I have an
example snippet from it relevant to country codes which are cached:

       public static CacheItemRemovedCallback onCountryCodeRemove =
null;
       public static CacheItemRemovedCallback
onLocalizedCountryCodeRemove = null;

       public const string CACHE_KEY_COUNTRY_CODES =
"App:CountryCodes";

       public void InitializeCaches()
       {
           CacheUtilities.onCountryCodeRemove =
           new
CacheItemRemovedCallback(this.RemovedCountryCodeCallback);

           this.CacheCountryCodes();
       }

The code snippet above is called from my global.asax. I define my
callbacks and then explicitly initialize the cached data.

The next code snippet is the callback that occurs when the .NET
framework has invalidated the cache. It calls the same Cache method:

       public void RemovedCountryCodeCallback(string cacheKey,
           object cacheObject, CacheItemRemovedReason reason)
       {
           this.CacheCountryCodes();
       }

       private void CacheCountryCodes()
       {
           this.CacheDBCodes(CACHE_KEY_COUNTRY_CODES, "connStr1",
               "SELECT * FROM CountryCodes ORDER BY ShortName",
"CountryCodes",
               "db1", CacheUtilities.onCountryCodeRemove);
       }

       private void CacheDBCodes(string cacheKey, string
connectionStringName,
           string sql, string entity, string sqlCacheDepName,
CacheItemRemovedCallback callback)
       {
           if (CacheInstance[cacheKey] == null)
           {
               SqlConnection dbConn = null;
               SqlDataAdapter selectStatement;
               DataSet cachedTables = new DataSet();
               try
               {
                   dbConn = new SqlConnection(
                       ConfigUtilities.GetConnectionString(
                       connectionStringName));
                   dbConn.Open();

                   selectStatement = new SqlDataAdapter(
                       sql, dbConn);
                   selectStatement.Fill(cachedTables, entity);

                   SqlCacheDependency cacheDep = new
SqlCacheDependency(
                       sqlCacheDepName, entity);

                   HttpRuntime.Cache.Insert(cacheKey,
                       cachedTables.Tables[0], cacheDep,
                       Cache.NoAbsoluteExpiration,
Cache.NoSlidingExpiration,
                       CacheItemPriority.NotRemovable, callback);
               }
               finally
               {
                   // Only the db connection is required to be
closed.
                   // SqlDataAdapter instances are automatically
closed
                   dbConn.Close();
               }
           }
       }

I think that's the major parts. Looking forward to hearing back from
you!