How to continuously query a database and update the UI using a BackgroundWorker

Question

I have created a Table in SQL and it has only one Column, its value may change over time.

If my Column's value is greater than 10, I want to show a Label, if it is less than 10 the Label text should change its text.

I have tried this using BackgroundWorker, but it is not working as expected

The BackgroundWorker should never stop querying the database. If the value changes in the SQL table, then the value should change in the Label.
The code in the BackgroundWorker should always check the new value.

private bool sqlData()
{
    try
    {
        SqlConnection con = new SqlConnection(
            @"Server=NTN\TEST; Database=WinApp;User ID=sa; pwd=Rh#yt$33q");
        SqlCommand cmd = new SqlCommand("select * from Timer_test", con);
        con.Open();
        SqlDataReader rdr = cmd.ExecuteReader();
        if (rdr.HasRows)
        {
            while (rdr.Read())
            {
                value = (int)rdr["value"];
            }
        }
        con.Close();
        if (value > 10)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    catch
    {
        return false;
    }
}

private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
    while (sqlData())
    {
        timer1.Start();
        timer1.Enabled = true;
        Thread.Sleep(0100);
    }
}

private void backgroundWorker1_RunWorkerCompleted(object sender,
    RunWorkerCompletedEventArgs e)
{
    inpo.Text = "value less than 10";
    backgroundWorker1.RunWorkerAsync();
}

private void Home_Load(object sender, EventArgs e)
{
    backgroundWorker1.RunWorkerAsync();
}

You haven't shown where you are starting the worker initially with `backgroundWorker1.RunWorkerAsync();`, nor the definition of the `timer1`. Are you starting it in the `Form.Load` event? Is the `timer1` a `System.Windows.Forms.Timer`? — Theodor Zoulias, Nov 16 '22 at 10:40
Do you _really_ have to poll the database? What is writing to that db? Can you hook into that to get a push notification on value change? Is it possible to make the DB send notifications? — Fildor, Nov 16 '22 at 11:20
Just a small hint. Don't write `if (value > 10) { return true; } else { return false; }` as you can replace this with `return value > 10;`. — Enigmativity, Nov 16 '22 at 21:10

Jimi · Answer 1 · 2022-11-16T21:54:10.957

The code posted here requires a complete rework, since - overall - it's not functional and also contains a number of issues (as not disposing of the Connection / Command), the use of a Timer in the DoWork event is uncalled for, as also is calling Thread.Sleep() in that context.

You can remove the BackGroundWorker and run a Task instead (using a Timer here is also feasible, but you need a threaded Timer to query your database, since it may cause stuttering if this takes a long-ish time, then you need to marshal the update to the UI Thread etc., well, read the notes here: Why does invoking an event in a timer callback cause following code to be ignored?).

Defines an IProgress<int> delegate that will update the UI when it's called from the ConstantUpdate() procedure that queries your database and also pre-define the query and connection strings, so you can pass them to the Task
The CancellationTokenSource is used to signal the procedure that queries the database (ConstantUpdate()) to terminate its operations, when the Cancel() method is called.
Note that you can also pass a TimeSpan object to a Constructor of the CancellationTokenSource, to automatically cancel it after a set period of time

IProgress<int> statusUpdate = null;
CancellationTokenSource cts = null;
string statusQuery = "SELECT [value] FROM Timer_test";
string statusCon = @"[Your Connection String]";

protected override void OnShown(EventArgs e) {
    base.OnShown(e);
    statusUpdate = new Progress<int>((value) => Updater(value));

    cts = new CancellationTokenSource();
    Task.Run(() => ConstantUpdate(statusUpdate, statusCon, statusQuery, 1500, cts.Token));
}

private void Updater(int value) => 
    lblStatusUpdate.Text = value >= 10 ? $"Value is {value}" : "Value is less than 10";

The procedure that queries the database retrieves the value associated with the Column specified in the query, casts it to int (since this is what the IProgress<int> delegate is expecting) and calls the Report() method of the IProgress delegate, which in turn updates the UI

Task.Delay() is used to pause the loop for the specified milliseconds

The SqlConnection and SqlCommand are disposable objects, so let's declare these with using statements

Call the StopUpdate() method whenever needed (see below).

private async Task ConstantUpdate(IProgress<int> statusUpdate, string connection, string query, int intervalMS, CancellationToken stopToken = default)
{
    try {
        while (!stopToken.IsCancellationRequested) {
            var delay = Task.Delay(intervalMS, stopToken);
            using (var con = new SqlConnection(connection))
            using (var cmd = new SqlCommand(query, con)) {
                con.Open();
                statusUpdate.Report((int)await cmd.ExecuteScalarAsync(stopToken));
            }
            await delay;
        }
    }
    catch (TaskCanceledException) {
        // Log it, if required, otherwise you can also remove the try/catch block
        Debug.WriteLine("Update procedure cancelled");
    }
}

private void StopUpdate() {
    cts?.Cancel();
    cts?.Dispose();
    cts = null;
}

Now you can call the StopUpdate() method when needed. Of course, you need to call it when the Form closes, in case the task is still running. If it's already been stopped, nothing happens:

protected override void OnFormClosed(FormClosedEventArgs e) {
    StopUpdate();
    base.OnFormClosed(e);
}

How to continuously query a database and update the UI using a BackgroundWorker

1 Answers1