C# timer for database cyclic polling

Question

I'm not very familiar with C# so far, at least not enough to feel confident when choosing ways of doing something when it comes for the first time.

What could be the best way to organize SQL Server database timer based polling from my WPF application for controls updating?

Could you please share some thoughts on this issue or, maybe, code examples how it could be implemented? To what extent short is it possible (or reasonable) to make period of such polling? What can be dangerous to do?

Maybe there is one standard and correct way to do this?

Answer 1

You can use a SqlDependency object, although that ties your implementation very tightly with SQL Server 2005+.

Answer 2

Your requirements are very generally described, so I can respond only in generalizations:

Generally:

1. How many applications will be polling at the same time?
2. How large is the resultset?

Ten clients is not a lot, ten thousand is. Ten rows is small, a thousand rows gets big and slow. Answering these questions will help you figure out how to set the polling frequency.

You will need to balance other considerations such as UI responsiveness (do the data update asynchronously,) the need for fresh data, and knowledge of how often the database is updated, to determine the most efficient and effective design.

Answer 3

If you need an application-wide update, and it's not going to cause too much delay to do the update, the best option is to start off a background Thread that can run and post events to subscribers that the data has been refreshed (possibly first check to see if it has been altered using some specific logic too by adding a custom EventArgs to the event).

For example, create an instance of this class somewhere top-level (perhaps your main form code?):

using System;
using System.Threading;
using System.Windows.Forms;

public class PollerThread
{
    private Thread _thread;
    private SynchronizationContext _syncContext;

    public event EventHandler UpdateFinished;

    public PollerThread()
    {
        _syncContext = WindowsFormsSynchronizationContext.Current;

        ThreadStart threadStart = new ThreadStart(ThreadStartMethod);
        _thread = new Thread(threadStart);
        _thread.Name = "UpdateThread";
        _thread.IsBackground = true;
        _thread.Priority = System.Threading.ThreadPriority.Normal;
    }

    public void Start()
    {
        if ((_thread.ThreadState & ThreadState.Unstarted) == ThreadState.Unstarted)
            _thread.Start();
        else
            throw new Exception("Thread has already been started and may have completed already.");
    }

    public void ThreadStartMethod()
    {
        try
        {
            while (true)
            {
                // Go get the new data from the SQL server

                OnUpdateFinished(); // Notify all subscribers (on their own threads)
                Thread.Sleep(10000); // 10 sec wait before the next update
            }
        }
        catch (ThreadAbortException)
        {
            // The thread was aborted... ignore this exception if it's safe to do so
        }
    }

    protected virtual void OnUpdateFinished()
    {
        if (UpdateFinished != null)
        {
            SendOrPostCallback method = new SendOrPostCallback(
            delegate(object state)
            {
                UpdateFinished(this, EventArgs.Empty);
            });
            _syncContext.Send(method, null);
        }
    }
}

Subscribe each of the areas that need to respond to new updates to the UpdateFinished event. This will execute on the thread used to construct the PollerThread class.

This answer may sound a little loose, and applies more specifically to windows forms projects, but the usefulness really depends on your current implementation. At least you can build on this anyway. Hope it helps :)