我有在两个SQL表中的数据的数据集。
如何更新与更新的数据集这两个表？ （因为加入不工作）
da = new SqlDataAdapter("select * from xxxx join.....", conn);
builderForTable1 = new SqlCommandBuilder(da);
da.Update(dataSetEmp, "Table");
Answer 1:
该SQLDataAdapter.Fill（）方法可以做到这一点，如果你提供的数据适配器的查询返回多个结果。
var da = new SqlDataAdapter("select * from customers; select * from orders;", conn);
da.Fill(myDataset);
var customersDataTable = myDataset.Tables[0];
var ordersDataTable = myDataset.Tables[1];
然后，你可以根据需要添加自己的DataRelations。
myDataset.Relations.Add(new DataRelation(
"CustomerOrders",
customersDataTable.Columns["CustomerID"],
ordersDataTable.Columns["CustomerID"]
)
Answer 2:
也许你可以使用一个交易？
SqlTransaction tr = conn.BeginTransaction();
da = new SqlDataAdapter("select * from xxxx join.....", conn);
builderForTable1 = new SqlCommandBuilder(da);
da.Update(dataSetEmp, "Table");
// ...
tr.Commit();
参考：
- 的SqlTransaction
Answer 3:
Public Shared Function GetRecords(ByRef CNobj As System.Data.SqlClient.SqlConnection,
ByRef RSobj As System.Data.DataSet,
ByVal SQLquery As String,
ByVal TableName() As String,
Optional ByVal PrimKeys() As String = Nothing,
Optional ByRef adapter As SqlDataAdapter = Nothing) As Integer
On Error Resume Next
Dim DataOkay As Integer = 0 '[Set to negative if data bad]
Dim suppliersAdapter As SqlDataAdapter = New SqlDataAdapter()
'Create connection object, won't create if already exists
If ConnectToDB(CNobj, False) = False Then
'error creating object, Session("ErrorMsg") set by called code
Return -1
End If
' Open the connection.
If CNobj.State = ConnectionState.Closed Then
CNobj.Open()
End If
' Create a SqlCommand to retrieve Suppliers data.
Dim suppliersCommand As SqlCommand = New SqlCommand(SQLquery, CNobj)
suppliersCommand.CommandType = CommandType.Text
' Set the SqlDataAdapter's SelectCommand.
suppliersAdapter.SelectCommand = suppliersCommand
' Fill the DataSet.
RSobj = New DataSet()
suppliersAdapter.Fill(RSobj)
If (Err.Number <> 0) Then
DataOkay = -1
_contexts.Session("ErrorMsg") = "Error reading records: " +
DelDoubleQuotesInString(Err.Description)
Call Err.Clear()
ElseIf (RSobj.Tables.Count = 0) Then
DataOkay = -2
_contexts.Session("ErrorMsg") = "No tables read reading records for sql=" + SQLquery
Else
' A table mapping names the DataTables.
Dim IDX As Integer
For IDX = 0 To TableName.Count - 1
RSobj.Tables(IDX).TableName = TableName(IDX)
If PrimKeys IsNot Nothing Then
Dim primstr = PrimKeys(IDX)
Dim primarr = Split(primstr, ",")
Dim primcol(primarr.Count) As DataColumn
Dim pidx As Integer
For pidx = 0 To primarr.Count
primcol(pidx) = RSobj.Tables(IDX).Columns.Item(primarr(pidx))
Next
RSobj.Tables(IDX).PrimaryKey = primcol
End If
Next
adapter = suppliersAdapter
End If
Return DataOkay
End Function
Public Shared Function UpdateDB(ByRef dset As System.Data.DataSet, ByVal adapter As SqlDataAdapter)
If dset.HasChanges = False Then
Return True
End If
' has multiple tables, select's separated by ;
Dim SelectQry As String = adapter.SelectCommand.CommandText
Dim commands() As String = Split(SelectQry, ";")
Dim idx As Integer = 0
For Each Table As System.Data.DataTable In dset.Tables
'Connection object is global in shared ASP.NET module
If CNobj.State = ConnectionState.Closed Then
CNobj.Open()
End If
'Change select for table being updated.
adapter.SelectCommand.CommandText = commands(idx)
Dim cb As SqlCommandBuilder
cb = New SqlCommandBuilder(adapter)
adapter.InsertCommand = cb.GetInsertCommand(True)
adapter.UpdateCommand = cb.GetUpdateCommand(True)
adapter.DeleteCommand = cb.GetDeleteCommand(True)
Try
adapter.AcceptChangesDuringUpdate = True
'force order delete, update, insert
adapter.Update(Table.Select(Nothing, Nothing, DataViewRowState.Deleted))
Table.AcceptChanges() 'Have to do after delete!
adapter.Update(Table.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent))
adapter.Update(Table.Select(Nothing, Nothing, DataViewRowState.Added))
Catch
HttpContext.Current.Session("ErrorMsg") = "Error saving data to DB: " + Err.Description
adapter.SelectCommand.CommandText = SelectQry
Return False
End Try
idx += 1
cb.Dispose()
Next
'Put original select back for next call
adapter.SelectCommand.CommandText = SelectQry
Return True
End Function
我包括我使用这两个程序。 第一个例程会得到多个表，如果调用选择用分号。 我通过在表名的数组分配给每个表。 如果数据集要更新到服务器，然后我还通过在主键和参考的更新调用传递适配器。
我知道这是在VB而不是C＃，但传统的ASP我转换为ASP.NET在VB已经和我没有想移植到C＃在它的上面。
更新代码分裂，每次更新调用每个表的选择路线。
错误 - 上面的代码并不适用于所有表的工作。 对于一个表，如果我不这样做的AcceptChanges不会更新 - 让并发错误。 在另一张表，它指出我打电话的AcceptChanges - 但呼叫后变为零，因此没有记录被更新之前有我的13个修改的行。 我的猜测是，对于一个表中获取并发错误是废话，是的AcceptChanges不应该被称为生成的更新SQL。 当我解决这个我会更新帖子。
文章来源: How to update two tables with one dataset?
