数据库中的数量跟踪 [英] Quantity tracking in the database
问题描述
这个问题类似我之前的帖子：上一篇文章，但我提出这个问题，因为我发现一个新的问题，无法自己解决这个问题。
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.
在这种情况下：我想检查数据库中的 Quantity 是否小于5，消息，但问题是当数据库中有两个数据，并且第一个数据的数量为3，第二个数据为2时，它只显示消息，只选择数据库中最低的 Quantity 值。但是当数据库中的数量相同时，它将显示消息，其中数据库中的 Quantity
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.
下面是数据库中有两个数据时的图片， Quantity 的两个数据相同，并且消息本身：
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:
以下是数据库中有两个数据的图片， Quantity 两者都是不同的和消息本身：
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.
如何解决这个问题？
下面是我使用的代码（在上一篇文章的@JLRishe的帮助下）：
Here is the code that I am using (With help from @JLRishe from the previous post):
SystemManager类： strong>
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 + "\n- Quantity: " + ProductInfo.Quantity + "\n\n";
}*/
if (UserInformation.LowQuantity.Any())
{
message += "- Product Code: " + productCode + "\n- Quantity: " + ProductInfo.Quantity + "\n\n";
}
}
if (message != string.Empty)
{
SystemManager.SoundEffect(@"\Media\Speech Off.wav");
string _message1 = "The system has detected the following: \n\n";
string _message2 = "Have quantity less than 5.\nPlease update them immediately.";
_customToolTip.Show(_message1 + message + _message2, _window, _x, _y, _duration);
}
reader.Close();
}
}
connection.Close();
}
}
UserInformation class：
UserInformation class:
public static IEnumerable<ProductInfo> LowQuantity
{
get;
set;
}
ProductInfo类：
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： >（这里是我执行代码的地方）
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();
}
}
}
非常感谢！
非常感谢你！
p>
推荐答案
在您的CheckQuantity（）方法中，您是：
In your CheckQuantity() method, you are:
- 仅查询与您构建的第一个ProductInfo具有相同数量的项目。
- 显示来自该单个ProductInfo的数量，而不是从数据库。
而应该更好：
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 +
"\n- Quantity: " + quantity + "\n\n";
}
reader.Close();
connection.Close();
}
if (message != string.Empty)
{
SystemManager.SoundEffect(@"\Media\Speech Off.wav");
string _message1 = "The system has detected the following: \n\n";
string _message2 = "Have quantity less than 5.\nPlease 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.
这篇关于数据库中的数量跟踪的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
