How to run an SQL stored procedure through C# at a specific time?

Question

I am building a website now for a library and i need to check every day at the same time if there are people who need to return their books in the next five days and to send them a reminder via email.

My question is what will be the correct way to do that?

What i need to accomplish is when the specific time of day comes i need to run an sql stored procedure and check either through visual studio 2010 or any other way if the stored procedure has returned any results to which i need to email.

Is there a way to maybe check the system time constantly on C# and not as a triggered event?

Answer 1

I'd image the simplest way to do this would be by creating a new job in the SQL Server Agent to call your stored procedure every day, rather than trying to achieve this in an application you've written.

See the following link for more information on creating SQL Jobs

Edit - Sorry, I really shouldn't try to answer SO posts while doing other work too. I completely missed the fact you wanted to send off emails, and got it into my head that you wanted to report overdue records on screen. Duh.

To send off overdue emails at a certain time every day, i'd guess a windows service would certainly do the job, but seems a little over the top, and you'd need to have full control over the server it's running on.

You could instead set up a scheduled task to call a sendOverdueMail.aspx web page every day to do the work perhaps? Like this

Obviously there is the SQL Server Agent emailing option too, although I'm not sure how much control you'd have over the format of the emails that would produce.

Finally, this tutorial steps through creating a scheduled task type effect, by hooking into cache expiry events on an ASP.net server, which then allow you to call your emailing code.

private const string DummyCacheItemKey = "GagaGuguGigi";
protected void Application_Start(Object sender, EventArgs e)
{
    RegisterCacheEntry();
}

private bool RegisterCacheEntry()
{ 
    if( null != HttpContext.Current.Cache[ DummyCacheItemKey ] ) return false;

        HttpContext.Current.Cache.Add( DummyCacheItemKey, "Test", null, 
        DateTime.MaxValue, TimeSpan.FromMinutes(1), 
        CacheItemPriority.Normal,
        new CacheItemRemovedCallback( CacheItemRemovedCallback ) );

    return true;
}


public void CacheItemRemovedCallback( string key, 
        object value, CacheItemRemovedReason reason)
{
    Debug.WriteLine("Cache item callback: " + DateTime.Now.ToString() );

    // send reminder emails here
    DoWork();
}

Answer 2

to compare dates, use the DATEDIFF function in T-SQL. for example, you have a table with Row ID, Book Name, student name, Date borrowed and Due Date. Both Date borrowed and due date has DateTime types.

to query for books due for x amount of days; which is 5 days according to your specification, query like this

SELECT RowID, BookName, StudentName, DateBorrowed, DueDate FROM dbo.Borrowers WHERE DATEDIFF(day, DateBorrowed, DueDate) >= 5

this will query all books due for reminding.

Then use a SQL query job to trigger an action that will append to another table that will contain an Email list of Students who need reminding. This Table can be read from a Windows Service that will use that list as an email Queue. Alternatively, you can configure SQL to send emails directly.

Answer 3

Use windows scheduled tasks to call the website page that does the task of running the stored procedure and sending the mails.

There is no specific method in C# though which will give you such results.

If you do not have access to the scheduled tasks or you are unable to run a windows service, i.e. you have a shared hosting, there is yet another alternative.

There are services available to monitor the uptime ofy our site. They make requests on regular basis. You can use this request to do your tasks. Some of the links given here

Site uptime

Pingdom

I have used them several times and they are pretty reliable. Above all, they do not require you to have administrator access on system to perform scheduled tasks.