ASP.NET MVC + SQL Server Application: Best Way to Send Out Event-Driven E-mail Notifications

Question

I have an ASP.NET MVC application that utilizes NHiberante and SQL Server 2008 on the backend. There are requirements for sending out both event-driven notifications on a daily/weekly basis AND general notifications on a weekly basis.

Here is an example of how the event-driven workflow needs to work:

1. Employee(s) create a number of purchase orders.
2. An e-mail notification is sent daily to any supervisor with a subordinate employee that has made a purchase order with a list of all purchase orders created by subordinates that require his approval. The supervisor should only get this once (e.g. if Employee A creates a PO, his supervisor should not get an e-mail EVERY DAY until he approves). Also, the list of purchase orders should ONLY include those which the supervisor has NOT taken action against. If no purchase orders need approval by a given supervisor ... they should not get an e-mail.
3. An e-mail notification is sent daily to Dept. Managers with a list of all purchase orders APPROVED by subordinate supervisors in a similar fashion to #2 above.
4. Any time any action is taken with regards to approving a PO by a supervisor or dept. manager, the employee should get an e-mail notification daily listing ALL such changes. If there are none for a given employee, they should not get an e-mail at all.

So given such a workflow:

• What is the best way to schedule such notifications to happen daily, weekly or even immediately after an event occurs?
• How would you ensure that such event-driven notifications ONLY get delivered once?
• How would you handle exceptions to ensure that failed attempts to send e-mail are logged and so that an attempt could be made to send the following day?

Thanks!

Answer 1

You can host a windows workflow or windows service. and setup a message queue to process these events. You might just use the database for your message queue or you could use ms message queue or use triggers in the database. But this kind of functionality really shouldn't be a responsibility of your front end web app. If push comes to shove you could spawn off another thread in you asp.net application to process this queue.

Answer 2

This can be done using SQL Server Agent read more about it here:

http://msdn.microsoft.com/en-us/library/ms189237.aspx

Answer 3

Sounds like a job for a service or a scheduled job.

You don't want to do it in ASP.NET because you'd have to configure IIS to keep your app alive all the time, which may not be the best idea.

A scheduled task is fine, but you'll have to program it with all the logic for parsing out your data. Not the best for a separation of concerns. Also you'll have to update two code bases if something changes.

A service isn't ideal as it would only truly be doing something once a day. But you could set up a wcf service and have the website queue up emails using the service.

Answer 4

You can add a normal action in a controller

Function SendEmails() As ActionResult
    Dim result As String = ""
    ''//big timeout to handle the load
    HttpContext.Server.ScriptTimeout = 60 * 10 ''//ten minutes

    result = DoTheActualWork()

    ''//returns text/plain
    Return Content(result)

End Function

And then call the page from a scheduled task. Can be a scheduled task on the server or in any machine. Use a .vbs for this:

SendEmails.vbs:

''//Force the script to finish on an error.
On Error Resume Next

''//Declare variables
Dim objRequest
Dim URL

Set objRequest = CreateObject("Microsoft.XMLHTTP")

''//Put together the URL link appending the Variables.
URL = "http://www.mysite.com/system/sendemails"

''//Open the HTTP request and pass the URL to the objRequest object
objRequest.open "POST", URL , false

''//Send the HTML Request
objRequest.Send

''//Set the object to nothing
Set objRequest = Nothing

Answer 5

I would have all your emails, notifications, etc saved to a DB / table first and then have a service that polls for new entries in this database or table that handles the actual sending of the email / notification.

To address your specific situations you could have controllers write to the DB when a email / notification is required and a service that does you interval / event specific checks also write to the DB to create new email. This way your application and service don't really care about how or what is happening to these notifications, they are just saying, "Hey do something." and the emailer/notification service is actually doing the implementation.

The advantage to this is that if your email provider is down you don't lose any emails and you have a history of all emails sent with their details of when, who, etc... You can also rip out or change the emailer to do more, like send to Twitter or a phone text message etc. This effectively decouples your notifications from your application.

All applications I have made recently use this type of model and it has stopped emails from being lost due to service failures and other reasons. It has also made it possible to lookup all emails that have gone through the system and get metrics that allows me to optimize the need to send emails by storing extra information in the email record like reason sent, if it's to report an error, etc... Adding on additions such as routing notifications (eg go to text message instead if email) based on time of day or user has been possible with no changes to the primary applicaton.

Your customer might think all they need is email today but you should make sure your solution is flexible enough to allow for more than just email in the future with just minor tweeks.