Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2007-11-14
8
Medium Priority
?
1,515 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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

886 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