none
Use LINQ and SqlCacheDependency RRS feed

  • Question

  • I am using Visual Studio 2008, C# and SQL Server 2005.

    have seen examples on the NET that associates LINQ with SqlCacheDependency.

    E.g. http://www.writebetterbits.com/2008/01/overview-of-sql-server-2005-database_11.html

     

    But not function, the cache is a created However when update table the cache is not invalidation.

    I am Alter a DataBase:

    Code Snippet

    ALTER DATABASE News SET NEW_BROKER

     

    ConnectionString:

    Code Snippet

    Persist Security Info=False;Integrated Security=false;Initial Catalog=NEWS;Server=200.111.111.59; uid ='us'; pwd='pass';

     

    No Global.asax

    Code Snippet

    protected void Application_Start(object sender, EventArgs e)

    {

    SqlDependency.Start(

    ConfigurationManager.ConnectionStrings["strNEWS"].ConnectionString);

    }

     

    I am use this code in my aplication:

    Code Snippet

    public List<KeyWord> GetKeyWordFromCache()

    {

    string cacheKey = "KeyWordCacheKey";

     

    List<KeyWord> ListKeyWord = (List<KeyWord>)System.Web.HttpContext.Current.Cache.Get(cacheKey);

    if (ListKeyWord == null)

    {

    if (!dependencyStarted)

    {

    SqlDependency.Start(DataBaseConnections.GetConnection(enumDatBase.NEWS.ToString()));

    dependencyStarted = true;

    }

    NewsDataContext DbNews = new NewsDataContext(

    DataBaseConnections.GetConnection(enumDatBase.NEWS.ToString()));

     

    var query = from p in DbNews.KeyWords select p;

    ListKeyWord = query.ToList();

     

    SqlCommand cmd = (SqlCommand)DbNews.GetCommand(query);

     

    SqlCacheDependency dependency = new SqlCacheDependency(cmd);

    System.Web.HttpContext.Current.Cache.Add(cacheKey, ListKeyWord, dependency, DateTime.MaxValue,TimeSpan.Zero, CacheItemPriority.AboveNormal, null);

    }

    return ListKeyWord;

    }

     

    The cache is created so that when the table changes it does not expire. Is bever expire.

    What is missing I do?
    Need to do something in iis?
     
    Tuesday, December 2, 2008 9:33 PM

All replies

  • I guess the order in which you assign the command to the SqlDependency object and the execution of query is incorrect. Can you try the following order:

    NewsDataContext DbNews = new NewsDataContext(  
    DataBaseConnections.GetConnection(enumDatBase.NEWS.ToString()));  
     
    var query = from p in DbNews.KeyWords select p;  
    SqlCommand cmd = (SqlCommand)DbNews.GetCommand(query);  
    SqlCacheDependency dependency = new SqlCacheDependency(cmd);  
    System.Web.HttpContext.Current.Cache.Add(cacheKey, ListKeyWord, dependency, DateTime.MaxValue,TimeSpan.Zero, CacheItemPriority.AboveNormal, null);  
     
    ListKeyWord = query.ToList();  
     
     
     
     


    Hope this helps,
    Sushil.



    Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights
    Thursday, March 5, 2009 4:17 AM