Discuss "Scheduling app, better on database or on app" in the .Net Development forum.

Hey guys its Peebman2000, semi-beginner programmer. I may be assigned to build a .net scheduling application for a client. I'm in the process now of researching and practicing code on doing this.

I've never built a scheduling app, but what they want so far as I know is to schedule appointments for hearings and obviously it would be stored in a sql database. When its the day and time of the appointment an email will be sent to the user advising or reminding them of the appointment.

My thought is I would just constantly loop through the database and when its a scheduled time and date, shoot the email to the user. But I start thinking about I/O, it hitting the database so much.

Then i thought of trying to loop it through the database in a stored procedure, but i'm not strong in coding loops in stored procedure, so I'm wanting more to just do it on the app side.

Anyway, this app is new for me; which is the better way to go in writing an app like this. Loop through the database for dates and times on the app side or on the database side?

Let me know and of course once I official start on this, I may have more question down the line.

Thanks for any help and suggestions.

From the app side, you could implement AJAX to query the database on a specified time interval. A constant loop would probably cause issues.

__________________
jmurrayhead
If you agree with me... click the icon!
If my post solved your problem, click the button in the lower right-hand corner of the post.

Join our Folding team: DeveloperBarn Folding

Thanks jmurrayhead for the reply, so it sounds like I need to do this on the database end. Sorry dude I'm not to familiar with AJax, I've kind of used the scriptmanager control some, but thats it.

I'll research on doing loops in a query and see what I come up with to practice on. You may see me post something again later down the line when I really start this app.

Thanks

Well now is a better time than never to start learning AJAX However, this will only work if someone has the web site open. Without the site being open, there isn't really much you can do in ASP.Net aside from having a scheduled task or a Windows Server call the web site at any given time.

Not sure about MS SQL, but in Oracle I would consider doing it this way (there is probably a parallel in MS SQL as well, I just haven't worked with it in quite some time).

Create a scheduled job on the DB Server to run about every morning about 0100. I this job (which is nothing more that a stored procedure), SELECT from the scheduling table those events that are due in the next 24-hours and send the e-mail. Once working, you can set it and forget it (tho consider sending a admin an e-mail if the sproc fails in some way).

You can reduce the interval to whatever you want, even down to every 5 minutes or so -- we have sprocs that do this 24/7 to collect 'real-time' data.

OTOH, if you have a requirement to send the e-mail at a specific date and time and would prefer not to have such demonic sproc, the sproc could, upon completion of sending the e-mail, query the schedule table again and re-schedule itself for when the next e-mail is due to be sent. I'd consider using a 10 minute window or so however.

I'd go with Wolffy's solution, Sql server is very capable of emailing

Hey thanks for the replies Wolffy and Lewy. I was not aware that SQL is capable of doing emails.

Anyway, thanks for the knowledge, but I was told yesterday I wouldn't be building the app. The client decided to go with a Can application from Prolaw, that is programmed in Delphi code and its a client/server app. I know nothing of delphi, didn't teach in college nor told about in college.

Anyway thanks again.