Solved

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

Posted on 2007-11-14
8
1,504 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:tedbilly
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:tedbilly
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
 

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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:
tedbilly 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:tedbilly
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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.
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now