c# - Unable to invalidate the cache dependent on sql query -

i trying invalidate cache based on change in rows of database. have achieved success while doing same thing linq same database , table iam not able in basic way:

here code have written, please help:

public dataset getdata(string sqlcmd)     {         string connectionstr = system.configuration.configurationmanager.connectionstrings["connstr"].connectionstring;         string cachekey = "test123";         dataset ds = (dataset)httpruntime.cache.get(cachekey);         if (ds != null)         {             return ds;         }         sqlcachedependency sqldep = null;         sqlconnection conn = new sqlconnection(connectionstr);         sqlcommand cmd = new sqlcommand(sqlcmd, conn);         cmd.notification = null;         cmd.notificationautoenlist = true;         sqldataadapter sqlad = new sqldataadapter(cmd);         ds = new dataset();         sqlad.fill(ds);          system.web.caching.sqlcachedependencyadmin.enablenotifications(connectionstr);         string notificationtable = "tblmetadata";         if (!system.web.caching.sqlcachedependencyadmin.gettablesenabledfornotifications(connectionstr).contains(notificationtable))             system.web.caching.sqlcachedependencyadmin.enabletablefornotifications(connectionstr, notificationtable);         sqldep = new sqlcachedependency(cmd);          httpruntime.cache.insert(cachekey, ds, sqldep);         return ds;      } 

i using sql server 2005 , using qualified queries can work command notification.

my other code in linq working same database , table, there no point service broker or other permissions not set. please me rid of problem.

this article has troubleshooting steps the mysterious notification, along explanation how things work helps trying understand why broken. should validate each piece of machinery works:

• check subscription notification gets created in sys.dm_qn_subscriptions
• check notification gets fired
• check notification gets delivered (you can safely ignore routing , remote delivery in link, though, since sqldependency local)

on unrelated note, can use linq-to-sql , query notifications directly: linqtocache.