SqlCacheDependency with SqlCommand not working

Question

Having problems with implementing very basic SqlCacheDependency prototype.

Have tried this with a sproc but now I'm down to straight command execution.

    public SqlCommand GetReadCommand(int ID)
    {
        SqlCommand cmd = new SqlCommand("SELECT dbo.Entity.Entity_ID, dbo.Entity.Name FROM dbo.Entity WHERE Entity_ID = @ID", _conn);
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.Add(new SqlParameter("ID", ID));

        return cmd;
    }

    public SqlCacheDependency GetSqlDependency(int ID)
    {
        SqlCommand cmd = GetReadCommand(ID);
        SqlCacheDependency dep = new SqlCacheDependency(cmd);
        return dep;
    }

And I'm reading a dataset of all objects and creating a very simple dependency test on some data I inserted manually:

    public DataSet SelectAll()
    {
        DataSet result;
        if (_cache["Entity_FullSet"] == null)
        {
            SqlCommand cmd = new SqlCommand("dbo.GetAllEntities", _conn);
            cmd.CommandType = CommandType.StoredProcedure;
            _conn.Open();

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            result = new DataSet();
            da.Fill(result);


            _cache[MasterCacheKey()] = DateTime.Now;
            string[] mk = new[] { MasterCacheKey() };
            CacheDependency cd = new CacheDependency(null, mk);

            SqlCacheDependency scd = new SqlCacheDependency(GetReadCommand(1));
            CacheDependency[] cds = new[] { cd, scd };

            AggregateCacheDependency acd = new AggregateCacheDependency();
            acd.Add(acd);

            _cache.Insert("Entity_FullSet", result, scd);
            _conn.Close();
        }
        else
        {
            result = (DataSet)_cache["Entity_FullSet"];
        }
        return result;
    }

The 'master key' is there for testing aggregate cache dependencies - changing it works great, but the sql dependency on 1 (variable scd) simply isn't working. If I go and update the table - even delete the row - nothing happens, the cache isn't cleared.

enter image description here

Any ideas as to why the sql dependency isn't registering/firing would be greatly appreciated!

score 1 · Accepted Answer · answered Dec 17 '11 at 08:16

1

Have you enabled service broker in the DB, and turned on query notifications?

ALTER DATABASE [YourDB] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

In Application_Start():

SqlDependency.Start(connectionString)

And call Stop() in Application_End().

Can you post the code for your stored procedure? Do you by any chance have SET NOCOUNT ON at the beginning of your SP? If so, that will prevent query notifications.

For the code as posted above, try replacing those 3-part column names with one or two-part names. For example, replace "dbo.Entity.Entity_ID" with "Entity_ID" or (better) "e.Entity_ID" (after assigning the alias "e" to dbo.Entity).

answered Dec 17 '11 at 08:16

RickNZ

18,448
3
51
66

I had not set those things - maybe I missed something obvious but in my reads of the classes documentation I don't recall coming across that alter database statement. Trying it now. – The Evil Greebo Dec 17 '11 at 20:18
Oh - WRT the sproc - I titled this badly I think - at first I thought it was sprocs, but then I tried it with a direct sql statement and still no joy. – The Evil Greebo Dec 17 '11 at 20:19
Thanks, it was a combination of that simple thing plus I need my dependency to be on the same cmd executed. – The Evil Greebo Dec 19 '11 at 14:47

SqlCacheDependency with SqlCommand not working

1 Answers1