为了保存数据，本地数据库 [英] Save order data to local database
问题描述
我使用C＃创建一个Windows窗体应用程序。如何创建一个本地数据库表来保存下面描述的数据？目前我的数据表由inv_Id，ITEM_ID和量的列。我知道我需要创建存储下面的每个数据项的列，但我不知道如何组织表。
- 项目= {椅子，桌子，钢笔，闪存盘}
- 价格= {5,10,2,48}
- 大部
- 税
- 总
我在C＃新。我做这个搜索，发现类似的东西如 https://www.youtube.com/watch ？v = I5qvyyhUKfY
数据显示在列表框，看起来像：
订购项目：椅价格：$ 5.00结果
订购项目：表价格：$ 10.00搜索
订购项目：笔价格：$ 2.00结果
有序项目：闪存盘价格：$ 48.00
小计：$ 65.00税额：$ 3.90总计：$ 68.90
对于我的目的是创建发票然后计算一切后，将其保存在数据库中。
下面是我得到的数据加载数据的代码DB到下拉列表（用户选择他们想要购买的项目），那么（卡斯特选定的项目将在listOuput框中列出计算）的用户将选择项目，然后列表框中将显示所选的输出和计算总像一个收据。
计算后，我想所有的数据存储在listOutput框我的分贝，但我在这里有问题。
问题：我不知道如何从列表框中我所有的数据移动到
数据库，并在结构上连接在一起。
< /块引用>
公共部分类Select_Item：表
{
的SqlConnection CON =新的SqlConnection（@数据源=（的LocalDB）\v11.0; AttachDbFilename = C：\Users\oo\Documents\Data.mdf;集成安全性= TRUE;连接超时= 30）;
SqlDataAdapter的大=新的SqlDataAdapter（）;
DataTable的DT =新的DataTable（）;
公共结构订单
{
公共字符串项;
公共双价;
}
const的双重征税= 0.06;
订货下单=新订单（）;
静态双大部= 0;
静态双totalTaxes = 0;
静态总两倍;
串finalBill =最终法案：\\\
公共Select_Item（）
{
的InitializeComponent（）;
}
私人无效的GetValues（字符串custOrder）
{
order.item = custOrder;
字符串A = comboBox1.Text;
order.price = Convert.ToDouble（custOrder）;
listOutput.Items.Add（价格+ order.price）;
finalBill + =订购项目：+ A +\\\
Price：+ order.price.ToString（C2）+\\\
;
updateBill（）;
}
私人无效updateBill（）
{
小计+ = order.price;
总+ = order.price +（order.price *含税）;
totalTaxes + = order.price *税;
listOutput.Items.Clear（）;
listOutput.Items.AddRange（finalBill.Split（'\\\
'））;
listOutput.Items.Add（小计：+ subtotal.ToString（C2））;
listOutput.Items.Add（税+ totalTaxes.ToString（C2））;
listOutput.Items.Add（总计：+ total.ToString（C2））;
}
私人无效dropdownSelection（对象发件人，EventArgs五）
{
如果（发件人== comboBox1）
System.Diagnostics.Debug.WriteLine（测试 + comboBox1.SelectedValue.ToString（））;
的GetValues（comboBox1.SelectedValue.ToString（））;
}
编辑代码：
私人无效StoreData（）
{
INT invoiceID;
使用（VAR CON1 =新的SqlConnection（@数据源=（的LocalDB）\v11.0; AttachDbFilename = C：\Users\choo\Documents\Data密度纤维板;集成安全性= TRUE;连接超时= 30））
{
con.Close（）;
con.Open（）;使用（VAR CMD = con.CreateCommand（））
{
cmd.CommandText = @
插入到发票（小计，税务，计）值（@小计，@税，总@ ）;选择SCOPE_IDENTITY（）作为InvoiceID;;
cmd.Parameters.AddWithValue（@小计，小计）;
cmd.Parameters.AddWithValue（@税，所得税）;
cmd.Parameters.AddWithValue（@总总）;
使用（VAR读卡器= cmd.ExecuteReader（））
{
如果（reader.Read（））
invoiceID =（int）的读者[InvoiceID ];
}
}
的foreach（在OrderItems.Rows VAR项）
{
使用（VAR CMD = con.CreateCommand（））
{
cmd.CommandText = @插入InvoiceItem（invoiceID，ITEM_ID，数量）的值（@ invoiceID，@ ITEM_ID，@量）;;
// cmd.Parameters.AddWithValue（@ InvoiceID，invoiceID）;
cmd.Parameters.AddWithValue（@ invoiceID，Convert.ToInt32（invoiceID））;
cmd.Parameters.AddWithValue（@ ITEM_ID，Convert.ToInt32（ITEM_ID））;
cmd.Parameters.AddWithValue（@量，Convert.ToInt32（量））;
cmd.ExecuteNonQuery（）;
}
}
}
}
解决方案我假设你正在将数据保存到SQL数据库。你的发票和项目表共享一个多对多的关系，所以你应该使用第三个表将它们链接在一起。
- 发票：invoiceID，小计，税收，总
- 项目：ITEMID，价格
- InvoiceItem：invoiceItemID，invoiceID，ITEMID，数量
的InvoiceItem表具有外键其他两个。这样，你把你的发票和项目数据独立和清洁;没有摆弄10个不同的笔项目，因为10个不同的订单包括一支钢笔。
请注意，您可以通过从该发票选择所有的项目计算Invoice.subtotal和计算量*价格的总和。我建议，包括它为了方便起见发票表
要获得订单到数据库中，你想要的东西是这样的：
私人无效StoreData（）
{
INT invoiceID;
使用（VAR CON =新的SqlConnection（@数据源=（的LocalDB）\v11.0; AttachDbFilename = C：\Users\oo\Documents\Data.mdf;集成安全性= TRUE;连接超时= 30））
{
con.Open（）;使用（VAR CMD = con.CreateCommand（））
{
cmd.CommandText = @
插入到发票（小计，税务，计）值（@小计，@税，总@ ）;选择SCOPE_IDENTITY（）作为InvoiceID;;
cmd.Parameters.AddWithValue（@小计，小计）;
cmd.Parameters.AddWithValue（@税，所得税）;
cmd.Parameters.AddWithValue（@总总）;使用
（VAR读卡器= cmd.ExecuteReader（））
{
如果（reader.Read（））
invoiceID = cmd.GetInt32（InvoiceID）;
}
}
的foreach（在VAR的OrderItems项目）
{
使用（VAR CMD = con.CreateCommand（））
{
cmd.CommandText = @插入InvoiceItem（InvoiceID，项目编号，数量）的值（@ InvoiceID，@项ID，@量）;;
cmd.Parameters.AddWithValue（@ InvoiceID，invoiceID）;
cmd.Parameters.AddWithValue（@项ID，item.ItemID）;
cmd.Parameters.AddWithValue（@量，item.Quantity）;
cmd.ExecuteNonQuery（）;
}
}
}
}
请理解这一点，你需要做一个基本的，最基本的想法。我也写它而不在IDE中实际检验，所以有可能是一个或两个错误。重要的是，它不是与现有的代码兼容。下面是你需要做的工作是：
- 创建项目的订单，所谓的集合
的OrderItems。这个集合中的每一项应该是某种对象，表示你的ListBox的一条线。请注意，您的OrderItems结构是不足以代表一个项目（你可以告诉为什么？）。现在你身边的东西传递数据的字符串。你需要有真正的对象合作，以获取有关OOP的力量的句柄。
- 在窗体的顶部取下的SqlConnection声明。你不想连接对象只是坐在那里。在
使用块确保寿命有限，该对象被关闭并妥善处理。如果你使用这个对象在其他地方（如获取项目的列表，显示您的用户），那么你需要修改代码以使用这种模式。
- 确定一个好办法得到ITEMID，分类汇总，税和总此方法。你可以将它们作为参数，或者你可以使用对象。
有很多的改进，可制成，既给代码我已经张贴和你有什么了。这仅意味着是足够的基本功能。这里有事情，我留给你作为练习，但你应该做的：
- 错误处理
- 创建项目对象的正确收集并将其绑定到你的UI元素
- 获取如价格和ITEMID从项目对象静态数据，并没有走出UI元素（组合框和列表框）
- 获得更多熟悉数据库的交互功能，让你可以理解它是如何工作
I am using C# to create a Windows form application. How do I create a local database table to save the data described below? Currently my data table consists of the columns of "inv_Id", "Item_Id" and "quantity". I know I need to create the columns to store each of the data items below, but I do not know how to structure the table.
- item={chair,table,pen, thumbdrive}
- Price={5,10,2,48}
- subtotal
- tax
- total
I am new at C#. I did a search for this, and found things like e.g.https://www.youtube.com/watch?v=I5qvyyhUKfY
The data is shown in a list box and looks like:
Ordered Item:chair Price:$5.00
Ordered Item:table Price:$10.00
Ordered Item:pen Price:$2.00
Ordered Item:thumbdrive Price:$48.00Subtotal:$65.00 Tax:$3.90 Total:$68.90
The purpose for me is to create the invoice then save it in the database after calculating everything.
Here is the code that I get the data load the data from db into drop down list (for user to select which item they want to buy), then (cust selected item will be listed in the listOuput box for calculating) user will select the item, then list box will show the selected output and calculate the total like a receipt.
After calculating, I wish to store all the data at the listOutput box to my db, but I having problem here.
Problem: I do not know how to move all my data from list box to database, and link them together in the structure.
public partial class Select_Item : Form { SqlConnection con = new SqlConnection( @"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\oo\Documents\Data.mdf;Integrated Security=True;Connect Timeout=30"); SqlDataAdapter da = new SqlDataAdapter(); DataTable dt = new DataTable(); public struct Orders { public string item; public double price; } const double TAX=0.06; Orders order = new Orders(); static double subtotal=0; static double totalTaxes=0; static double total; string finalBill = "FINAL BILL: \n"; public Select_Item() { InitializeComponent(); } private void getValues(string custOrder) { order.item = custOrder; String a = comboBox1.Text; order.price = Convert.ToDouble(custOrder); listOutput.Items.Add("Price: " + order.price); finalBill += "Ordered Item: " + a + "\nPrice: " + order.price.ToString("C2") + "\n"; updateBill(); } private void updateBill() { subtotal += order.price; total += order.price + (order.price * TAX); totalTaxes += order.price * TAX; listOutput.Items.Clear(); listOutput.Items.AddRange(finalBill.Split('\n')); listOutput.Items.Add("Subtotal:" + subtotal.ToString("C2")); listOutput.Items.Add("Tax:" + totalTaxes.ToString("C2")); listOutput.Items.Add("Total:" + total.ToString("C2")); } private void dropdownSelection(object sender, EventArgs e) { if (sender == comboBox1) System.Diagnostics.Debug.WriteLine("test " + comboBox1.SelectedValue.ToString()); getValues(comboBox1.SelectedValue.ToString()); }
Edited Code:
private void StoreData() { int invoiceID; using (var con1 = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\choo\Documents\Data.mdf;Integrated Security=True;Connect Timeout=30")) { con.Close(); con.Open(); using (var cmd = con.CreateCommand()) { cmd.CommandText = @"insert into Invoice(subtotal,tax,total) values (@subtotal,@tax,@total); select SCOPE_IDENTITY() as InvoiceID;"; cmd.Parameters.AddWithValue("@subtotal", subtotal); cmd.Parameters.AddWithValue("@tax", tax); cmd.Parameters.AddWithValue("@total", total); using (var reader = cmd.ExecuteReader()) { if (reader.Read()) invoiceID = (int)reader["InvoiceID"]; } } foreach (var item in OrderItems.Rows) { using (var cmd = con.CreateCommand()) { cmd.CommandText = @"insert into InvoiceItem(invoiceID,Item_Id,quantity) values (@invoiceID,@Item_Id,@quantity);"; // cmd.Parameters.AddWithValue("@InvoiceID", invoiceID); cmd.Parameters.AddWithValue("@invoiceID", Convert.ToInt32("invoiceID")); cmd.Parameters.AddWithValue("@Item_Id", Convert.ToInt32("Item_Id")); cmd.Parameters.AddWithValue("@quantity", Convert.ToInt32("quantity")); cmd.ExecuteNonQuery(); } } } }
解决方案I am assuming you are saving the data to an SQL database. Your invoice and item tables share a many to many relationship, so you should use a third table to link them together.
- Invoice: invoiceID, subtotal, tax, total
- Item: itemID, price
- InvoiceItem: invoiceItemID, invoiceID, itemID, quantity
The InvoiceItem table has foreign keys to the other two. This way you keep your invoice and item data separate and clean; there's no mucking about with 10 different "pen" items because 10 different orders included a pen.
Note that you can calculate Invoice.subtotal by selecting all the items from that invoice and calculating the sum of quantity * price. I recommend including it on the Invoice table for convenience's sake.
To get the order into the database, you want something like this:
private void StoreData() { int invoiceID; using(var con = new SqlConnection( @"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\oo\Documents\Data.mdf;Integrated Security=True;Connect Timeout=30")) { con.Open(); using(var cmd = con.CreateCommand()) { cmd.CommandText = @"insert into Invoice(subtotal,tax,total) values (@subtotal,@tax,@total); select SCOPE_IDENTITY() as InvoiceID;"; cmd.Parameters.AddWithValue("@subtotal",subtotal); cmd.Parameters.AddWithValue("@tax",tax); cmd.Parameters.AddWithValue("@total",total); using(var reader = cmd.ExecuteReader()) { if(reader.Read()) invoiceID = cmd.GetInt32("InvoiceID"); } } foreach(var item in orderItems) { using(var cmd = con.CreateCommand()) { cmd.CommandText = @"insert into InvoiceItem(InvoiceID,ItemID,quantity) values (@InvoiceID,@ItemID,@quantity);"; cmd.Parameters.AddWithValue("@InvoiceID",invoiceID); cmd.Parameters.AddWithValue("@ItemID",item.ItemID); cmd.Parameters.AddWithValue("@quantity",item.Quantity); cmd.ExecuteNonQuery(); } } } }Please understand this is a rudimentary, bare-bones idea of what you need to do. I've also written it without actually checking it in an IDE, so there might be a mistake or two. Importantly, it's not compatible with your existing code. Here's what you need to do to work this in:
- Create a collection of items for your order, called
orderItems. Each item in this collection should be some kind of object that represents a line in your ListBox. Note that your OrderItems struct is not sufficient to represent a single item (can you tell why?). Right now you're passing things around as strings of data. You need to be working with genuine objects to get a handle on the power of OOP.- Remove the SqlConnection declaration at the top of your form. You don't want connection objects just sitting around. The
usingblocks ensure a limited lifetime and that the object gets closed and disposed of properly. If you're using this object elsewhere (e.g. to get a list of items to show your user), then you need to modify that code to use this pattern.- Determining a good way to get itemID, subtotal, tax and total into this method. You could pass them as parameters, or you could use objects.
There are a lot of improvements that can be made, both to the code I've posted and to what you have already. This is meant only to be enough for basic functionality. Here are things that I leave to you as an exercise, but which you should do:
- Error handling
- Creating a proper collection of item objects and binding it to your UI elements
- Getting static data like price and itemID from item objects and not out of the UI elements (ComboBox and ListBox)
- Getting more familiar with the database interaction functionality, so you can understand how it works
这篇关于为了保存数据，本地数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
