C # timer for polling database

Question

I am not very familiar with C # yet, at least not enough to feel confident when you choose ways to do something, when this will happen for the first time.

What could be the best way to organize a timer-based survey of SQL Server databases from my WPF application to update controls?

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

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

Answer 1

If you need an update for the entire application, and this does not cause too much delay for the update, the best option is to start the Thread background, which can trigger and send events to subscribers who have data (perhaps first check if it has changed using any then a certain logic, adding a custom EventArgs to the event).

For example, create an instance of this class somewhere at the 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 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); } } } 

Sign each of the areas that should respond to new updates in the UpdateFinished event. This will be executed in the thread used to build the PollerThread class.

This answer may seem a bit loose and more specifically relates to window designs, but the usefulness really depends on your current implementation. At least you can build this anyway. Hope this helps :)

Answer 2

You can use the SqlDependency object, although this links your implementation with SQL Server 2005+ very strongly.

Answer 3

Your requirements are mostly described, so I can only answer generalizations:

Generally:

• How many applications will poll at a time?
• How big is the result set?

Ten customers are not many, ten thousand. Ten lines are few, thousands of rows become large and slow. The answer to these questions will help you determine how to set the polling frequency.

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