坚持从数据库中读取数据
本文关键字：读取 数据 数据库 坚持 | 更新日期: 2023-09-27 18:29:47
我想用运行程序时给出的值减去数据库中的数据。一切都正常，但我一直被困在newVal。我已经这样做了，但newVal一直显示为0（因为我声明了decimal newVal = 0，但在这个问题上，我只使用了decimal newVal;）。还有两个问题：如果我把newVal = ...移到顶部，那就没用了，因为newVal中的一个计算是从数据库中读取数据（因为我想要数据库减去我运行程序时给出的新值，需要dReader = cmd.ExecuteReader();），但如果我在读取数据后把newVal放在底部，那也没用，因为我设置了Quantity = ?，?的值是newVal..，这是代码：
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是一个对象。您已经定义它应该作为整数处理。
2） 将查询中的?替换为@Quantity，因此它将由查询执行填充。
3） 当更新时，您不需要以读取器的身份执行它。使用不带循环的CCD_ 16。它将更新所有项目。
4） 在构建整数列表时，应该执行if (textBoxQuantityContainer[index].Value != 0 && textBoxQuantityContainer[index].Value >=
Convert.ToInt32(dReader["Quantity"].ToString()))
{，这样只会更新正确的数量。
如果你只想更新某些行，你必须扩展你的where子句：
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和数据库之间的记录顺序和计数相同。
文本框和行之间的关系是什么。你怎么知道哪些文本框链接到哪一行
如果你给我看更多的代码（比如textBoxCodeContainer在哪里/如何定义），我可以向正确的方向推动你
更新：
我做了一些代码来读取和操作你的数据库，这是没有测试的，因为我这里没有任何数据库。
我将创建这些类，1是数据类Product，1是处理程序类ProductHandler。
数据类只包含数据（不是表示格式）数据处理程序知道如何读取和写入它们。
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; }
}
一些示例代码：
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));
}
如何将文本框链接到正确的产品：
我会创建一个UserControl，它包含Product属性和TextBoxes，并在发生textbox_changed事件时进行处理。这些事件处理程序操作Product实例。
你只生成16个控件并将一个产品绑定到它。当你按下一个按钮时，你只需要保存更改后的产品。