Here is my problem , I am coding a C# application that will log in to a remote SQL server 2008 [Express edition to be precise] and fetch data from the database table (say foo) periodically[after each fetch the SQL client connection is closed ] .
How can I do this process periodically with a time interval like 60 seconds or so (user definable). ??
with a time interval like 60 seconds or so
60s is rather less then things like Windows Task Scheduler is set up for – it can do it via the advanced settings (once per day and repeat every one minute.
One open question is what to do if a run of the task takes longer than the scheduler interval? Can two runs overlap or not? Assuming they can overlap something as simple as:
var timer = new System.Threading.Timer(method, null,
TimeSpan.Zero, // Start now.
TimeSpan.FromSeconds(60));
where method is a delegate of type TimerCallback: takes an object state (null passed above) and returns nothing.
If you do not want to allow calls to overlap should the next task be skipped or delayed, assuming skipped:
var object locker = new object();
var timer = new System.Threading.Timer(_ => {
bool entered = Monitor.TryEnter(locker);
try {
if (entered) {
method(null);
}
} finally {
if (entered) {
Monitor.Exit(locker);
}
}
}, null, TimeSpan.Zero, TimeSpan.FromSeconds(60));
(where, again, method is the worker code).
Instead of System.Threading.Timer I'd use System.Timers.Timer which can be configured to be a "one shot" timer using the AutoReset property:
System.Timers.Timer sqlTimer = new System.Timers.Timer();
sqlTimer.Interval = 60000; // One minute
sqlTimer.AutoReset = false; // Fire only once
sqlTimer.Elapsed = TimerEvent; // Assign event for timer
sqlTimer.Start(); // Start the timer
In that case, as the timer fires only once, you don't need to worry about locking or synchronization:
private void TimerEvent(object source, ElapsedEventArgs e)
{
// Do your SQL-stuff
// Restart the timer if you want
sqlTimer.Start();
}
It must be noted that this timer as well runs in a separate thread, so the TimerEvent event will not be called in the context of the thread that created the timer. This is important to know in case you need to update any UI elements.
Please note that this does not work for .NET Compact Framework, as it doesn't know the System.Timers namespace - you'll have to use Richard's solution in that case.
Why not use the built in schedulers within SQL Server Agent, or the one built into Windows? Why reinvent the wheel?
From MSDN
Benefits of SQL Server Agent SQL Server Agent uses SQL Server to store job information. Jobs contain one or more job steps. Each step contains its own task, for example, backing up a database.
SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand. For example, if you want to back up all the company servers every weekday after hours, you can automate this task. Schedule the backup to run after 22:00 Monday through Friday; if the backup encounters a problem, SQL Server Agent can record the event and notify you.
Or see this :
Is it possible to use Windows 7 Task scheduler in own application
or
How might I schedule a C# Windows Service to perform a task daily?
