SqlCacheDependency not working when using SqlCommand for cache-key

Question

This code works fine, it invalidates the data whenever it is changed in the database:

    AggregateCacheDependency aggDep = new AggregateCacheDependency();

                System.Data.SqlClient.SqlCommand ocom = new System.Data.SqlClient.SqlCommand();
                SqlCacheDependency SqlDep = new SqlCacheDependency("DBNAMEINCONFIG", "Products");    
                aggDep.Add(SqlDep);

I cannot have a straight invalidate on the entire "Products" table though, I need to be able to invalidate a selection on the table. The problem I'm having is the following code does not ever invalidate the cache when the data is changed:

AggregateCacheDependency aggDep = new AggregateCacheDependency();

            System.Data.SqlClient.SqlCommand ocom = new System.Data.SqlClient.SqlCommand();
            ocom.CommandText = "SELECT ID,ClinicID,Price,Enabled FROM dbo.Products WHERE ClinicID = 1";
            ocom.Connection = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["DBSTRING"].ToString());
            SqlCacheDependency SqlDep = new SqlCacheDependency(ocom);

            aggDep.Add(SqlDep);

I tried to include all the information necessary to analyze this, but please let me know if I should include more!

score 0 · Answer 1 · answered Jan 09 '12 at 17:40

0

The problem is, according to the rules outlined here, that you cannot use SELECT * for the query.

The following change should solve your issue:

ocom.CommandText = "SELECT ClinicID FROM Products WHERE ClinicID = 1";

answered Jan 09 '12 at 17:40

competent_tech

44,465
11
90
113

Thanks for the tip. I updated the SELECT statement, but the problem still exists where the cache is not invalidated even if something in the table (and the SELECT statement) changes. – Greg Snider Jan 09 '12 at 17:51
Ah, maybe try adding specific columns that do change to the select statement. I am a little unclear on exactly how the underlying mechanism works, but it may be watching columns in the select statement itself. – competent_tech Jan 09 '12 at 17:53
I updated the opening post with the current code. Changed this line: ocom.CommandText = "SELECT ID,ClinicID,Price,Enabled FROM dbo.Products WHERE ClinicID = 1"; – Greg Snider Jan 09 '12 at 17:53
It still doesn't invalidate the cache even when the SELECT statement looks like it conforms to the requirements. – Greg Snider Jan 09 '12 at 17:59

score 0 · Answer 2 · answered Jan 09 '12 at 20:13

0

As competent_tech pointed out there are quite a few rules for the queries used to build SqlCacheDependency. According to this MSDN article the most important are:

Do not use SELECT * or table_name.* in your queries.
You must use fully qualified table names (e.g. dbo.Products).

Beside those rules it is important to execute the SqlCommand used to build the SqlCacheDependency in order to enable the query notification:

using (SqlDataReader reader = ocom.ExecuteReader())
{
  while (reader.Read())
  {           
  }
} 

SqlCacheDependency SqlDep = new SqlCacheDependency(ocom);

aggDep.Add(SqlDep);

Hope, this helps.

answered Jan 09 '12 at 20:13

Hans

12,902
2
57
60

Hans, I tried executing the command before creating the SqlCacheDependency object, but still no luck with it invalidating the cached object. – Greg Snider Jan 09 '12 at 21:25
@GregSnider: Could you please send a more complete code sample (how do you initiialize the SqlCacheDependency infrastructure)? – Hans Jan 10 '12 at 17:22

SqlCacheDependency not working when using SqlCommand for cache-key

2 Answers2