更新，将行添加到数据表 VB.Net
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16590731/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update, Add rows to a datatable VB.Net
提问by Amin Abu Dahab
I have tried so hard to solve my problem, here's what I'm trying to do:
我一直在努力解决我的问题，这就是我想要做的：
- I have an XML file that I load into a dataset("ds") using
ReadXML, several tables are populated in to the dataset, the one I'm concerned about is ("SalesReceiptRet") and I'll refer to it as the source table. - I have another table in MS Access database, I load it into the same
dataset usingOleDBAdapter.Fill, into a datatable named ("dtTarget") and the adapter's name is ("dbAdapter").
- 我有一个 XML 文件，我使用 加载到数据集（“ds”）中
ReadXML，将几个表填充到数据集中，我关心的是（“SalesReceiptRet”），我将其称为源桌子。 - 我在 MS Access 数据库中有另一个表，我
使用OleDBAdapter.Fill, 将它加载到同一个数据集中，加载到名为（“dtTarget”）的数据表中，适配器的名称是（“dbAdapter”）。
I want to go through all the records in the source table, lookup a field called ("TxnID") to locate the record in the target table. If it does not exist, add it, if it does do another validation and overwrite/update it using the source's row.
我想遍历源表中的所有记录，查找一个名为（“TxnID”）的字段来定位目标表中的记录。如果它不存在，则添加它，如果它确实进行了另一次验证并使用源的行覆盖/更新它。
here's the code:
这是代码：
Private Sub btnTest_Click(sender As Object, e As EventArgs) Handles btnTest.Click
'Initialize command builder
Dim dbCommandBuilder As New OleDb.OleDbCommandBuilder(dbAdapter)
'Set the primary keys
ds.Tables("dtTarget").PrimaryKey = New DataColumn() {ds.Tables("dtTarget").Columns("TxnID")}
'Find rows
Dim dr As DataRow
Dim lookupRow As DataRow
For Each dr In ds.Tables("SalesReceiptRet").Rows
lookupRow = ds.Tables("dtTarget").Rows.Find(dr.Item(0))
'If the a row with a similar TxnID exists, do the following validation
If Not (lookupRow Is Nothing) Then
If lookupRow.Item(8).ToString <> "Regular" Then
'do something here to overwrite/update the matching row
End If
Else
'If the row does not exist, import it
ds.Tables("dtTarget").ImportRow(dr)
End If
Next
'Update Access
dbAdapter.Update(ds, "dtTarget")
dbConnection.Close()
End Sub
Perhaps I need to mention that both tables have exact column names except for Access "dtTarget" has additional ones, which does not seem to be causing problem at least with the import row.
也许我需要提到两个表都有确切的列名，除了 Access “dtTarget” 有额外的列名，这似乎至少不会对导入行造成问题。
Any thoughts please?
请问有什么想法吗？
回答by Andre Pageot
This should get you started,
这应该让你开始，
For more help with Access and the insert /Update on the adapter see this link http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/958e6d51-725a-4649-8fc0-79a831f8f37e
有关访问和适配器上的插入/更新的更多帮助，请参阅此链接 http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/958e6d51-725a-4649-8fc0-79a831f8f37e
Dim connStr As String = "Provider=SQLNCLI10;Server=(local);Database=MyNewTest;Uid=user; Pwd=******;"
Dim selectCmd As String = "select * from IdentityTest"
Dim dbAdapter As New OleDb.OleDbDataAdapter(selectCmd, connStr)
dbAdapter.AcceptChangesDuringUpdate = True
'code here to fille up your adapter
'build the insert and update comand on the adapter
Dim InsertCommans As New OleDb.OleDbCommand("INSERT INTO <table> (field1,field2,field3,field4) Values (@val1,@val2,@val3,@val4)")
Dim UpdateCommans As New OleDb.OleDbCommand("UPDATE <table> SET <field> = @Value WHERE <field> = @TxnID")
'Set the primary keys
Dim ds As DataSet
ds.Tables("dtTarget").PrimaryKey = New DataColumn() {ds.Tables("dtTarget").Columns("TxnID")}
'assign our datatables
Dim oDtSource As DataTable = ds.Tables("SalesReceiptRet")
Dim oDtTarget As DataTable : dbAdapter.Fill(oDtTarget)
'for each TxnID in our source table
For Each dr As DataRow In oDtSource.Rows
'get the rows in the target that match to the current source table row taxnid [colname] = TxnID
Dim oRowsInTarget As DataRow() = oDtTarget.Select("[" & oDtTarget.Columns(0).ColumnName & "] = " & dr("TxnID"))
'if there is a match (assuming there will only be one) and the column 8 contents (this is column 9 in reality)
'dowes not equal "Regular" then proceed to run the ole adapter update statement else insert
If oRowsInTarget.Count > 0 AndAlso Not oRowsInTarget(0)(8).ToString <> "Regular" Then
'perform our update
'you have thematching TxnId in dr("TxnID")
'I think you can configure an insert and update statment on the Ole adapter connected to access
'EXEC UPDATE ON THE ADAPTER
ElseIf oRowsInTarget.Count > 0 Then
'EXEC INSERT ON THE ADAPTER
End If
Next
