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!

Answer 1

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:

1. Do not use SELECT * or table_name.* in your queries.
2. 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.

Answer 2

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";