Checking the count in a while loop constantly throws messagebox when it shouldnt

Question

Goal:

I am aiming to notify the user then a new record is detected in MySQL table.

What I am using:

I am using Windows form and MySQL.

Also, within the code I am using "SELECT COUNT(*)" and store it into a variable to check the previous count and the new count.

What I currently have coded:

  public Dashboard()
    {
        InitializeComponent();

        //Timer to run function every 10 seconds
        System.Timers.Timer timer = new System.Timers.Timer();
        timer.Interval = 10000;
        timer.Elapsed += Notification;
        timer.Start();
    }

    //Track changes in MySQL table - and if new records appear then notify the user
    private void Notification(object sender, System.Timers.ElapsedEventArgs e)
    {
        //MySQL connection
        using(var conn = new MySqlConnection(ConnectionString.ConnString))
        {
            conn.Open();
            //Create command
            using(var cmd = new MySqlCommand("SELECT COUNT(*) from tester", conn))
            {
                var dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    //Current Number
                    int count = dr.GetInt32(0);

                    //Previous number
                    int prev_numb = int.MinValue;

                    while (true)
                    {
                        //If count is not same as previous number
                        if(count != prev_numb)
                        {
                            //Notify user
                            MessageBox.Show("New Record!");
                            //Set prev_numb to new numb
                            prev_numb = count;
                        }
                    }
                }
            }
        }
    }

Problem:

The first part of the code works fine (Runs the function every 10 seconds).

But the messagbox keeps prompting every 10 seconds, even if the count has not changed... why is this happening? And what needs amending within the code to work as desired?

Resources:

Repeat function every n minutes https://stackoverflow.com/a/34204679/12485722

Loop: https://stackoverflow.com/a/13051703/12485722

You might want to change the scope of ```prev_numb```. ```Count``` hasn't changed, but ```prev_numb``` has, is there any reason for setting it to ```int.MinValue```? Also, the ```while(true)``` loop seems to be unnecessary. — devsmn, Jun 12 '20 at 13:16

Joel Coehoorn · Accepted Answer · 2020-06-12T13:42:39.343

1

The trick is you don't want a loop in the method at all. The timer handles that for you. Additionally, the count needs to be a class member, rather than scoped to the method.

private int RecordCount = int.MinValue;

private void Notification(object sender, System.Timers.ElapsedEventArgs e)
{
    int newCount;
    using(var conn = new MySqlConnection(ConnectionString.ConnString))
    using(var cmd = new MySqlCommand("SELECT COUNT(*) from tester", conn))
    {
        conn.Open();
        newCount = (int)cmd.ExecuteScalar();
    }

    if (RecordCount == int.MinValue) RecordCount = newCount;
    int diff = newCount - RecordCount;
    if (diff != 0)
    {
        RecordCount = newCount;
        MessageBox.Show($"{diff} new records!");
    }
}

I'm also a little nervous about putting a MessageBox(), which blocks for user input, inside a timer event. I might shift into a Task() (or similar, I'd have to think about it more) instead.

edited Jun 12 '20 at 13:42

answered Jun 12 '20 at 13:32

Joel Coehoorn

399,467
113
570
794

Hi - just got this error on cmd.executescalar()... `System.InvalidCastException: 'Specified cast is not valid.'` Regarding the messaegbox().. It's only for test purposes :) – Eduards Jun 12 '20 at 13:54
the only way I can think to get that exception is if the table doesn't exist or you don't have permission to query it. Otherwise, you'll still get a `0`, even if there aren't rows. – Joel Coehoorn Jun 12 '20 at 13:56
It works! I used this `newCount = Convert.ToInt32(cmd.ExecuteScalar());` – Eduards Jun 12 '20 at 14:08
that sounds like somehow the MySql `count()` function is returning a string, which would be very strange, but it's MySql so I guess I shouldn't be too suprised. – Joel Coehoorn Jun 12 '20 at 14:11
But appreciate your answer! Really helped me out :D – Eduards Jun 12 '20 at 14:25

Checking the count in a while loop constantly throws messagebox when it shouldnt

1 Answers1