Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1519
  • Last Modified:

ASP.NET, C#, Sql Server 2005, and Cache Dependencies

All,

I'm trying to find a good working example of Cache Dependencies written in ASP.NET with C#.

Everything i have (which isn't much) works in the sense that I can get the data, but if the data is refreshed it doesn't show up on the asp.net page, just the same orignal query.

Any ideas?  I'll post the code if it'll help.

Kris
0
kdunnett
Asked:
kdunnett
  • 4
  • 2
  • 2
1 Solution
 
Ted BouskillSenior Software DeveloperCommented:
Yes, please provide code.
0
 
kdunnettAuthor Commented:
Here's the jist of it:


...
System.Data.SqlClient.SqlDependency.Start(ConnectionStrings.ReadWrite);
DataTable dt = (DataTable)Cache.Get("CACHED_TABLE");

if(dt == null)

{
      dt = new DataTable();

      using (CustomConnectionMSSQL DB = new CustomConnectionMSSQL(ConnectionStrings.ReadOnly))
      {
            DB.StoredProcedure("SP_CLIENT_GET_NAV_NODES_BY_SITE_ID");
            DB.AddParameter("@pSITE_ID", System.Web.Configuration.WebConfigurationManager.AppSettings["SITE_ID"], SqlType.Int);
            DB.AddParameter("@pERROR", SqlType.TinyInt, ParameterDirection.Output);
            DB.AddParameter("@pRV", SqlType.Int, ParameterDirection.ReturnValue);
            DB.Fill(links);
            Cache.Insert("CACHED_TABLE",dt, new SqlCacheDependency(DB.Command));
                 
            pRV = DB.GetParamValueToString("@pRV");
            pERROR = DB.GetParamValueToString("@pERROR");
      }
}
...
0
 
Ted BouskillSenior Software DeveloperCommented:
So, if the data table doesn't exist in the cache it is added.  However, the key point is when does it expire?  From what I can see when the DB.Command changes, the cache will expire.  However, when does that DB.Command change?

The DB object disappears as soon as th using using (CustomConnectionMSSQL DB = new CustomConnectionMSSQL(ConnectionStrings.ReadOnly)) {...} finishes!

Just to be clear, is this data that rarely changes and is very time consuming to retrieve?  If both statements are true then you should use the Cache object.  If one is false, then the Cache should NOT be used.

Here is an example:  "Hey Joe, the query that retrieves this report data is taking at least a minute which delays loading the page.  The data in the report is updated nightly, maybe we should put it in the cache?"

If the data in your query can be retrieved in less than 1/2 second and/or changes frequently, don't put it in the cache.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
kdunnettAuthor Commented:
Yes, both statements are true.  Its data that rarely changes and its very time consuming to get.

Do you have suggestions or an example I can use?

Thanks,
Kris
0
 
YoungCaymanKidCommented:
Hey, what we're looking for is SqlCacheDependency, from what I am to understand, what happens is SQL Server 2005 remembers the query, when information in the rows the query would return are modified, SQL Server sends a notification to .NET saying "Expire that Cache as the data has changed".

The above example was just a way for me to simplify down what I am looking to have done. It's not so much the query that i'm worried about, it's all the logic that is happening to the data after the fact. Since this is the going to be hit on every page load, across every page in the application, I want to store the end result in a DataTable in the Cache. This cached DataTable expires once people modify one or more values in the DB.

Now I understand that there is another way I can go about this, but I still want to use SqlCacheDependency as there are instances in the future whereby I must use them, and this is a good time for me to get some experience with SqlDepenedencies now.
0
 
Ted BouskillSenior Software DeveloperCommented:
Here is the issue with any caching in a web application.  As you know web applications are stateless.  A web server sits idle listening to port 80 waiting to fulfill web requests for pages.  When a request comes in it executes code to generate the page, sends the page, then if no new requests of come in goes back to being idle.

Therefore, the application Cache will only be refreshed when some accesses a page.  The CacheDependency object sets a predicate for the cache to decide when to refresh the cache.  It can be an expiring time or an object that changes state (the SqlCacheDendency is a specialize sub-class of CacheDependency)

So you need to fix your code so that when the user accesses the Cache the decision to update the Cache can be triggered.  For example, I sometimes use a separate value in another table in the database that is a counter.  Everytime a rarely changed table changes, the counter is incremented.  Objects that are caching data in the table take a copy of that counter everytime they take a copy of the table.  Then the code that is caching the table executes it can compare it's copy of the value to the one in the database and trigger a cache update if necessary.

Here is a link for a pretty interesting sample: http://www.codeproject.com/cs/database/DatabaseEventsArticle.asp

However, a key point is that whatever client happens to request a page when the cache needs to be refreshed will be penalized!  Avoiding that hassle is another question.
0
 
YoungCaymanKidCommented:
alright, so basically the cache could expire but if the web server isn't active at the time then it could miss out on the notification SQL is sending it. You'd think Microsoft would have .NET start listening on a specific port for Sql Noticifications. Oh well...
0
 
Ted BouskillSenior Software DeveloperCommented:
Agreed. I wish there was a specific service running on a web application with events you could tie into to push data into the web application from SQL or pull from SQL without a user requesting a page.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now