Solved

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

Posted on 2007-11-14
8
1,513 Views
Last Modified: 2013-11-07
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
Comment
Question by:kdunnett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 20285222
Yes, please provide code.
0
 

Author Comment

by:kdunnett
ID: 20288326
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
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 20294628
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:kdunnett
ID: 20297611
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
 

Expert Comment

by:YoungCaymanKid
ID: 20298734
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
 
LVL 51

Accepted Solution

by:
Ted Bouskill earned 500 total points
ID: 20299791
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
 

Expert Comment

by:YoungCaymanKid
ID: 20300002
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
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 20300196
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

Featured Post

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question