c# - Quantity tracking in the database

Question

This question is similar like my post before: Previous post, but I made this question because I am discover a new problem and could not solve this myself.

Here is the case: I want to check if the Quantity in the database is less than 5 and show the message, but the problem is when there are two datas in the database and the Quantity from the first one is 3 and the second one is 2, it only show the message and it is only picked the lowest Quantity value in the database. But when the Quantity of datas in the database are same, it will show the message where Quantity in the database is less than 5.

Here is the image of the database:

Here is the image of when there are two datas in the database and the Quantity for both of it are same and the message itself:

Here is the image of when there are two datas in the database and the Quantity for both of it are different and the message itself:

As you can see from the above image, the message shows both datas when the Quantity for both datas are same.

How can I solve this?

Here is the code that I am using (With help from @JLRishe from the previous post):

SystemManager class:

public static void GetProductInfo()
        {
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                string query = "SELECT [ProductCode], [Quantity] FROM [Database] WHERE [Quantity] < 5";

                conn.Open();

                using (OleDbCommand command = new OleDbCommand(query, conn))
                {
                    using (OleDbDataReader reader = command.ExecuteReader())
                    {
                        var lowQuantity = new List<ProductInfo>();

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

                            lowQuantity.Add(new ProductInfo(productCode, quantity));
                        }

                        UserInformation.LowQuantity = lowQuantity;
                    }
                }

            }
        }

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

            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 = ProductInfo.Quantity;

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

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

";
                            }*/

                            if (UserInformation.LowQuantity.Any())
                            {
                                message += "- Product Code: " + productCode + "
- Quantity: " + ProductInfo.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.";

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

                        reader.Close();
                    }

                }

                connection.Close();
            }
        }

UserInformation class:

public static IEnumerable<ProductInfo> LowQuantity
        {
            get;
            set;
        }

ProductInfo class:

public static string Code
        {
            get;
            set;
        }

        public static int Quantity
        {
            get;
            set;
        }

        public ProductInfo(string _code, int _quantity)
        {
            Code = _code;
            Quantity = _quantity;
        }

MainSystem form: (Here is where I execute the code)

Timer _timer = new Timer();

int timeLeft = 15;

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

            _timer.Tick += Timer_Tick;

            _timer.Start();
         }

void Timer_Tick(object sender, EventArgs e)
         {
             this.textBox4.Text = DateTime.Now.ToString("dd - MMM - yyyy hh:mm:ss tt");

             timeLeft--;

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

                 SystemManager.GetProductInfo();

                 if (UserInformation.LowQuantity.Any())
                 {
                     SystemManager.CheckQuantity(customToolTip1, this, _screen.Right, _screen.Bottom, 5000);

                     timeLeft = 15;

                     _timer.Start();
                 }

                 else
                 {
                     timeLeft = 15;

                     _timer.Start();
                 }

             }
         }

Any help would be much appreciated!

Thank you so much!

Sorry for long posting by the way.

Answer 1

In your CheckQuantity() method, you are:

1. Only querying items that have the same quantity of the first ProductInfo that you're constructing.
2. Displaying the quantity from that single ProductInfo instead of the value you are querying from the database.

Instead, this should work better:

public static void CheckQuantity(CustomToolTip _customToolTip, IWin32Window _window, 
                                 int _x, int _y, int _duration)
{
    string message = string.Empty;

    string productCode = string.Empty;
    int quantity;

    string query =
        "SELECT [ProductCode], [Quantity] FROM [Database] " +
        "WHERE [Quantity] = < 5 ORDER BY [ProductCode] ASC";
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    using (OleDbCommand command = new OleDbCommand(query, connection))
    using (OleDbDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            productCode = (string)reader["ProductCode"];
            quantity = (int)reader["Quantity"];

            message += "- Product Code: " + productCode + 
                       "
- Quantity: " + quantity + "

";
        }
        reader.Close();
        connection.Close();
    }

    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.";

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

I think your code probably needs some reorganization. You have one query to check whether the products are below a certain quantity, and then you're querying them all over again to re-obtain information that you have already retrieved. I suggest putting some thought into the matter and finding a way to do this with just one query.