Creating a c# windows service to poll a database

Question

I am wanting to write a service that polls a database and performs an operation depending on the data being brought back.

I am not sure what is the best way of doing this, I can find a few blogs about it and this stack overflow question Polling Service - C#. However I am wary that they are all quite old and possibly out of date.

Can anyone advise me on the current advice or best practices (if there are any) on doing something like this or point me in the direction of a more recent blog post about this. From what I can gather either using a timer or tpl tasks are two potential ways of doing this.

If timers are still suggested then how will they work when the service is stopped because the operations I intend for these services to do could potentially take 30+ minutes, this is why I say use tasks because I can use a task cancellation token but these throw exceptions when cancelled (correct me if I am wrong) and I don't think I really want that behaviour (although correct me if you think there is a reason I will want that).

Sorry that I may be asking quite a lot in a single question but I'm not entirely sure myself what I am asking.

Answer 1

Go with a Windows service for this. Using a scheduled task is not a bad idea per se, but since you said the polls can occur every 2 minutes then you are probably better off going with the service. The service will allow you to maintain state between polls and you would have more control over the timing of the polls as well. You said the operation might take 30+ minutes once it is kicked off so maybe you would want to defer polls until the operation complete. That is a bit easier to do when the logic is ran as a service.

In the end it does not really matter what mechanism you use to generate the polls. You could use a timer or a dedicated thread/task that sleeps or whatever. Personally, I find the dedicated thread/task easier to work with than a timer for these kinds of things because it is easier to control the polling interval. Also, you should definitely use the cooperative cancellation mechanism provided with the TPL. It does not necessary throw exceptions. It only does so if you call ThrowIfCancellationRequested. You can use IsCancellationRequested instead to just check the cancellation token's state.

Here is a very generic template you might use to get started.

public class YourService : ServiceBase
{
  private CancellationTokenSource cts = new CancellationTokenSource();
  private Task mainTask = null;

  protected override void OnStart(string[] args)
  {
    mainTask = new Task(Poll, cts.Token, TaskCreationOptions.LongRunning);
    mainTask.Start();
  }

  protected override void OnStop()
  {
    cts.Cancel();
    mainTask.Wait();
  }

  private void Poll()
  {
    CancellationToken cancellation = cts.Token;
    TimeSpan interval = TimeSpan.Zero;
    while (!cancellation.WaitHandle.WaitOne(interval))
    {
      try 
      {
        // Put your code to poll here.
        // Occasionally check the cancellation state.
        if (cancellation.IsCancellationRequested)
        {
          break;
        }
        interval = WaitAfterSuccessInterval;
      }
      catch (Exception caught)
      {
        // Log the exception.
        interval = WaitAfterErrorInterval;
      }
    }
  }
}

Like I said, I normally use a dedicated thread/task instead of a timer. I do this because my polling interval is almost never constant. I usually start slowing the polls down if a transient error is detected (like network or server availability issues) that way my log file does not fill up with the same error message over and over again in rapid succession.

Answer 2

You have a few options. To start with what could be essentially the easiest option, you could decide to create your app as a console application and run the executable as a task in the Windows Task Scheduler. All you would need to do is assign your executable as the program to start in the task and have the task scheduler handle the timing interval for you. This is probably the preferred way if you don't care about state and will prevent you from having to worry about creating and managing a windows service if you don't really need to. See the following link for how to use the scheduler.

Windows Task Scheduler

The next way you could do this would be to create a windows service and in that service use a timer, specifically System.Timers.Timer. Essentially you would set the timer interval to the amount of time you would like to have pass before you run your process. Then you would sign up for the timers tick event which would fire every time that interval occurred. In this event you would essentially have the process you would like to run; this could kick off addition threads if you would like. Then after that initial setup you would just call the timers Start() function or set the Enabled property to True to start the timer. A good example of what this would look like can be found in the example on MSDN page describing the object. There are plenty of tutorials out there that show how to set up a windows service so I won't bother with going into that specifically.

MSDN: System.Timers.Timer

Finally and more complex would be to set up a windows service that listens for a SqlDependency. This technique is useful if things can occur in the database outside your application yet you need to be made aware of it in your application or some other service. The following link has a good tutorial on how to set up a SqlDependency in an application.

Using SqlDependency To Monitor SQL Database Changes

Two things I would like to point out from your original post that are not specific to the question you had.

1. If you are writing a true windows service you don't want the service to stop. The service should be running constantly and if an exception does occur it should be handled appropriately and not stop the service.

2. A cancellation token doesn't have to throw an exception; simply not calling ThrowIfCancellationRequested() will cause the exception not to be thrown or if this is a CancellationTokenSource set the argument to false on the Cancel method then subsequently check the token to see if cancellation is requested in your threads and return out of the thread gracefully if so.

For example:

    CancellationTokenSource cts = new CancellationTokenSource();
    ParallelOptions options = new ParallelOptions
    {
        CancellationToken = cts.Token
    };
    Parallel.ForEach(data, options, i =>
    {
        try
        {
            if (cts.IsCancellationRequested) return;

            //do stuff

        }
        catch (Exception ex)
        {
            cts.Cancel(false);
        }
    });