更新多个表 [英] Updating Multiple Tables

问题描述

我正在从头开始编写我的发票应用程序，现在想使其更稳定.
我有一张销售发票表格，这是与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

推荐答案

一个小小的Google大有帮助. 读我 [ ^ ]

A little Google goes a long way. Read me[^]

实际上，仅凭一个命令，您的想法/想要的确实是一种无风险的方法.

为了帮助您更多地了解数据集以及如何更有效地使用它们，我提出了这种可能的解决方案.

我会在项目中创建一个DataSet对象，例如``CustomDataSet''.这会将数据集类定义为"CustomDataSet"类型.

然后，使用现有的SELECT命令将TableAdapters添加到设计器中.您可以将问题中的所有表添加到设计器中.

允许设计人员自动生成更新，删除，插入命令(请注意sql必须用于一个表，即无联接，否则自动生成insert，delete，update无效).

然后在代码中:

There really isn''t a risk-free way of doing what your thinking/wanting, with just one command.

To help you learn a little more about Datasets and how they can be used more efficiently, I present this possible solution.

I would create a DataSet object in the project e.g. ''CustomDataSet''. This will define the data set class as type ''CustomDataSet''.

Then add the TableAdapters to the designer using your existing SELECT commands. You can add all the tables as in your question to the designer.

Allow the designer to auto generate the update, delete, insert commands (NOTE that the sql must be for one table i.e no joins, otherwise auto generation of insert,delete,update will not work).

Then in code:

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().

这篇关于更新多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！