-2

There is an ASP.net C# web application through which we can get the recipient emails, time zone and their smtp server details in to the database.I have two requirements: 1. Consider a table in the database. When ever there is a change in the table, an email has to be sent. It is OK if we can constantly check the database every 5 minutes. It would be great if we can send it instantly but a delay is fine. 2. Sending emails automatically at 12 AM at their respective time zone.

I m familiar with C# programming. But kind of new to automatic scheduling stuff. This could sound like a basic question but it would be great if you can help. What is the best way to implement this - Web api or web services or WCF or windows services or combination of web api and task scheduler? Please let me know your thoughts. Also a small tip on how to implement this would be great.

Lalli86
  • 1
  • 3
  • Periodic background tasks are best performed by Windows Services or scheduled Console Applications. – David Dec 28 '15 at 15:21
  • why don't you have a trigger setup on the table so that anything that changes on that particular table you have an email automatically generated and sent out.. this is actually a very simple task. don't waste time pooling the database every 5 minutes for something so basic. Or you could create a job that runs at 12am based on a timestamp checking a column that has a timedate field of modified etc.... still very simple task to accomplish – MethodMan Dec 28 '15 at 15:21
  • @MethodMan A trigger in a database that executes a script/CLR? Seems a bit overkill, to me. – Rob Dec 28 '15 at 15:24
  • I need to send a report with an attached PDF at 12AM. If it is the email based on change in the database it can just be an email with small body. So windows services or console is the best ? – Lalli86 Dec 28 '15 at 15:27
  • @Rob if the table does not change very often that's not overkill in my opinion also running a check on the database every 5 mins some would view as overkill too.. however if the OP decides on that route that's still a very simple task to accomplish.. the only thing that the OP would need to do is add a column or 2 CreatedTime and UpdatedTime etc... a scheduled job could still handle all of this as well – MethodMan Dec 28 '15 at 15:27
  • @Lalli86 I would suggest that you do some research on this this site is not a code factory site also what have you even attempted on your own..? also `ITextSharp` is a pretty easy tool to use as well when creating PDF's – MethodMan Dec 28 '15 at 15:28
  • The table might not change that regularly. It will definitely change atleast once in an hour. – Lalli86 Dec 28 '15 at 15:29
  • @MethodMan I have a windows service that i wrote which will ping the database every 5 minutes and send the emaill also same with the scheduled emails. But, we wanted to improve the method. Thats what i have done as of now. – Lalli86 Dec 28 '15 at 15:30
  • @Lalli86 checking the database once an hour is not going to cause any excessive overhead so that's a good start .. in the meantime I think you should start coding the logic for this and test it out in a ConsoleApp to test your logic and process.. – MethodMan Dec 28 '15 at 15:32
  • can you perhaps show the method so that we can see if there are any deficiencies in your logic and or method(s) of processing.. – MethodMan Dec 28 '15 at 15:33
  • @Methodman: the poolingitself needs to be done once in 5 minutes surely. – Lalli86 Dec 28 '15 at 15:35
  • As I understand it.. this service needs to be up, all the time, regardless of any user state or Web State (up or down, or recycled, etc).. So IMO its best to keep this guy as a windows service that can be watched.. Having a dedicated windows service means that you do not have to background thread anything and piggyback off another App in the mix of things – DaveTheRave Dec 28 '15 at 15:54
  • @DaveTheRave: Thats the exactly how i thought as well Dave but if there are like 1000 emails to be sent at the same time, i dont know if windows service is a better option. Let me know what you think. – Lalli86 Dec 28 '15 at 15:58
  • You could then implement a trigger on the table to write to a queue table when things get added or changed.. Then consume the queue table to process the emails.. – DaveTheRave Dec 28 '15 at 15:59
  • An interesting approach could be this: If you have control / visibility in the ASP side on when this table is going to be changed updated.. then.. after you update/insert it.. you can fire off a background thread that sends the email for this particular row. You could add a column in that table that statuses the state of the notification. In this case.. set it to notified (as you are sending immediate email notification through ASP web side). – DaveTheRave Dec 28 '15 at 16:06
  • Then in your trigger.. only move to a queue table those records that get changed that have not been notified yet.. and let that windows service handle those – DaveTheRave Dec 28 '15 at 16:07
  • @DaveTheRave: Wow .. That is REALLY interesting approach. I get the data in to the database through another webapi module. Thank you. I will see if i can do some thing with that. – Lalli86 Dec 28 '15 at 16:08

1 Answers1

1

You have an option of setting up trigger but I hate that approach as it will add overhead to your table tow insertion and not actually needed. I think you are in the right path by thinking about pooling. There is a nice little library in .net called hangfire which I find to be very useful to do scheduled task. It has pretty sophisticated reporting and almost all the time works really well. You can give it a try. But if you want to control things better writing a small windows service don't be that bad either. I think doing websevice either using webapi or wcf is a bit overkill here and might not fit purpose.

qamar
  • 1,437
  • 1
  • 9
  • 12
  • thank you. If i have to implement web api method. I guess the only way of doing it is in combination with a console app/ windows service to send a post request. Is that correct ? and this windows service should also check the database every 5 mins. Is this why you said its kind of overkill ? did i understand that right ? – Lalli86 Dec 28 '15 at 15:34
  • Yes and no. Console app with combination of web API method will execute the email sending instructions immediately and might clog your server. All I wanted to say you should have some kind of queue system ( in the form of hangfire job or even a msmq queue) where you can just drop a message to send an email and be done with it. Now some other service can read from that queue and send email one by one as a background process. Does this sound clear – qamar Dec 28 '15 at 15:38
  • Yes Qamar. I see your point that it could clog the server. How about the scheduled emails then that are supposed to be sent at 12AM. – Lalli86 Dec 28 '15 at 15:57
  • Please read through hangfire documentation. It can take a cron expression where you should be able to set various rules like execute email sending job at 12am everyday, execute another one every Friday etc. The reason I am trying to convince you for using some ready made solution like hangfire cause no point doing something someone else does well. You should always keep your IIS clean should not make it busy at any time by calling some API endpoint to send out emails – qamar Dec 28 '15 at 16:00