Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-11-14
8
Medium Priority
?
1,514 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
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.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

721 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