Would like to execute a query once per day in ASP.NET MVC

Question

I would like my ASP.NET MVC app to execute a query once per day. What is the recommended way to do this?

My first thought is to put a timer in Global.asax that goes off every 24 hours, then call my query from the Elapsed handler. Any pitfalls with doing it this way? Is there a better way?

Edit

Let me add a little detail to what I'm trying to do. I'd specifically like the query to execute at midnight every day. If a day is missed (say due to sever maintenance or upgrading the app), that wouldn't be a major issue.

Edit 2

Couple more details:

1. The query is actually an INSERT, not a SELECT. The purpose is to add a "renewal" record for any member that is due to renew his/her membership at the end of the month.
2. I'm using SQL Server Compact (it's a very small database).

Answer 1

Does it have to originate in the Web layer? Who'd be there to consume the HTML? Typically, periodic SQL queries are scheduled within the database. In case of MS SQL Server - via the SQL Agent job facility. SQL Server can even send e-mail.

RE: edit2: Should've told so right away. SQL Server Compact is not the same as SQL Server - for one, it does not have SQL Agent IIRC. Still, invoking the Web layer is an overkill. I'd use a Windows Scripting Host file (.js) in conjuction with Windows task scheduler. WSH files can connect to databases via ADO and do whatever they want - inserts, selects, anything.

To detect missed scheduled runs, introduce an extra table with a log of scheduled runs. Then on subsequent runs you can analyse the date of the last run and act accordingly.

Edit2: so no administrative access. You should really tell all those details in the question. In this case, I would go through the Web layer after all, but the scheduling would be on MY end - where I do have control. Have Task Scheduler run on your end and invoke an HTTP URL on the server. To invoke URLs, you can use something like the free CURL utility. Running IE in scheduled manner has the disadvantage of leaving the window open.

IIS is not a scheduling engine.

Edit3 re:comment: sorry, I've misunderstood the nature of your setup. My own experiences have clouded my judgement :) Can you just run a check during every logon operation, and if it's been a while since the last maintenance operation, run it right then and there? How long does the maintenance take? If it's ~1min+, makes sense to run it in a worker thread, so that the logging-on user is not made wait.

Scheduling daily maintenance is a good idea in general, and it is implemented fairly often, but it seems you simply don't have the capability.

Answer 2

I do this very thing in my web apps, but use Asynchronous HTTP Handlers (http://msdn.microsoft.com/en-us/library/ms227433.aspx#Y512); I believe this would be recommended. I just start it off on application start and shut it down on application end (Global.asx).

The thing to remember is that you'll probably have to store the last time the query ran in the database because you'll loose track of that when your application pool recycles.