c# - Check the quantity in the database every 15 seconds

Question

I want to ask a question, I want to keep tracking the quantity in the database every 15 seconds or so.. It works fine, but the problem is it check the every column that have quantity less than 5, not single column of quantity that have less than 5. I have the database like the image below:

And from the image below, I minus the Quantity from the below image to the database (above image), so the quantity in the database (above image) is now 2, whenever the Quantity in first and second row (below image) are less than 5, it will show the box in the bottom right corner like the below image:

The problem is, either the quantity in the first or second row of the database still more than 5 or equal (for example: the quantity in the first row of the database is 2, but in the second row is 50), the box in the bottom right corner like above image does not show, it only shows when the quantity in first and second row in the database less than 5.

My question is: How do I show the box whenever either the quantity in the first or second row more than 5?

Here is the code that I am using:

System Manager class:

public static void GetQuantity()
{
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        string query = "SELECT [Quantity] FROM [Database]";

        connection.Open();

        using (OleDbCommand command = new OleDbCommand(query, connection))
        {
            using (OleDbDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    int quantity = (int)reader["Quantity"];

                    UserInformation.Quantity = Convert.ToDecimal(quantity);
                }

                reader.Close();
            }
        }

        connection.Close();
    }
}

public static void CheckQuantity(CustomToolTip _customToolTip, IWin32Window _window, int _x, int _y, int _duration)
{
    GetQuantity();

    string message = string.Empty;

    string productCode = string.Empty;

    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        string query = "SELECT [ProductCode] FROM [Database] WHERE [Quantity] = @Quantity ORDER BY [ProductCode] ASC";

        connection.Open();

        using (OleDbCommand command = new OleDbCommand(query, connection))
        {
            command.Parameters.Add("@Quantity", OleDbType.Decimal);
            command.Parameters["@Quantity"].Value = UserInformation.Quantity;

            using (OleDbDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    productCode = (string)reader["ProductCode"];

                    if (UserInformation.Quantity < 5)
                    {
                        message += "- Product Code: " + productCode + "
- Quantity: " + UserInformation.Quantity + "

";
                    }
                }

                if (message != string.Empty)
                {
                    SystemManager.SoundEffect(@"MediaSpeech Off.wav");

                    string _message1 = "The system has detected the following: 

";
                    string _message2 = "Have quantity less than 5.
Please update them immediately.";

                    if (UserInformation.Language == "Indonesian")
                    {
                        _message1 = "Program mendeteksi bahwa: 

";
                        _message2 = "Memiliki kuantitas kurang dari 5.
Perbarui segera.";
                    }

                    _customToolTip.Show(_message1 + message + _message2, _window, _x, _y, _duration);
                }

                reader.Close();
            }

        }

        connection.Close();
    }
}

User Information class:

public static decimal Quantity
        {
            get;
            set;
        }

Main System class: Here is where I called the box and minus the quantity from this class to the database:

int timeLeft = 15;

Timer _timer = new Timer();

void MainSystem_Load(object sender, EventArgs e)
{
   _timer.Interval = 1000;

   _timer.Tick += Timer_Tick;

   _timer.Start();
}

void Timer_Tick(object sender, EventArgs e)
 {
     timeLeft--;

     if (timeLeft == 0)
     {
         _timer.Stop();

         MessageBox.Show("The timer has been stopped");

         SystemManager.GetQuantity();

         if (UserInformation.Quantity < 5)
         {
             MessageBox.Show("The quantity less than 5");

             SystemManager.CheckQuantity(customToolTip1, this, _screen.Right, _screen.Bottom, 5000);

             timeLeft = 15;

             _timer.Start();
         }

         else if (UserInformation.Quantity >= 5)
         {
             MessageBox.Show("The quantity more than 5 or equal");

             timeLeft = 15;

             _timer.Start();
         }

         else
         {
             MessageBox.Show("Nothing");

             timeLeft = 15;

             _timer.Start();
         }

     }
 }

Your answer much appreciated!

Thank you so much!

Answer 1

In this loop:

while (reader.Read())
{
    int quantity = (int)reader["Quantity"];

    UserInformation.Quantity = Convert.ToDecimal(quantity);
}

you are overwriting the value of UserInformation.Quantity over and over until the last row, and it keeps whatever value is in the last row.

You haven't shown us how your UserInformation class is defined, so it's hard to show you how to modify it, but basically, you should only be querying for rows that are applicable:

string query = "SELECT [Product Code], [Quantity] FROM [Database] " +
               "WHERE [Quantity] < 5";

Build up a list of the results:

var lowProducts = new List<ProductInfo>();

while (reader.Read())
{
    int quantity = (int)reader["Quantity"];
    string code = (string)reader["Product Code"];

    lowProducts.Add(new ProductInfo(code, quantity));    
}

UserInformation.LowProducts = lowProducts;

And then you can check whether LowProducts has any items in it:

if (UserInformation.LowProducts.Any())
{
    MessageBox.Show("Some products are running low.");

    SystemManager.CheckQuantity(customToolTip1, this, _screen.Right,
                               _screen.Bottom, 5000);
    timeLeft = 15;
    _timer.Start();
}

Edit: to answer your question in the comments, one simple way you could implement ProductInfo is:

class ProductInfo
{
    public string Code { get; private set; }
    public int Quantity { get; private set; }

    public ProductInfo(string code, int quantity)
    {
        Code = code;
        Quantity = quantity;
    }
}