MySqlDataAdapter.update() 不使用 DataRelation 更新 DataRow

Question

我有两个表，第一个是“Employee”，第二个是“YearEmployee”。“YearEmployee”的外键是“Employee”的主键。
我想在数据集中添加两个具有 DataRelation 的数据行，但我得到“外键约束失败”。
我知道我可以在数据库中写入第一个数据行，然后在两个数据行之间建立关系，但我想只用一个函数调用来更新整个数据集。
有人知道这个问题或可以确定我做错了什么？谢谢你的帮助，抱歉我的英语不好..

我的代码:

从数据库中获取数据

Public Shared Sub sub_mysql_get_all_dataset(ByVal dsDataset As DataSet, ByVal strTable() As String)
    Dim dbConnection As New MySqlConnection
    Dim dbAdapter As New MySqlDataAdapter

    dbConnection.ConnectionString = _strConnStr

    Try
        dbConnection.Open()
        For Each strTable_row As String In strTable
            dbAdapter = New MySqlDataAdapter("SELECT * FROM " & strTable_row, dbConnection)
            dbAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
            dbAdapter.FillSchema(dsDataset, SchemaType.Source, strTable_row)
            dbAdapter.Fill(dsDataset, strTable_row)
        Next
        dbConnection.Close()
    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try

End Sub

更新数据库中的数据和更新数据集

    Public Shared Function func_mysql_update_dataset(ByVal dsDataset As DataSet, ByVal strTable() As String) As Boolean

    Dim dbConnection As New MySqlConnection
    Dim dbAdapter As New MySqlDataAdapter

    dbConnection.ConnectionString = _strConnStr

    Try
        dbConnection.Open()

        For Each strTable_row As String In strTable
            dbAdapter = New MySqlDataAdapter("SELECT * FROM " & strTable_row, dbConnection)
            Dim cb As New MySqlCommandBuilder(dbAdapter)

            dbAdapter.Update(dsDataset, strTable_row)
            dbAdapter.Fill(dsDataset, strTable_row)
        Next
        dbConnection.Close()

        func_mysql_update_dataset = True
    Catch ex As Exception
        Console.WriteLine(ex.Message)
        func_mysql_update_dataset = False
    End Try
End Function

初始化数据集

    Public Shared ds_Employee As New DataSet
    Database.sub_mysql_get_all_dataset(ds_Employee, {"Employee", "YearEmployee", "WorkHours"})

    Dim rel_Emp_To_YEmp As DataRelation = ds_Employee.Relations.Add("PK_Employee_to_FK_YearEmployee", ds_Employee.Tables("Employee").Columns("IDEmployee"), ds_Employee.Tables("YearEmployee").Columns("EmployeeID"))
    Dim rel_YEmp_To_WHours As DataRelation = ds_Employee.Relations.Add("PK_YearEmployee_to_FK_WorkHours", ds_Employee.Tables("YearEmployee").Columns("IDYearEmployee"), ds_Employee.Tables("WorkHours").Columns("YearEmployeeID"))

添加数据行

    Dim dr_Employee As DataRow = ds_Employee.Tables("Employee").NewRow()

    dr_Employee("DepartementID") = WPF_Emp_CbBxDepartement.SelectedItem.Key
    dr_Employee("FirstName") = WPF_Emp_txtBxFName.Text
    ds_Employee.Tables("Employee").Rows.Add(dr_Employee)


    Dim dr_YearEmployee As DataRow = ds_Employee.Tables("YearEmployee").NewRow()
    dr_YearEmployee.SetParentRow(ds_Employee.Tables("Employee").Rows(ds_Employee.Tables("Employee").Rows.Count - 1), ds_Employee.Tables("YearEmployee").ParentRelations("PK_Employee_to_FK_YearEmployee"))
    dr_YearEmployee("fromDate") = CType(tp_YearEmployee.Controls(0).Controls.Find("UC_YE_DTP_From", False).First, DateTimePicker).Value
       ds_Employee.Tables("YearEmployee").Rows.Add(dr_YearEmployee)

写入数据库

Database.func_mysql_update_dataset(ds_Employee, {"Employee", "YearEmployee"})

Answer 1

解决方案是在 RowUpdated 上添加一个处理程序。

函数处理程序

Private Shared Sub dbAdapter_RowUpdated(sender As Object, e As MySqlRowUpdatedEventArgs)
    If (e.StatementType = StatementType.Insert And e.Status = UpdateStatus.Continue) Then
        Dim identityQuery As MySqlCommand = New MySqlCommand("Select @@IDENTITY")
        Dim strIDTable As String = "ID" & e.TableMapping.DataSetTable
        identityQuery.Connection = e.Command.Connection

        e.Row(strIDTable) = identityQuery.ExecuteScalar
    End If
End Sub

要使用它，只需在 update() 方法之前添加 hanlder 并在 update() 执行之后将其删除，就像这样:

' ### ADD REFRESH ID HANDLER '
AddHandler dbAdapter.RowUpdated, AddressOf dbAdapter_RowUpdated

' ### WRITE DATASET '
dbAdapter.Update(dsDataset, strTable_row)

' ### DELETE REFRESH ID HANDLER '
RemoveHandler dbAdapter.RowUpdated, AddressOf dbAdapter_RowUpdated

资料来源: 1 2