3

I'm developing a system to handle sending transactional emails to our customers. This is how it works:

  1. An event occurs during the order's life cycle, for example 'shipped'

  2. This event will trigger the creation of an email in the database (email queue)

  3. A separate windows service is polling the db table for new emails to send. When it finds one it calls a Web service with all the required data. It's the Web service's responsibility to handle the actual sending of the email.

My question relates to step 2. When an email triggering event occurs, should I take a snapshot of all the data required by the service (thereby duplicating data and introducing new tables) or should I get the required data from the transactional db tables only at the point where I'm ready to call the Web service.

[Note: I'm not at all concerned with the body of the email, all I'm doing is passing the data to a Web service. The email templates etc are handled by another system. My concern is purely to queue the email notification and pass the required data to the Web service.]

|improve this question
  • 2
    Why not just use something like MSMQ (perhaps wrapped nicely using something like Rebus of NServiceBus) to send a message containing contents for the email to be sent from the application server to the emailer service. Since MSMQ supports enlisting into the DTC, you can update the data in the database, and send the message to the email service, in a single transaction. – Pete Sep 3 '14 at 11:58
  • What's involved in adding a message to the queue from a stored procedure (sql server 2005)? – user2669338 Sep 3 '14 at 15:38
  • I wouldn't send a message to a queue from a stored procedure, I would send it from c# code (since the question is tagged with c#) – Pete Sep 3 '14 at 19:20
  • Apologies, I tried to amend tag earlier but couldn't work out how to do it - I'm using the mobile app. I'm afraid some of the logic which creates the email request is in stored procedures. I'm writing a c# class to create the email but a couple of applications will bypass the class and populate the sql tables with the email data directly. – user2669338 Sep 3 '14 at 19:30
  • 1
    If you are looking at queuing, SQL Server has Service Broker built in. MS has provided extensions to allow messages to be delivered to WCF endpoints for C# processing. – Michael Green Sep 4 '14 at 3:52
3

In this case I would serilize the objects needed to pass to the web service and store them in the database. The two services should operate as independently from one another as they can. Serialization of the complete "driving" objects will allow you to update and adjust the mailer without having to update and adjust the event listener.

You could also create a data containing class and pass just the information you need, but then you make 3 updates where there only needs to be one down the line.

However, it seems like your building a very complex system, that shouldn't be. Assuming that you need to call the web service to send an email, then why not just call the web service in the event listener. If you need to store something to the database, then you can do that too, in the event listener.

|improve this answer
  • Good idea with the serialisation of the objects. The reason for storing the request and not sending it when the event occurs is because the event can come from 2 places, either an application or from a stored procedure. A lot of our business logic is in stored procedure and moving this out to a business logic layer may not be possible for all our apps. The other reason is that if the call to the Web service fails, I would like it to retry x number of times before abandoning the email. Event listener is just a shared class which saves the email (Stored procs write to Db directly). – user2669338 Sep 3 '14 at 12:13
2

Record the values at the point of triggering and pass those to the emailer. Then the email's content will be consistent.

To expand on your example - an order enters the state 'shipped' and an email is queued. Before the email is send the customer cancels. The order is now in state 'cancelled'. Now the email is sent. What will its content be "Dear Customer, your order is now 'cancelled' and will be with you on 5th September"? That's less forgivable than getting confirmation of an order which has since been changed.

|improve this answer
  • Don't you mean this scenario is 'more forgivable'? – user2669338 Sep 4 '14 at 10:33
  • No - to my mind receiving an email confirming a newly-cancelled order is more forgivable. Receiving a confirmatory email with the wrong status would be less forgivable. – Michael Green Sep 4 '14 at 11:06
  • It was confusing because your original comment said "your order is 'NOT cancelled'". Now that you've edited it, it makes more sense. Thanks. – user2669338 Sep 4 '14 at 11:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.