Automatically sending emails based on date in DB table  
Author Message
Dan Ehrig





PostPosted: Visual C# General, Automatically sending emails based on date in DB table Top

I'm hoping to get some feedback on this. I have a simple web page for entering medical breathing mask fitness tests. The data is stored in an SQL 2000 server. The owner is now requesting that emails be automatically generated and sent to the staff members based on how long its been since their last test. Essentially, they're supposed to get reminder emails 60 days and 30 days in advance of the 1 year anniversary of their most recent test.

I've been trying to figure out how to approach this. Would it be best to write a windows service to do it Some combination of DB triggers and scripting Has anyone done anything like this before


Visual C#2  
 
 
James Curran





PostPosted: Visual C# General, Automatically sending emails based on date in DB table Top

Actually a Windows service is probably overkill for this, as it needs to be run only once a day (or perhaps, only once a week) so, first write it as a console app, and get it running, then decided how to run it (I'd suggest as a Scheduled Task).

Then just a simple query (select P.*, tr.TestDate from PatientInfo p inner Join TestResults TR on p.PatientId = tr.PatientId where max(TR.TestDate) < GetDate() - 305 and max(tr.testDate) > GetDate()-306) will give you the info you need to write the emails.