问题描述
我正在从头开始编写我的发票应用程序，现在想使其更稳定.
我有一张销售发票表格，这是与i
相关的表
1.表名称SP包含主寄存器和发票信息，其字段为SPId，No，Date，Cust，TotalAmt
2.表名称项目包含具有字段PId，SPId，产品，数量，费率，金额Amd
的产品开票"的详细信息3.表名Tax包含具有字段TId，SPId，TaxRate，TaxAmt，Tax
的Taxation详细信息4.表名Other包含具有字段OId，SPId，Charge，Amt的Other Charges的详细信息
每个表都使用SPId链接到SP，并且根据用户的要求，输入的数据可以是单行或多行，也可以不是任何表中的单行.
目前，在我的旧应用程序中，我正在使用SQL Command更新SP，并从数据库中检索SPId，然后在将SPId应用到表之后使用其表适配器更新每个表.这将需要打开连接5次以上，因此更容易出现任何无法控制的错误.
我刚刚学习/了解了数据集和关系，并想着可以一次性更新所有四个表，而不必分别更新每个表以及如何更新
这是我当前的代码(VB 2010和SQL Server Express)
I''m writing my Invoicing Application from Scratch and now want to make it more stable.
I have a Form for Sale Invoicing, Here are the Tables related to i
1. Table Name SP contains master register and invoice information having fields SPId, No, Date, Cust, TotalAmt
2. Table Name Items contains details of Product Billed having fields PId, SPId, Product, Qty, Rate, Amt
3. Table Name Tax contains details of Taxation having fields TId, SPId, TaxRate, TaxAmt, Tax
4. Table Name Other Contains details of Other Charges having fields OId, SPId, Charge, Amt
Every Table is linked to SP with SPId and data entered can be one single row or many rows or not a single row in any table depending on the requirement of the user.
Currently in my old app i was updating SP using SQL Command and retrieving SPId from DB and then updating each table using its table adapter after applying SPId to it. This would need to open the connection more than 5 times and thus was more prone to err of anything goes wrong which is beyond control.
I just studied / learned about dataset and relationships and was thinking wheather it is possible to update all the four tables in single stroke without having to update each table seperately and how
here is my current code (VB 2010 & SQL Server Express)
Dim DS as DataSet
Dim AdapSp as New SqlDataAdapter("SELECT * FROM SP WHERE SPId=0",Conn)
Dim AdapItem as New SqlDataAdapter("SELECT * FROM Items WHERE SPId=0",Conn)
Dim AdapTax as New SqlDataAdapter("SELECT * FROM Tax WHERE SPId=0",Conn)
Dim AdapOther as New SQLDataAdapter("SELECT * FROM Other WHERE SPId=0",Conn)
AdapSp.Fill(DS,"SP")
AdapItem.Fill(Ds,"Item")
AdapTax.Fill(DS,"Tax")
AdapOther.Fill(DS,"Other")
'Now I Have Added Rows to respective Data Tables in DataSet DS
'Now When User Clicks Save Button a Single Row is Added to Table SP
dim R as DataRow=DS.Tables("SP").NewRow
R("SPId")=0
R("No")= xxxx
R("Date")= xxxx
R("Cust")= xxxx
R("TotalAmt")= xxxx
ds.Tables("SP").Rows.Add(R)
'Add & Update Commands can be created using SqlCommandBuilder and data can be updated to DB File but that would be the same what i was doing previously
'Now I want to update the complete information to my Database
请建议我现在该怎么办
在此先感谢
阿米特·萨拉夫(Amit Saraf)
Please Suggest me what to do now
Thanks in Advance
Amit Saraf
推荐答案
Dim ds as New CustomDataSet
Dim spTa as New CustomDataSetTableAdapters.SpTableAdapter 'or whatever you call the table
Dim itemTa as New CustomDataSetTableAdapters.ItemTableAdapter
spTa.Fill(ds.sp)
itemTa.Fill(ds.Item)
'repeat for other tables
'Note the ds.sp tables rows will have RowState of 'Unchanged'. You do not need to use this but it helps to know what it is. The .Fill method sets rowstate = 'unchanged'. New rows added to the DS will be 'Added'. Updated rows will be 'Modified'. Have a play around or read up 'DataRow.RowState'
' Add row
Dim spRow as CustomDataSet.spRow = ds.sp.NewSpRow()
spRow.spId = 0
'Set other spRow fields this way. (Fields will appear in intellisense because it is defined in typed dataset CustomDataSet.
ds.sp.AddSpRow(spRow) ' <-- Here the spRow.RowState will be 'Added'
'
'Repeat the table row adds for the other tables
'
' Now call built in update method
spTa.Update(ds.Sp)
itemTa.Update(ds.Item)
请注意，一旦提交，调用.Update方法会将行状态重置为Unchanged.如果需要在执行数据库提交/更新之前操纵已修改或已添加的行，则可以使用RowState属性，然后使用DataSet.AcceptChanges()进行最终确定.
Note that calling the .Update methods will reset the rowstates to Unchanged once committed. If you need to manipulate modified or added rows before doing database commit/update, then you can use the RowState property then finalise using DataSet.AcceptChanges().
这篇关于更新多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！