0

I am trying to use SqlCacheDependency to allow me to invalidate the application cache for my web application when a certain key changes in a settings table. Everything seems OK code wise to me, there are no errors when registering the dependency:

CacheItemRemovedCallback onCacheItemRemoved = new CacheItemRemovedCallback(CacheItemRemoved);

lock (_cacheLock)
{
    using (var connection = new SqlConnection(_connectionString))
    {  
        var command = new SqlCommand("SPNameHere", connection)
        {
            CommandType = CommandType.StoredProcedure
        };

        command.Parameters.AddWithValue("@Context", context.ToString());

        var dependency = new SqlCacheDependency(command);

        connection.Open();

        command.ExecuteNonQuery();

        HttpRuntime.Cache.Insert(GetKey(context), value, dependency, Cache.NoAbsoluteExpiration, TimeSpan.FromHours(24), CacheItemPriority.Default, onCacheItemRemoved);
    }
}

and the SP I am running is very basic (I even tried without the WHERE, but had the same issue):

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

SELECT
     Value
FROM 
     [dbo].[tb_SystemSettings]
 WHERE
    'SomePrefixText_' + @Context = Key

When I look at SQL Profiler, I can see there are 6 events raised. 5 being

3 - Subscription fired

and the final one being

1 - Subscription registered

The event text for the errors contain:

id="0" type="subscribe" source="statement" info="invalid" database_id="0"

Which would suggest there is an issue with the SP, but I cannot for the life of me figure out where the issue lies. The result of all of the is my callback for CacheItemRemoved is called constantly with a reason of DependencyChanged. I have scoured the web to see what the problem could be and none of them seem to be the same problem as I have here.

Is there something I am missing with this? Any help would be greatly appreciated!

UPDATE:

I tried registering the query text, rather than an SP call, and I only got the Subscription registered event, but running and UPDATE on one row took nearly 2 minutes (there are only 6 rows in the table), and profiler showed change events firing constantly. What could cause this? There are not triggers on the table or anything like that.

Odrai
  • 2,163
  • 2
  • 31
  • 62
Steven Brookes
  • 834
  • 6
  • 27
  • Try playing with the lock, remove it or use a lock around the complete operation like here.. https://stackoverflow.com/a/844948/3254405 – boateng Nov 29 '17 at 15:48
  • Hi, I had a play with the locks to no avail. I did register the raw query rather than the SP command and got different results, will update my question with my findings – Steven Brookes Nov 29 '17 at 16:37
  • Raw queries not ideal due to sql injections.. Try this popular alternative to ADO .Net.. http://dapper-tutorial.net/parameter-string – boateng Nov 30 '17 at 15:07
  • The point is I don't want to register a query...I want to register a stored procedure command – Steven Brookes Nov 30 '17 at 16:55
  • If you are querying a system table, try with basic table.. Because according to the specs that's not allowed.. https://technet.microsoft.com/en-us/library/ms181122(v=sql.105).aspx – boateng Dec 01 '17 at 01:05
  • It's not a system table, it is a table within my application – Steven Brookes Dec 01 '17 at 09:01
  • You don't seem to close the connection after command.ExecuteNonQuery(); is called.. – boateng Dec 01 '17 at 14:39
  • It's within a using block, the connection is closed and disposed upon exiting the using block – Steven Brookes Dec 01 '17 at 15:46
  • Here it states this important point or memory leak would result.. Also your callback fires and connection still open, may be indefinitely.. Important Point: When using a CacheItemRemovedCallback, make sure that you make the callback method ("OnCachedItemRemoved" in the sample above) a static method.. https://www.codeproject.com/Articles/117476/CacheItemRemovedCallback-Example-in-ASP-NET – boateng Dec 01 '17 at 16:14

0 Answers0