Why does my SqlCacheDependency HasChanged come back false but almost immediately after changes to true?

Question

I cannot figure out why the HasChanged value of my SqlCacheDependency object is coming back originally from the command execution as false, but somewhere almost immediately after it comes back from the database, the value changes to true.

Sometimes this happens before the item is even inserted into the cache, causing the cache to discard it immediately, sometimes it's after the insert, and I can grab an enumerator which sees the key in the cache but before I even loop to that item in the cache it's been deleted.

SPROC:

ALTER PROCEDURE [dbo].[ntz_dal_ER_X_Note_SelectAllWER_ID]
        @ER_ID int
AS
BEGIN
    SELECT
        ER_X_Note_ID,
        ER_ID,
        Note_ID
    FROM dbo.ER_X_Note e
    WHERE
        ER_ID = @ER_ID
END

The database is MS SQL Server 2008, broker service is enabled, and SOME output does cache and remain cached. For instance, this one works just fine:

ALTER PROC [dbo].[ntz_dal_GetCacheControllerByEntityName] (
    @Name varchar(50)
) AS
BEGIN
    SELECT 
        CacheController_ID,
        EntityName,
        CacheEnabled,
        Expiration
    From dbo.CacheController cc
    WHERE   EntityName = @Name
END

The code which calls the SPROC in question that fails:

    DataSet toReturn;
    Hashtable paramHash = new Hashtable();
    paramHash.Add("ER_ID", _eR_ID.IsNull ? null : _eR_ID.Value.ToString());
    string cacheName = BuildCacheString("ntz_dal_ER_X_Note_SelectAllWER_ID", paramHash);
    toReturn = (DataSet)GetFromCache(cacheName);
    if (toReturn == null)
    {

        // Set up parameters (1 input and 0 output)
        SqlParameter[] arParms = {
                new SqlParameter("@ER_ID", _eR_ID),
            };
        SqlCacheDependency scd;

        // Execute query.
        toReturn = _dbTransaction != null 
            ? _dbConnection.ExecuteDataset(_dbTransaction, "dbo.[ntz_dal_ER_X_Note_SelectAllWER_ID]", out scd, arParms) 
            : _dbConnection.ExecuteDataset("dbo.[ntz_dal_ER_X_Note_SelectAllWER_ID]", out scd, arParms);

        AddToCache(cacheName, toReturn, scd);
    }

    return toReturn;

Code that works

        const string sprocName = "ntz_dal_GetCacheControllerByEntityName";
        string cacheControlPrefix = "CacheController_" + CachePrefix;
        CacheControl controller = (CacheControl)_cache[cacheControlPrefix];
        if (controller == null)
        {
            try
            {
                SqlParameter[] arParms = {
                                             new SqlParameter("@Name", CachePrefix),
                                         };
                SqlCacheDependency sqlCacheDependency;

                // Execute query.
                DataSet result = _dbTransaction != null
                                     ? _dbConnection.ExecuteDataset(_dbTransaction, sprocName, out sqlCacheDependency, arParms)
                                     : _dbConnection.ExecuteDataset(sprocName, out sqlCacheDependency, arParms);

                controller = result.Tables[0].Rows.Count == 0
                                 ? new CacheControl(false)
                                 : new CacheControl(result.Tables[0].Rows[0]);

                _cache.Insert(cacheControlPrefix, controller, sqlCacheDependency);
            }
            catch (Exception ex)
            {
                // if sproc retreival fails cache the result of false so we don't keep trying
                // this is the only case where it can be added with no expiration date
                controller = new CacheControl(false);

                // direct cache insert, no dependency, no expiration, never try again for this entity
                if (HttpContext.Current != null && UseCaching && _cache != null) _cache.Insert(cacheControlPrefix, controller);
            }
        }
        return controller;

The AddToCache method is overloaded and has more tests in it; The direct _cache.Insert in the working method is to bypass those other tests. The working code helps determine if db caching should happen at all.

You can see that when the "non working" data is retrieved initially, all is OK:

But somewhere random beyond that point, in this instance, just stepping into the next method

And yet the data is NOT changing at all; I'm the only one touching this instance of the database.

Answer 1

It was really, really simple, so simple I completely overlooked it.

In this article Creating a Query for Notification, which I DID scour multiple times, it clearly states:

SET Option Settings

When a SELECT statement is executed under a notification request, the connection that submits the request must have the options for the connection set as follows:

ANSI_NULLS ON
ANSI_PADDING ON
ANSI_WARNINGS ON
CONCAT_NULL_YIELDS_NULL ON
QUOTED_IDENTIFIER ON
NUMERIC_ROUNDABORT OFF
ARITHABORT ON

Well, I read and re-read and RE-re-read the sproc, and I still didn't see that both ANSI_NULLS and QUOTED_IDENTIFIER were "OFF", not ON.

My dataset is now caching and retaining the data properly without false indicators of change.

Answer 2

I have a hunch that the issue is with your _eR_ID. I think that you should try adding a local variable to the failing procedure that uses an impossible value for _eR_ID, such as -1. I never trust what is going to happen when nulls are involved and I think this could be the source of your problem.

Here is the modified version that I recommend trying:

DataSet toReturn;
Hashtable paramHash = new Hashtable();

int local_er_ID = eR_ID.IsNull ? -1 : _eR_ID.Value;
paramHash.Add("ER_ID", local_eR_ID.ToString());

string cacheName = BuildCacheString("ntz_dal_ER_X_Note_SelectAllWER_ID", paramHash);
toReturn = (DataSet)GetFromCache(cacheName);
if (toReturn == null)
{

    // Set up parameters (1 input and 0 output)
    SqlParameter[] arParms = {
            new SqlParameter("@ER_ID", local_eR_ID),
        };
    SqlCacheDependency scd;

    // Execute query.
    toReturn = _dbTransaction != null 
        ? _dbConnection.ExecuteDataset(_dbTransaction, "dbo.[ntz_dal_ER_X_Note_SelectAllWER_ID]", out scd, arParms) 
        : _dbConnection.ExecuteDataset("dbo.[ntz_dal_ER_X_Note_SelectAllWER_ID]", out scd, arParms);

    AddToCache(cacheName, toReturn, scd);
}

return toReturn;

Important

While creating the above code, I think I discovered the source of your problem: when setting the stored proc parameter, you are using _eR_ID but when you set the paramHash you are using _eR_ID.Value.

The code rewrite will solve this problem, but I suspect that this is the root of the problem.