How to have a console application constantly send emails?

Question

I have a C# console application which runs a stored procedure which imports email data from a text file into a SQL table, which the table's data is then used to send emails (e.g. To, From, Body, etc) in the C# console application. The application calls another stored procedure which gathers all records which have not yet been flagged as sent and sends emails to them.

The issue I'm struggling with is that these emails need to constantly be sent out based on the text file containing the email information that is dropped off. The lowest frequency I see in Task Scheduler to run an application is every 5 minutes.

Basically I'm in need of a way to continuously send emails based on flat files that are dropped off which have the email information necessary to send.

What is a good approach to this?

Can your console app not just run continually, and inside the main function you can have a loop that just polls the database at the frequency you would like (assuming it isn't already sending emails) and then send the outstanding emails. — 3-14159265358979323846264, Jul 16 '15 at 19:14
Its not good implementation to let your application to send the emails, read about sp_send_dbmail in sql , and you can make a job that runs each 10 minutes to check if there still any email not send in a log table for example — Monah, Jul 16 '15 at 19:30
@HadiHassan why sending emails from application is not a good implementation? — Amr Elgarhy, Jul 16 '15 at 19:38
@AmrElgarhy I think you can face some problems using application when you want to send the email, that you can avoid in sql server which they are: either you will stuck with the application get freeze till the mail get sent, or you will get an error if the email was not sent successfully, or you need to add your code using threading to make your application to continue to work, while easily you have almost 90% task done on your sql server where the data stored and easy to access without round trip — Monah, Jul 16 '15 at 19:42
@HadiHassan I believe that sending emails from sql server is an option which we can use, but that doesn't mean that sending email from the application is a bad practice. and we can use asyn methods for sending email which will not freeze the application — Amr Elgarhy, Jul 16 '15 at 19:44
@AmrElgarhy you are right, its an option and it depends on the developer which method to use, but in my opinion better to let the sql to manage mails as long as the mail containing details from the database, and as you mentioned to use async ( threading) to let the application not to freeze, what about the failure of the send? how you will track the emails that was failed to be sent from application side? " I know you can manage to answer these questions but you need to do more code to achieve it while its straight forward in the database" — Monah, Jul 16 '15 at 19:47
@HadiHassan so we both agree that there is no better approach in sending email, it is all based on the application logic, so it debends — Amr Elgarhy, Jul 16 '15 at 19:49
@Trevor Pilley how can this question be re-worded to not be on hold as too broad? — kyle_13, Jul 17 '15 at 13:20

score 2 · Answer 1 · answered Jul 16 '15 at 19:14

2

What about using FileSystemWatcher class, using this class you can check if a new file created or modified.
And on the event of new file, you can open the file and read email from there.

answered Jul 16 '15 at 19:14

Amr Elgarhy

66,568
69
184
301

Hi @Amr Elgarhy, for FileSystemWatcher, would the application have to constantly be open (never closed), so the application could listen for directory changes? If so, how is this achieved? – kyle_13 Jul 16 '15 at 20:07
Yes, the application should be running, if you check the msdn page i added to the answer you will find a complete example how to do this. – Amr Elgarhy Jul 16 '15 at 20:14

score 1 · Answer 2 · edited May 23 '17 at 12:24

IMO a good approach would be to drop the console application entirely. This is something that SQL Server can be set up to handle.

You should just create a SQL Server Agent Job that imports the file on regular intervals (once a minute I think is the minimum frequency in 2005, but it's 10 seconds in 2008+). Just run a bulk import :

BULK INSERT mydatabase.mytable
   FROM 'c:\whatever\emailfile.csv'
   WITH
     (
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '\n',
        FIRE_TRIGGERS
      )

You could easily add a second step to the job to check the newly inserted data and use SQL Mail to send the emails. You could also simply set a trigger on the table(s) that receive the data to send an email every time a row is inserted. See: Send e-mail from a trigger.

If you have very high throughput here, and you really want a robust process and want to put a lot of work into it, I would suggest writing a file monitoring process (as a windows service) which puts new files into a queue to be processed.

How to have a console application constantly send emails?

2 Answers2