c# / ASP.NET / SQL Server - running query once a day

I'm going to be doing a progress bar for a donations page on a website that I run. I only want the progress bar to update once a day, rather than on every page load as I would normally do.

What options do I have here for grabbing the current SUM of donations and putting it maybe in a flat text file for the aspx page to read rather than it queryi开发者_运维知识库ng the database every time.?

Hope this makes sense.

Another option is to use caching and set the cache to only expire once every 24 hours. Then the data is pulled and placed in cache and the cached version is served all day.

Why don't you implement Cache and make it expire in 24 hrs? This is a better solution and aids in performance too :-)

HTH

I would just run a SUM query on the database each time. Unless you're expecting millions of row inserts per day, this will be a negligable performance hit. (Providing your database table is indexed)

First: this type of query is very fast; unless you have some reason that you haven't mentioned, just have the page query the database each time it loads. A lot of people these days seem to advocate avoiding round-trips to the database. However, SQL Server is very fast, and this sort of thing will have virtually no impact on your application performance. Also, I'm a fan of having accurate data displayed whenever possible - not something that's potentially 24 hours out-of-date.

If you insist on doing this, you have a couple of options.

1. Create a VBScript or (preferred) PowerShell script that queries the database and dumps the result to, say, an aspx, ascx or HTML file in a virtual folder attached to your website. Set the script to run as a scheduled task on your web server (or a scripts server, if you have one).

2. Do the same with a Windows service. That may be overkill for this. Services are great when you need something to listen for remote connections, etc, but for running a task periodically, the built-in scheduler works just fine.

The file you generate should probably already contain the formatted HTML. Your ASP.NET page would include this as an user control or partial view. If you're using MVC, you could use Razor from your script to format the output.

Again, I think this is a bad idea, and creating unnecessary work. Unless you have a very good reason for avoiding hitting the database, don't go down this path.

If you want something to run exactly once a day, the Task Scheduler is your best bet. Have it run a query and stick the result somewhere your website can read from.

The next option is to use a cache. Have your code check if the value is in the cache. If it is, use that. If it isn't, run the query and populate the cache. Set the cache to expire in 24 hours. The problem with this is that the query will probably run more then once on a busy website (multiple requests come in after the cache is expired but before the first one populates it again), but that likely won't matter for a query that doesn't take long.

• Try searching for ASP.NET global cache examples. Set two variables, one for you query result, another for the DateTime of the query execution. Check in code if DateTime.Now - the date in cache is > 24h -> update again (or something like that)

• If you have static variable in some class, it is visible across the entire web app

I would say to use the cache. As an enhancement on the other caching answers you could impliment some code to make it so that the cache would expire at the same time every day rather than just 24 hours after the first access of the day. This protects against having erratic update times in a scenario where traffic is sporatic. The first request after the cut off will look up the value and persist it until the next cutoff time. But the cutoff stays constant regardless of when the first request occurs.

Something along the lines of this:

    int ProgressValue
    {
        get
        {
            int? Value = Cache["ProgressValue"] as int?;
            if (Value == null)
            {
                //Set expiration time to 6 AM tomorrow.
                DateTime ExpTime = DateTime.Now.Date.AddDays(1).AddHours(6);
                Value = GetValueFromDB();
                Cache.Insert("ProgressValue", Value, null, ExpTime, System.Web.Caching.Cache.NoSlidingExpiration);
            }
            return (int)Value;
        }
    }