Al Hunter Al Hunter - 2 months ago 15
C# Question

Design Patterns for C# Windows Services that send date driven messages

For simplicity, I'm going to use the scenario of sending birthday emails to a customer base of ~1,000,000 customers (stored in a DB), as my own scenario I'm trying to solve is similar.

Typically, the solution I would design would be to create a Windows Service, scheduled using Quartz.net, which would poll the entire table of customers (albeit indexed) looking for birthdays which match today's date (or whenever the Service runs). The Service would loop round the list of customers and send the email etc. This would be scheduled to run, say, once a day.

I was wondering if this is the normal approach to solving this problem, or is there a better way / different pattern? Polling the database to me feels... a bit smelly..

I'm thinking that since we know the customers birthday when they sign up, (for example, their birthday could be in a weeks' time), should I at the point of signing up, pro-actively set up the schedule to run then?

One thing I'm trying to avoid is polling the database when we know there aren't any birthdays on that day for example (ok, out of 1,000,000 customers you'd expect that there were a few!... but let's pretend there aren't any for a whole month..) - what solution would avoid that problem?

Thanks

Answer

I would not implement a windows service if you plan on making a database call once per day. A console application being triggered by Task Scheduler is sufficient.

If you go down the once per day route - why not do this entirely in the database. Create a job executing your sql and send the emails from SQL Server (assuming you are using SQL Server).

As you mentioned, you could set up a schedule when a customer signs up. If you go that route, you can detect changes with SqlDependancy and then create a schedule in the OnDependencyChange event.

void OnDependencyChange(object sender, SqlNotificationEventArgs e )
{
  // Create schedule.
}
Comments