坚持从数据库读取数据 [英] Keep stuck at the reading the data from database
问题描述
我想用我在运行程序时给出的值减去数据库中的数据.一切正常，但我一直停留在 newVal.我已经这样做了，但是 newVal 一直出现 0 (因为我声明了 decimal newVal = 0，但是在这个问题上，我只是使用 decimal newVal;).还有两个问题:如果我把 newVal = ... 移到顶部，那是没用的，因为 newVal 中的计算之一是从数据库中读取数据(因为我想要数据库减去在运行程序时给出的新值，需要 dReader = cmd.ExecuteReader();)，但是如果我把 newVal 放在底部读完数据，也是没用的，因为我设置了Quantity = ?，?的值是newVal..好了，这里是代码:
I would like to minus the data from the database with the value that I give when I run the program. Everything works, but I keep stuck at the newVal. I already did it like this, but the newVal keep appear 0 (because I declared decimal newVal = 0, but on this question, I just used decimal newVal;). Two more problems: if I move the newVal = ... to the top, it is useless, because one of the calculations in the newVal is reading data from the database (since I want database minus with the new value given when i run the program, required dReader = cmd.ExecuteReader();), but if I put the newVal at the bottom after reading data, it is useless as well, because I set the Quantity = ? and the value of ? is newVal.. Well, here is the code:
private void AddObjects(object sender, EventArgs e, Form theForm)
{
button1.Visible = true;
textBoxQuantityContainer = new List<NumericUpDown>();
textBoxCodeContainer = new List<NumericTextBox>();
textBoxDescContainer = new List<TextBox>();
textBoxSubTotalContainer = new List<TextBox>();
textBoxTotalContainer = new List<TextBox>();
textBoxAllTotalContainer = new TextBox();
OleDbDataReader dReader;
OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT [Code] FROM [Seranne]", conn);
dReader = cmd.ExecuteReader();
AutoCompleteStringCollection codesCollection = new AutoCompleteStringCollection();
while (dReader.Read())
{
string numString = dReader[0].ToString().PadLeft(4, '0');
codesCollection.Add(numString);
}
dReader.Close();
conn.Close();
if (firstForm.comboBox1.SelectedIndex == 0)
{
label1.Text = "Code:";
label1.Location = new Point(60, 125);
label2.Text = "Welcome to the Selling System.";
label2.Location = new Point(600, 30);
label3.Text = "Quantity:";
label3.Location = new Point(155, 125);
label4.Text = "Description:";
label4.Location = new Point(580, 125);
label5.Text = "Sub Total on Rp:";
label5.Location = new Point(1020, 125);
label6.Text = "Total on Rp:";
label6.Location = new Point(1210, 125);
label7.Text = "Total on Rp:";
label7.Location = new Point(1080, 580);
}
else if (firstForm.comboBox1.SelectedIndex == 1)
{
label1.Text = "Kode:";
label1.Location = new Point(60, 125);
label2.Text = "Selamat datang di Selling System.";
label2.Location = new Point(600, 30);
label3.Text = "Banyaknya:";
label3.Location = new Point(145, 125);
label4.Text = "Keterangan:";
label4.Location = new Point(580, 125);
label5.Text = "Sub Total di Rp:";
label5.Location = new Point(1020, 125);
label6.Text = "Total di Rp:";
label6.Location = new Point(1210, 125);
label7.Text = "Total di Rp:";
label7.Location = new Point(1080, 580);
}
//****TextBox for Code****
for (int y = 0; y <= 16; y++)
{
textBoxCodeContainer.Add(new NumericTextBox());
textBoxCodeContainer[y].Size = new Size(100, 50);
textBoxCodeContainer[y].Location = new Point(25, 150 + (y * 25));
textBoxCodeContainer[y].TextChanged += new System.EventHandler(this.textBox_TextChanged);
textBoxCodeContainer[y].AutoCompleteMode = AutoCompleteMode.Suggest;
textBoxCodeContainer[y].AutoCompleteSource = AutoCompleteSource.CustomSource;
textBoxCodeContainer[y].AutoCompleteCustomSource = codesCollection;
theForm.Controls.Add(textBoxCodeContainer[y]);
}
//****TextBox for Quantity****
for (int y = 0; y <= 16; y++)
{
textBoxQuantityContainer.Add(new NumericUpDown());
textBoxQuantityContainer[y].Size = new Size(100, 50);
textBoxQuantityContainer[y].Location = new Point(125, 150 + (y * 25));
textBoxQuantityContainer[y].TextChanged += new System.EventHandler(this.textBox_TextChanged);
textBoxQuantityContainer[y].Maximum = 1000;
theForm.Controls.Add(textBoxQuantityContainer[y]);
}
//****TextBox for Description****
for (int y = 0; y <= 16; y++)
{
textBoxDescContainer.Add(new TextBox());
textBoxDescContainer[y].Size = new Size(750, 50);
textBoxDescContainer[y].Location = new Point(225, 150 + (y * 25));
theForm.Controls.Add(textBoxDescContainer[y]);
}
//****TextBox for Sub Total****
for (int y = 0; y <= 16; y++)
{
textBoxSubTotalContainer.Add(new TextBox());
textBoxSubTotalContainer[y].Size = new Size(175, 50);
textBoxSubTotalContainer[y].Location = new Point(975, 150 + (y * 25));
theForm.Controls.Add(textBoxSubTotalContainer[y]);
}
//****TextBox for Total****
for (int y = 0; y <= 16; y++)
{
textBoxTotalContainer.Add(new TextBox());
textBoxTotalContainer[y].Size = new Size(175, 50);
textBoxTotalContainer[y].Location = new Point(1150, 150 + (y * 25));
textBoxTotalContainer[y].TextChanged += new System.EventHandler(this.textBox_TextChanged);
theForm.Controls.Add(textBoxTotalContainer[y]);
}
//****TextBox for Total All****
textBoxAllTotalContainer.Size = new Size(175, 50);
textBoxAllTotalContainer.Location = new Point(1150, 575);
textBoxAllTotalContainer.TextChanged += new System.EventHandler(this.textBox_TextChanged);
theForm.Controls.Add(textBoxAllTotalContainer);
}
private void UpdateDatas()
{
int codeValue = 0;
int index = 0;
string query = "SELECT [Quantity], [Description], [Price] FROM [Seranne] WHERE [Code] IN (";
OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
if (int.TryParse(this.textBoxCodeContainer[0].Text, out codeValue))
{
query = query + codeValue.ToString();
}
for (int i = 1; i < 17; i++)
{
if (int.TryParse(this.textBoxCodeContainer[i].Text, out codeValue))
{
query = query + "," + codeValue.ToString();
}
}
query = query + ")";
OleDbCommand cmd = new OleDbCommand(query, conn);
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
while (dReader.Read())
{
if (textBoxCodeContainer[index].TextLength != 0)
{
this.textBoxQuantityContainer[index].Maximum = Convert.ToInt32(dReader["Quantity"].ToString());
this.textBoxDescContainer[index].Text = dReader["Description"].ToString();
this.textBoxSubTotalContainer[index].Text = dReader["Price"].ToString();
}
index += 1;
}
dReader.Close();
conn.Close();
}
private void UpdatePrice()
{
int totalPrice = 0;
int quantity = 0;
int price = 0;
for (int i = 0; i < 17; i++)
{
if (textBoxQuantityContainer[i].Value > 0)
{
quantity = (int)textBoxQuantityContainer[i].Value;
price = Convert.ToInt32(textBoxSubTotalContainer[i].Text);
textBoxTotalContainer[i].Text = (quantity * price).ToString();
}
else
{
textBoxSubTotalContainer[i].Text = "";
textBoxTotalContainer[i].Text = "";
}
}
for (int i = 0; i < 17; i++)
{
if (textBoxTotalContainer[i].TextLength != 0)
{
totalPrice += Convert.ToInt32(textBoxTotalContainer[i].Text);
}
}
textBoxAllTotalContainer.Text = totalPrice.ToString("n2");
}
private void UpdateQuantity()
{
int index = 0;
int codeValue = 0;
decimal newVal;
List<int> integers = new List<int>();
foreach (var tb in textBoxCodeContainer)
{
if (int.TryParse(tb.Text, out codeValue))
{
integers.Add(codeValue);
}
}
string command = "UPDATE [Seranne] SET [Quantity]=? WHERE [Code] IN(" + string.Join(", ", integers) + ")";
OleDbConnection conn = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(command, conn);
cmd.Parameters.Add("Quantity", System.Data.OleDb.OleDbType.Integer);
cmd.Parameters["Quantity"].Value = this.newVal.ToString();
OleDbDataReader dReader;
conn.Open();
dReader = cmd.ExecuteReader();
while (dReader.Read())
{
if (textBoxQuantityContainer[index].Value != 0)
{
newVal = (Convert.ToInt32(dReader["Quantity"].ToString()) -
textBoxQuantityContainer[index].Value);
int numberOfRows = cmd.ExecuteNonQuery();
}
index += 1;
}
if (newVal == 0)
{
System.Media.SoundPlayer sounds = new System.Media.SoundPlayer(@"C:\Windows\Media\Windows Notify.wav");
sounds.Play();
MessageBox.Show("Cannot Update", "Error");
}
else
{
System.Media.SoundPlayer sound = new System.Media.SoundPlayer(@"C:\Windows\Media\Windows Notify.wav");
sound.Play();
MessageBox.Show("Was Updated Successfully", "Success");
}
dReader.Close();
conn.Close();
}
private void textBox_TextChanged(object sender, EventArgs e)
{
UpdateDatas();
UpdatePrice();
}
private void button1_Click(object sender, EventArgs e)
{
UpdateQuantity();
}
非常感谢
推荐答案
今天好，我有几件事:
1) cmd.Parameters["Quantity"].Value = this.newVal.ToString(); 这里不需要转换成字符串，因为Value是一个对象.您已经定义它应该作为整数处理.
1) cmd.Parameters["Quantity"].Value = this.newVal.ToString(); conversion to string is not needed here, because the Value is an object. You already defined that it should be handled as integer.
2) 将查询中的 ? 替换为 @Quantity，这样它就会被查询执行填充.
2) Replace the ? within you query to @Quantity, so it will be filled by the query execution.
3) 更新时，您不需要以读者身份执行它.使用 int numberOfRows = cmd.ExecuteNonQuery(); 没有循环.它将更新所有项目.
3) When updating you don't need to execute it as a reader. use the int numberOfRows = cmd.ExecuteNonQuery(); without the loop. It will update all items.
4) 你应该执行 if (textBoxQuantityContainer[index].Value != 0 && textBoxQuantityContainer[index].Value >=Convert.ToInt32(dReader["Quantity"].ToString())){ 在构建整数列表时，这样您只会更新正确的数量.
4) You should execute the if (textBoxQuantityContainer[index].Value != 0 && textBoxQuantityContainer[index].Value >=
Convert.ToInt32(dReader["Quantity"].ToString()))
{ when building the integers list, this way you are only updating the right quantities.
如果您只想更新某些行，则必须扩展 where 子句:
If you only want to update certain rows, you'll have to expand your where clause:
cmd.Parameters.Add("MinimumQuantity", System.Data.OleDb.OleDbType.Integer).Value = minimumQuantity;
string command = "UPDATE [Seranne]
SET [Quantity]=@Quantity
WHERE [Code] IN(" + string.Join(", ", integers) + ")
AND [Quantity] > @MinimumQuantity
最大的风险是:您假设 textBoxCodeContainer 和数据库之间的记录顺序和计数相同.
The higest risk is: You assume that the order and count of the records are the same between your textBoxCodeContainer and the database.
文本框和行之间的关系是什么.你怎么知道哪个文本框链接到哪一行?
如果你向我展示更多的代码(比如 textBoxCodeContainer 在哪里/如何定义)，我可以给你一个正确的方向
I could give you a push in the right direction, if you show me some more code (like where/how is textBoxCodeContainer defined)
更新:
我编写了一些代码来读取和操作您的数据库，这是未测试的，因为我这里没有任何数据库.
I made some code the read and manipulate your database, this is not tested since i don't have any database here.
我会创建这些类，1个是数据类Product，一个是Handler类ProductHandler.
I would create these classes, 1 is a data class Product and one is a Handler class ProductHandler.
数据类只包含数据(不是表示格式)数据处理程序知道如何读取和写入它们.
The data class only contains the data (not in presentation format) The data handler knows how to read and write them.
public class Product
{
public int Code { get; set; }
public string Description { get; set; }
public int Quantity { get; set; }
}
public class ProductHandler
{
public ProductHandler(string connectionString)
{
ConnectionString = connectionString;
}
public bool AddProduct(Product product)
{
return AddProducts(new Product[] { product }) > 0;
}
public int AddProducts(IEnumerable<Product> products)
{
int rowsInserted = 0;
using (OleDbConnection conn = new OleDbConnection(ConnectionString))
{
conn.Open();
string query = "INSERT INTO [Seranne] (Code, Description, Quantity) VALUES(@Code, @Description, @Quantity)";
using (OleDbCommand cmd = new OleDbCommand(query, conn))
{
cmd.Parameters.Add("Code", OleDbType.Integer);
cmd.Parameters.Add("Description", OleDbType.VarChar);
cmd.Parameters.Add("Quantity", OleDbType.Integer);
foreach (var product in products)
{
cmd.Parameters["Code"].Value = product.Code;
cmd.Parameters["Description"].Value = product.Description;
cmd.Parameters["Quantity"].Value = product.Quantity;
rowsInserted += cmd.ExecuteNonQuery();
}
}
}
return rowsInserted;
}
public bool UpdateProduct(Product product)
{
return UpdateProducts(new Product[] { product }) > 0;
}
public int UpdateProducts(IEnumerable<Product> products)
{
int rowsUpdated = 0;
using (OleDbConnection conn = new OleDbConnection(ConnectionString))
{
conn.Open();
string query = "UPDATE [Seranne] SET Description = @Description, Quantity = @Quantity WHERE [Code] == @Code)";
using (OleDbCommand cmd = new OleDbCommand(query, conn))
{
cmd.Parameters.Add("Code", OleDbType.Integer);
cmd.Parameters.Add("Description", OleDbType.VarChar);
cmd.Parameters.Add("Quantity", OleDbType.Integer);
foreach (var product in products)
{
cmd.Parameters["Code"].Value = product.Code;
cmd.Parameters["Description"].Value = product.Description;
cmd.Parameters["Quantity"].Value = product.Quantity;
rowsUpdated += cmd.ExecuteNonQuery();
}
}
}
return rowsUpdated;
}
public bool DeleteProduct(Product product)
{
return DeleteProducts(new int[] { productCode }) > 0;
}
public int DeleteProducts(IEnumerable<Product> products)
{
using (OleDbConnection conn = new OleDbConnection(ConnectionString))
{
conn.Open();
string productCodeStr = string.Join(", ", products.Select(item => item.Code));
string query = string.Format("DELETE FROM [Seranne] WHERE [Code] in ({0})", productCodeStr);
using (OleDbCommand cmd = new OleDbCommand(query, conn))
{
int rowsDeleted = cmd.ExecuteNonQuery();
return rowsDeleted;
}
}
}
public IEnumerable<Product> ReadAllProducts()
{
List<Product> result = new List<Product>();
using (OleDbConnection conn = new OleDbConnection(ConnectionString))
{
conn.Open();
using (OleDbCommand cmd = new OleDbCommand("SELECT [Code], [Description], [Quantity] FROM [Seranne]", conn))
using (OleDbDataReader dReader = cmd.ExecuteReader())
while (dReader.Read())
{
Product product = new Product();
product.Code = Convert.ToInt32(dReader["Code"]);
product.Description = Convert.ToString(dReader["Description"]);
product.Quantity = Convert.ToInt32(dReader["Quantity"]);
result.Add(product);
}
}
return result;
}
public string ConnectionString { get; private set; }
}
<小时>
一些示例代码:
Some example code:
ProductHandler _productHandler = new ProductHandler("connectionstring here or from config");
public void Example()
{
_productList.Clear();
_productList.AddRange(_productHandler.ReadAllProducts());
// displaying
foreach (var product in _productList)
Trace.WriteLine(string.Format("code: {0}, description: {1}, quantity: {2}", product.Code, product.Description, product.Quantity);
// updating
var selectedProduct = _productList.FirstOrDefault(item => item.Code == 15);
if(selectedProduct!= null)
{
selectedProduct.Quantity = 50;
_productHandler.UpdateProduct(selectedProduct);
}
// deleting
_productHandler.DeleteProducts(_productList.Where(item => item.Quantity < 5));
}
<小时>
如何将文本框链接到正确的产品:
How to link the textboxes to the right product:
我将创建一个包含 Product 属性和 TextBoxes 的 UserControl，并在 textbox_changed 事件发生时处理.这些事件处理程序操作 Product 实例.
I would create a UserControl that contains a Product property and the TextBoxes and handles when textbox_changed events occurs. Those event handlers manipulate the Product instance.
您只需生成 16 个控件并将产品绑定到它.当您按下按钮时，您只需要保存更改的产品.
You only generate 16 controls and bind a product to it. When you press a button, you only need to save the changed products.
这篇关于坚持从数据库读取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
