How can I do Time based / scheduled events in .NET ?

Question

Hi folks,

I need to offer scheduling of actions/events, in our web site. A crap analogy could be a calendar system, where a person adds a calendar item and when the date/time has been 'hit', then some logic (eg calculate a report) is fired.

I could be having hundreds and even thousands of scheduled events that are entered by my clients. When a client enters something to get scheduled, I will save that info to the database. Then i'm guessing i'll added an event/job somewhere, which will contain the database table primary key. when the event is to be fired, i'll grab that info from the db, then do the logic. done.

What are some common solutions to handle this?

I'm using .NET 3.5 SP1. DB is Sql Server 2008. UI will be webbased though.

I wasn't sure if people use MSMQ? Or something built into Sql Server? Or some open source library (eg. Quartz.NET) with an NT Service. Server will be windows 2008 standard edition.

Also - please not suggestions to use a cron job equivalent or any command line scripts, etc.

Finally, and this is a secondary objective .. i'd love to throw this up onto azure for shiz and giggles ... so is the also possible? it's only a wish list idea. I would prefer to do it on a dedicated box if the solution is easier, than using Azure.

cheers :)

edit: Logic to process when an event is required to be fired, are background jobs. No UI required.

Answer 1

It depends a bit on the nature of the system. Working from the assumption that the full definition of the events is stored in the database:

• If the event needs to cause something in a Web UI, on updates check the table for entries that have 'happened' and add the appropriate visual indicators / screens / popups to the next web request output.
• If your events relate to background processing, the simplest solution is to write a separate stand-along .NET application that polls the database events table for new records, and checks against the clock to see which events have happened. You can go fancy and involve threading if some of the events cause long running processes, etc. but that would be the basic design. (Only use threading if you have to... a simple single-threaded app that polls the table would be most robust)

Answer 2

One option could be creating a SQL job to run at whatever the most granular interval you have is and processing all your events then.

If you want the best of both the .NET and SQL Server worlds you can use SQL jobs and call managed code as well. I don't have any experience with this solution so I apologize up-front if the resource I provided isn't very thorough.

If the tasks are very complex and you don't want to load assemblies on SQL Server or you just don't like their scheduling system then I'd recommend a .NET Windows Service with a third-party scheduler plugin. Personally if all the code could be kept in a stored procedure or a series of stored procedures I'd make a job since I think it's a lot easier to manage and a lot of the work has already been done for you.

Edit

I just noticed a comment that explained the granularity. If you have some actions that need to be performed every minute then a .NET Service solution would probably be easier since you would probably have to implement some sort of multi-threaded solution.

Answer 3

Then i'm guessing i'll added an event/job somewhere, which will contain the database table primary key. when the event is to be fired, i'll grab that info from the db, then do the logic.

I don't know what the common solution is but, reinventing the wheel, I'd think of creating a database clustered index on the event time, so I could poll the database each minute with a statement like ...

SELECT * FROM Events WHERE Events.Time < next_minute

Answer 4

You're going to need a job scheduler and a job manager since you have small granularity and you have to ability to know a priori whether all the jobs that happen on a given minute will finish by the next minute.

That makes the job of the scheduler easy. You should have a DB table that stores your jobs and every minute have a job scheduler service go to that table and select the jobs that need to be done.

It will enqueue those jobs with a job manager. You could use another table or a transactional queue to enqueue your job. Your job manager service will monitor the job queue. You can roll your own or use BizTalk to monitor the job queue and kick off an orchestration or workflow. It will pull the tasks off the queue one by one, carry out the tasks and update the status of the job in the DB.

Your issue will be scalability depending on the number of users, the number of tasks they enqueue and how intensive (time, memory, cpu, etc.) each task is allowed to be. If I were you, I would push back on the 1 minute time granularity and push it to 5 minutes. What do you need done at 1:38 p.m. that you can't wait until 1:40 p.m. for? :)

Answer 5

Hi,

This is already a discussed problem on StackOverflow. please go through http://stackoverflow.com/questions/781300/need-job-scheduler-in-asp-net

You forgot to add the most essential piece of information in your question - ASP.Net

It describes a scheduler framework for ASp.Net web apps which can run like a service and give you a method which periodically gets called and then you can fire your logic to load events, queue valid ones using timers.

Also, this one is the cheapest of all alternatives. Hope this is what you needed.

Answer 6

Workflow Foundation can do this. Use a Delay activity, set the TimeSpan to (DesiredTimeOfExecution - Now). For larger scale systems, set UnloadOnIdle to true, and the pending workflows will be persisted until needed.

Here's a very simple example of the Delay Activity.

WF is Free, included as part of the .NET Framework 3.0.

Answer 7

What you want is SQL Server Service Broker.

You can do timed database access to query for calendar data, etc. It's messaged-based, more powerful than MSMQ, requires no polling, and can be used in distributed environments if that's what you need. Works great for me!

You'll hit a stored procedure to put your event info on a SSB queue in the db. You'll give that message a time in the future to wake up and process another stored procedure that grabs your event data and puts in on a pickup queue which does an "external activation". EA sends a notification to a windows service which points to a console app. That console app grabs the event info from the pickup queue. Now you have your event data at the precise time you want it. Process it however you like in the console app.

There is a learning curve but it's well worth it if you need the reliability, flexibility, and scalability that SQL Service Broker offers. Below are some links to get you started.

Here's a comparison: SQL Service Broker vs. MSMQ

SO would only allow me to post one link unfortunately. Otherwise I could point you to more resources to help get you started with SSB.

Hope this helps!

Answer 8

Quartz.NET probably could also handle these requirements quite well. The benefits I see are in various ways of setting the scheduler (CronTrigger + calendars) and the ability to have your jobs in small units of testable code (jobs).

Quartz.NET also gives you the needed distribution and fail-over with clustering features. One nice feature is also per-trigger configurable misfire handling setups that give you opportunity to react to misfires and scheduler down-time.

SQL Service Broker seems to have the benefit of no polling, I wonder whether that will ever be the issue though, broker has the disadvantage of sounding a bit complicated ( at least from the description I read above).

Just my biased $0.02.