none
Add constrains to data table relations RRS feed

  • Question

  • Hi,

    I have following table design and a desired XML output, and the included code produces the desired XML but i cannot add constraints to it or if i add constraints i cannot read back the xml with read xml. 

    I want to add  constraints to the data table relation so that if i delete a product all inside a product tag should be deleted , if i delete a category all inside a category node should be deleted.  so update and delete rule should be set to cascade. Could anyone please help me to achieve this?

    I am ready to change my table design to achieve the goal , if possible i would like to have product id and category in one column any idea how should i design my table or change the code to have desired xml output with constrains?. Xml should be readable back to DataSet. 

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load Dim DtSet As New DataSet With DtSet .Tables.Add("Product") With .Tables("Product") .Columns.Add("ProductID", GetType(String)) .Rows.Add("P1") .Rows.Add("P2") End With .Tables.Add("Category") With .Tables("Category") .Columns.Add("ProductID", GetType(String)) .Columns.Add("CategoryID", GetType(String)) .Rows.Add("P1", "C1") .Rows.Add("P1", "C2") .Rows.Add("P2", "C3") .Rows.Add("P2", "C4") End With .Tables.Add("Items") With .Tables("Items") .Columns.Add("ProductID", GetType(String)) .Columns.Add("CategoryID", GetType(String)) .Columns.Add("Items", GetType(String)) .Rows.Add("P1", "", "Item1") .Rows.Add("P2", "", "Item2") .Rows.Add("", "C2", "Item3") .Rows.Add("", "C3", "Item4") .Rows.Add("", "C4", "Item5") End With .Relations.Add(New DataRelation("ProductToItems", .Tables("Product").Columns("ProductID"), .Tables("Items").Columns("ProductID"), False)) .Relations.Add(New DataRelation("ProductToCategory", .Tables("Product").Columns("ProductID"), .Tables("Category").Columns("ProductID"), False)) .Relations.Add(New DataRelation("CategoryToItems", .Tables("Category").Columns("CategoryID"), .Tables("Items").Columns("CategoryID"), False)) For Each Relation As DataRelation In .Relations Relation.Nested = True Next End With DtSet.WriteXml("C:\Data\Data.XML", XmlWriteMode.IgnoreSchema)

    End Sub

    Desired XML out put 

    <Product>
    
         <Items>
               <Item1>
               <Item2>
         </Items>
    
         <Category>
               <Items>
                    <Item3>
                    <Item4>
               </Items>
         </Category>
    
    </Product>



    • Edited by Shan1986 Monday, July 15, 2019 1:57 PM
    Monday, July 15, 2019 1:55 PM

All replies

  • Hello,

    You need to create constraints, see the following

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/dataset-datatable-dataview/datatable-constraints

    Public Class Form1
        Private Sub CreateConstraint(
            dataSet As DataSet,
            table1 As String,
            table2 As String,
            column1 As String,
            column2 As String)
    
            ' Declare parent column and child column variables.
            Dim parentColumn As DataColumn
            Dim childColumn As DataColumn
            Dim foreignKeyConstraint As ForeignKeyConstraint
    
            ' Set parent and child column variables.
            parentColumn = dataSet.Tables(table1).Columns(column1)
            childColumn = dataSet.Tables(table2).Columns(column2)
            ' Set null values when a value is deleted.
            foreignKeyConstraint = New ForeignKeyConstraint("SupplierForeignKeyConstraint", parentColumn, childColumn) _
            With {
                .DeleteRule = Rule.Cascade,
                .UpdateRule = Rule.Cascade,
                .AcceptRejectRule = AcceptRejectRule.None
            }
    
            ' Add the constraint, and set EnforceConstraints to true.
            dataSet.Tables(table1).Constraints.Add(foreignKeyConstraint)
            dataSet.EnforceConstraints = True
    
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, July 15, 2019 3:29 PM
    Moderator

  • Desired XML out put 

    <Product>
    
         <Items>
               <Item1>
               <Item2>
         </Items>
    
         <Category>
               <Items>
                    <Item3>
                    <Item4>
               </Items>
         </Category>
    
    </Product>



    https://docs.microsoft.com/en-us/dotnet/api/system.data.datarelation.nested?view=netframework-4.8

    Success
    Cor

    Monday, July 15, 2019 6:20 PM
  • Hallo Karen, thanks for your answer but it gives me error before compiling on the last line "This constraint cannot be added since Foreignkey doesnot belong to the table Product" 
    Monday, July 15, 2019 7:57 PM
  • Hallo Karen, thanks for your answer but it gives me error before compiling on the last line "This constraint cannot be added since Foreignkey doesnot belong to the table Product" 
    Unsure what to advise without being able to examine everything.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, July 15, 2019 8:38 PM
    Moderator
  • here is my code

            Dim DtSet As New DataSet
            With DtSet
                .Tables.Add("Product")
                With .Tables("Product")
                    .Columns.Add("ProductID", GetType(String))
                    .Rows.Add("P1")
                    .Rows.Add("P2")
                End With
    
                .Tables.Add("Category")
                With .Tables("Category")
                    .Columns.Add("ProductID", GetType(String))
                    .Columns.Add("CategoryID", GetType(String))
                    .Rows.Add("P1", "C1")
                    .Rows.Add("P1", "C2")
                    .Rows.Add("P2", "C3")
                    .Rows.Add("P2", "C4")
                End With
    
                .Tables.Add("Items")
                With .Tables("Items")
                    .Columns.Add("ProductID", GetType(String))
                    .Columns.Add("CategoryID", GetType(String))
                    .Columns.Add("Items", GetType(String))
                    .Rows.Add("P1", "", "Item1")
                    .Rows.Add("P2", "", "Item2")
                    .Rows.Add("", "C2", "Item3")
                    .Rows.Add("", "C3", "Item4")
                    .Rows.Add("", "C4", "Item5")
                End With
    
                .Relations.Add(New DataRelation("ProductToItems", .Tables("Product").Columns("ProductID"),
                                                                      .Tables("Items").Columns("ProductID"), False))
    
                .Relations.Add(New DataRelation("ProductToCategory", .Tables("Product").Columns("ProductID"),
                                                                         .Tables("Category").Columns("ProductID"), False))
    
                .Relations.Add(New DataRelation("CategoryToItems", .Tables("Category").Columns("CategoryID"),
                                                                       .Tables("Items").Columns("CategoryID"), False))
                For Each Relation As DataRelation In .Relations
                    Relation.Nested = True
                Next
    
    
    
                ' Declare parent column and child column variables.
                Dim parentColumn As DataColumn
                Dim childColumn As DataColumn
                Dim foreignKeyConstraint As ForeignKeyConstraint
    
                ' Set parent and child column variables.
                parentColumn = .Tables("Product").Columns("ProductID")
                childColumn = .Tables("Category").Columns("ProductID")
                ' Set null values when a value is deleted.
                foreignKeyConstraint = New ForeignKeyConstraint("SupplierForeignKeyConstraint", parentColumn, childColumn) _
                With {
                    .DeleteRule = Rule.Cascade,
                    .UpdateRule = Rule.Cascade,
                    .AcceptRejectRule = AcceptRejectRule.None
                }
    
                ' Add the constraint, and set EnforceConstraints to true.
                .Tables("Product").Constraints.Add(foreignKeyConstraint)
                .EnforceConstraints = True

    Tuesday, July 16, 2019 5:00 AM
  • Hi Shan,
    try the following demo. You can get the desired XML via XElement:

    Public Class Form1
      Private dg As New DataGrid With {.Dock = DockStyle.Fill}
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim DtSet As New DataSet
        With DtSet
          .Tables.Add("Product")
          With .Tables("Product")
            .Columns.Add("ProductID", GetType(String))
            .Rows.Add("P1")
            .Rows.Add("P2")
          End With
    
          .Tables.Add("Category")
          With .Tables("Category")
            .Columns.Add("ProductID", GetType(String))
            .Columns.Add("CategoryID", GetType(String))
            .Rows.Add("P1", "C1")
            .Rows.Add("P1", "C2")
            .Rows.Add("P2", "C3")
            .Rows.Add("P2", "C4")
          End With
    
          .Tables.Add("Items")
          With .Tables("Items")
            .Columns.Add("ProductID", GetType(String))
            .Columns.Add("CategoryID", GetType(String))
            .Columns.Add("Items", GetType(String))
            .Rows.Add("P1", "", "Item1")
            .Rows.Add("P2", "", "Item2")
            .Rows.Add("", "C2", "Item3")
            .Rows.Add("", "C3", "Item4")
            .Rows.Add("", "C4", "Item5")
          End With
    
          .Relations.Add(New DataRelation("ProductToItems", .Tables("Product").Columns("ProductID"),
                                                                .Tables("Items").Columns("ProductID"), False))
    
          .Relations.Add(New DataRelation("ProductToCategory", .Tables("Product").Columns("ProductID"),
                                                                   .Tables("Category").Columns("ProductID"), False))
    
          .Relations.Add(New DataRelation("CategoryToItems", .Tables("Category").Columns("CategoryID"),
                                                                 .Tables("Items").Columns("CategoryID"), False))
          For Each Relation As DataRelation In .Relations
            Relation.Nested = True
          Next
        End With
    
        DtSet.WriteXml("c:\temp\x1.xml")
    
    
        Dim xe0 As New XElement("root")
        '
        For Each dr1 As DataRow In DtSet.Tables("Product").Rows
          '
          Dim xe1 As New XElement("Product")
          xe0.Add(xe1)
          xe1.Add(New XAttribute("ProductID", dr1.Field(Of String)("ProductID")))
          '
          Dim xe2 As New XElement("Items")
          xe1.Add(xe2)
          '
          For Each dr2 As DataRow In dr1.GetChildRows("ProductToItems")
            Dim xe3 As New XElement(dr2.Field(Of String)("Items"))
            xe2.Add(xe3)
          Next
          '
          For Each dr3 As DataRow In dr1.GetChildRows("ProductToCategory")
            Dim xe4 As New XElement("Category")
            xe1.Add(xe4)
            xe4.Add(New XAttribute("CategoryID", dr3.Field(Of String)("CategoryID")))
            ' 
            Dim xe5 As New XElement("Items")
            xe4.Add(xe5)
            '
            For Each dr4 As DataRow In dr3.GetChildRows("CategoryToItems")
              Dim xe6 As New XElement(dr4.Field(Of String)("Items"))
              xe5.Add(xe6)
            Next
          Next
        Next
    
        xe0.Save("c:\temp\x2.xml")
    
        Me.Controls.Add(dg)
        dg.DataSource = DtSet
      End Sub
    End Class

    And you get for your data:

    <?xml version="1.0" encoding="utf-8"?>
    <root>
      <Product ProductID="P1">
        <Items>
          <Item1 />
        </Items>
        <Category CategoryID="C1">
          <Items />
        </Category>
        <Category CategoryID="C2">
          <Items>
            <Item3 />
          </Items>
        </Category>
      </Product>
      <Product ProductID="P2">
        <Items>
          <Item2 />
        </Items>
        <Category CategoryID="C3">
          <Items>
            <Item4 />
          </Items>
        </Category>
        <Category CategoryID="C4">
          <Items>
            <Item5 />
          </Items>
        </Category>
      </Product>
    </root>


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Tuesday, July 16, 2019 5:48 AM
  • Hallo Peter,

    My problem is i could not get the constraints to work. My code already produces the desired XML. I want to export the data to xml and read the xml back to dataset. Any idea why i cant apply the constraints?.

    Thanks

     

    Tuesday, July 16, 2019 7:26 AM
  • Hi Shan,
    for this case try the following demo. If a XML file exists in Form_Load the DataSet will be loaded from XML file. Then you can navigate in DataGrid, change data, insert data. In Form_Closing the dataset will be saved as XML for next round. If you have any trouble with this demo write the correct error and line.

    Imports System.IO
    
    Public Class Form1
    
      Private dg As New DataGrid With {.Dock = DockStyle.Fill}
      Private inpFileName As String = "c:\temp\x1.xml"
      Dim DtSet As New DataSet
    
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' simulate designer
        Me.Controls.Add(dg)
        ' Binding to DataGrid
        dg.DataSource = DtSet
        '
        If File.Exists(inpFileName) Then
          DtSet.ReadXml(inpFileName)
        Else
          With DtSet
            .Tables.Add("Product")
            With .Tables("Product")
              .Columns.Add("ProductID", GetType(String))
              .Rows.Add("P1")
              .Rows.Add("P2")
            End With
    
            .Tables.Add("Category")
            With .Tables("Category")
              .Columns.Add("ProductID", GetType(String))
              .Columns.Add("CategoryID", GetType(String))
              .Rows.Add("P1", "C1")
              .Rows.Add("P1", "C2")
              .Rows.Add("P2", "C3")
              .Rows.Add("P2", "C4")
            End With
    
            .Tables.Add("Items")
            With .Tables("Items")
              .Columns.Add("ProductID", GetType(String))
              .Columns.Add("CategoryID", GetType(String))
              .Columns.Add("Items", GetType(String))
              .Rows.Add("P1", "", "Item1")
              .Rows.Add("P2", "", "Item2")
              .Rows.Add("", "C2", "Item3")
              .Rows.Add("", "C3", "Item4")
              .Rows.Add("", "C4", "Item5")
            End With
    
            .Relations.Add(New DataRelation("ProductToItems", .Tables("Product").Columns("ProductID"),
                                                                        .Tables("Items").Columns("ProductID"), False))
    
            .Relations.Add(New DataRelation("ProductToCategory", .Tables("Product").Columns("ProductID"),
                                                                           .Tables("Category").Columns("ProductID"), False))
    
            .Relations.Add(New DataRelation("CategoryToItems", .Tables("Category").Columns("CategoryID"),
                                                                         .Tables("Items").Columns("CategoryID"), False))
            For Each Relation As DataRelation In .Relations
              Relation.Nested = True
            Next
          End With
        End If
    
        'Dim xe0 As New XElement("root")
        ''
        'For Each dr1 As DataRow In DtSet.Tables("Product").Rows
        '  '
        '  Dim xe1 As New XElement("Product")
        '  xe0.Add(xe1)
        '  xe1.Add(New XAttribute("ProductID", dr1.Field(Of String)("ProductID")))
        '  '
        '  Dim xe2 As New XElement("Items")
        '  xe1.Add(xe2)
        '  '
        '  For Each dr2 As DataRow In dr1.GetChildRows("ProductToItems")
        '    Dim xe3 As New XElement(dr2.Field(Of String)("Items"))
        '    xe2.Add(xe3)
        '  Next
        '  '
        '  For Each dr3 As DataRow In dr1.GetChildRows("ProductToCategory")
        '    Dim xe4 As New XElement("Category")
        '    xe1.Add(xe4)
        '    xe4.Add(New XAttribute("CategoryID", dr3.Field(Of String)("CategoryID")))
        '    ' 
        '    Dim xe5 As New XElement("Items")
        '    xe4.Add(xe5)
        '    '
        '    For Each dr4 As DataRow In dr3.GetChildRows("CategoryToItems")
        '      Dim xe6 As New XElement(dr4.Field(Of String)("Items"))
        '      xe5.Add(xe6)
        '    Next
        '  Next
        'Next
        'xe0.Save("c:\temp\x2.xml")
    
      End Sub
    
      Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
        DtSet.WriteXml("c:\temp\x1.xml")
      End Sub
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Tuesday, July 16, 2019 9:30 AM
  • Hallo Peter, 

    my problem is applying constraints. for e.g if i delete a category then all items within that category should be deleted automatically , if delete a product then all category inside the product and items has to be deleted automatically. So i am trying to get the constraints working. 

    I can not write 'for each' statements for all of my tables . What  i have here shown is a small part of my tables and the problem i am facing. 

     ' Set parent and child column variables.
                parentColumn = .Tables("Product").Columns("ProductID")
                childColumn = .Tables("Category").Columns("ProductID")
                ' Set null values when a value is deleted.
                foreignKeyConstraint = New ForeignKeyConstraint("SupplierForeignKeyConstraint", parentColumn, childColumn) _
                With {
                    .DeleteRule = Rule.Cascade,
                    .UpdateRule = Rule.Cascade,
                    .AcceptRejectRule = AcceptRejectRule.None
                }
    
                ' Add the constraint, and set EnforceConstraints to true.
                .Tables("Product").Constraints.Add(foreignKeyConstraint)
                .EnforceConstraints = True

    What is wrong with is code and why it is not working?

    thanks

    Tuesday, July 16, 2019 10:39 AM
  • Hi Shan,
    it is impossible add ForeignKeyConstraint when DataRelation between master and child can be broken (in your case Items can contains empty key). If you want use DeleteRule=Cascade then all Items must only a DataRelation to Category like in this demo:

    Imports System.IO
    
    Public Class Form1
    
      Private dg As New DataGrid With {.Dock = DockStyle.Fill}
      Private inpFileName As String = "c:\temp\x1.xml"
      Dim DtSet As New DataSet
    
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' simulate designer
        Me.Controls.Add(dg)
        ' Binding to DataGrid
        dg.DataSource = DtSet
        '
        If File.Exists(inpFileName) Then
          DtSet.ReadXml(inpFileName)
        Else
          With DtSet
            .Tables.Add("Product")
            With .Tables("Product")
              .Columns.Add("ID", GetType(Integer))
              .Columns.Add("ProductName", GetType(String))
              .Rows.Add(1, "P1")
              .Rows.Add(2, "P2")
            End With
    
            .Tables.Add("Category")
            With .Tables("Category")
              .Columns.Add("ID", GetType(Integer))
              .Columns.Add("ProductFK", GetType(Integer))
              .Columns.Add("CategoryName", GetType(String))
              .Rows.Add(1, 1, "C1")
              .Rows.Add(2, 1, "C2")
              .Rows.Add(3, 2, "C3")
              .Rows.Add(4, 2, "C4")
            End With
    
            .Tables.Add("Items")
            With .Tables("Items")
              .Columns.Add("ID", GetType(Integer))
              .Columns.Add("CategoryFK", GetType(Integer))
              .Columns.Add("ItemsName", GetType(String))
              .Rows.Add(1, 1, "Item1")
              .Rows.Add(2, 1, "Item2")
              .Rows.Add(3, 2, "Item3")
              .Rows.Add(4, 3, "Item4")
              .Rows.Add(5, 4, "Item5")
            End With
    
            ' ForeignKeyConstraint
            Dim fkc1 As New ForeignKeyConstraint("CategoryForeignKeyConstraint",
                                                 .Tables("Product").Columns("ID"),
                                                 .Tables("Category").Columns("ProductFK")) With
                {
                    .DeleteRule = Rule.Cascade,
                    .UpdateRule = Rule.Cascade,
                    .AcceptRejectRule = AcceptRejectRule.None
                }
            ' Add the constraint
            .Tables("Category").Constraints.Add(fkc1)
    
            ' Set null values when a value is deleted.
            Dim fkc2 As New ForeignKeyConstraint("ItemsForeignKeyConstraint",
                                                 .Tables("Category").Columns("ID"),
                                                 .Tables("Items").Columns("CategoryFK")) With
                      {
                          .DeleteRule = Rule.Cascade,
                          .UpdateRule = Rule.Cascade,
                          .AcceptRejectRule = AcceptRejectRule.Cascade
                      }
            ' Add the constraint, and set EnforceConstraints to true.
            .Tables("Items").Constraints.Add(fkc2)
    
            .Relations.Add(New DataRelation("ProductToCategory",
                                            .Tables("Product").Columns("ID"),
                                            .Tables("Category").Columns("ProductFK"), True))
    
            .Relations.Add(New DataRelation("CategoryToItems",
                                            .Tables("Category").Columns("ID"),
                                            .Tables("Items").Columns("CategoryFK"), True))
    
            For Each Relation As DataRelation In .Relations
              Relation.Nested = True
            Next
    
            'set EnforceConstraints to true.
            .EnforceConstraints = True
    
          End With
        End If
    
        'Dim xe0 As New XElement("root")
        ''
        'For Each dr1 As DataRow In DtSet.Tables("Product").Rows
        '  '
        '  Dim xe1 As New XElement("Product")
        '  xe0.Add(xe1)
        '  xe1.Add(New XAttribute("ProductID", dr1.Field(Of String)("ProductID")))
        '  '
        '  Dim xe2 As New XElement("Items")
        '  xe1.Add(xe2)
        '  '
        '  For Each dr2 As DataRow In dr1.GetChildRows("ProductToItems")
        '    Dim xe3 As New XElement(dr2.Field(Of String)("Items"))
        '    xe2.Add(xe3)
        '  Next
        '  '
        '  For Each dr3 As DataRow In dr1.GetChildRows("ProductToCategory")
        '    Dim xe4 As New XElement("Category")
        '    xe1.Add(xe4)
        '    xe4.Add(New XAttribute("CategoryID", dr3.Field(Of String)("CategoryID")))
        '    ' 
        '    Dim xe5 As New XElement("Items")
        '    xe4.Add(xe5)
        '    '
        '    For Each dr4 As DataRow In dr3.GetChildRows("CategoryToItems")
        '      Dim xe6 As New XElement(dr4.Field(Of String)("Items"))
        '      xe5.Add(xe6)
        '    Next
        '  Next
        'Next
        'xe0.Save("c:\temp\x2.xml")
    
      End Sub
    
      Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
        DtSet.WriteXml("c:\temp\x2.xml")
      End Sub
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Tuesday, July 16, 2019 12:41 PM
  • Thanks peter, in this case my XML structure will change right?. 

    Is it possible to keep XML structure and ForeignKeyConstraint by changing a datatable design?. 

    Tuesday, July 16, 2019 1:10 PM
  • Hi Shan,
    it is impossible add ForeignKeyConstraint when DataRelation between master and child can be broken (in your case Items can contains empty key). If you want use DeleteRule=Cascade then all Items must only a DataRelation to Category like in this demo:

    Imports System.IO
    
    Public Class Form1
    
      Private dg As New DataGrid With {.Dock = DockStyle.Fill}
      Private inpFileName As String = "c:\temp\x1.xml"
      Dim DtSet As New DataSet
    
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' simulate designer
        Me.Controls.Add(dg)
        ' Binding to DataGrid
        dg.DataSource = DtSet
        '
        If File.Exists(inpFileName) Then
          DtSet.ReadXml(inpFileName)
        Else
          With DtSet
            .Tables.Add("Product")
            With .Tables("Product")
              .Columns.Add("ID", GetType(Integer))
              .Columns.Add("ProductName", GetType(String))
              .Rows.Add(1, "P1")
              .Rows.Add(2, "P2")
            End With
    
            .Tables.Add("Category")
            With .Tables("Category")
              .Columns.Add("ID", GetType(Integer))
              .Columns.Add("ProductFK", GetType(Integer))
              .Columns.Add("CategoryName", GetType(String))
              .Rows.Add(1, 1, "C1")
              .Rows.Add(2, 1, "C2")
              .Rows.Add(3, 2, "C3")
              .Rows.Add(4, 2, "C4")
            End With
    
            .Tables.Add("Items")
            With .Tables("Items")
              .Columns.Add("ID", GetType(Integer))
              .Columns.Add("CategoryFK", GetType(Integer))
              .Columns.Add("ItemsName", GetType(String))
              .Rows.Add(1, 1, "Item1")
              .Rows.Add(2, 1, "Item2")
              .Rows.Add(3, 2, "Item3")
              .Rows.Add(4, 3, "Item4")
              .Rows.Add(5, 4, "Item5")
            End With
    
            ' ForeignKeyConstraint
            Dim fkc1 As New ForeignKeyConstraint("CategoryForeignKeyConstraint",
                                                 .Tables("Product").Columns("ID"),
                                                 .Tables("Category").Columns("ProductFK")) With
                {
                    .DeleteRule = Rule.Cascade,
                    .UpdateRule = Rule.Cascade,
                    .AcceptRejectRule = AcceptRejectRule.None
                }
            ' Add the constraint
            .Tables("Category").Constraints.Add(fkc1)
    
            ' Set null values when a value is deleted.
            Dim fkc2 As New ForeignKeyConstraint("ItemsForeignKeyConstraint",
                                                 .Tables("Category").Columns("ID"),
                                                 .Tables("Items").Columns("CategoryFK")) With
                      {
                          .DeleteRule = Rule.Cascade,
                          .UpdateRule = Rule.Cascade,
                          .AcceptRejectRule = AcceptRejectRule.Cascade
                      }
            ' Add the constraint, and set EnforceConstraints to true.
            .Tables("Items").Constraints.Add(fkc2)
    
            .Relations.Add(New DataRelation("ProductToCategory",
                                            .Tables("Product").Columns("ID"),
                                            .Tables("Category").Columns("ProductFK"), True))
    
            .Relations.Add(New DataRelation("CategoryToItems",
                                            .Tables("Category").Columns("ID"),
                                            .Tables("Items").Columns("CategoryFK"), True))
    
            For Each Relation As DataRelation In .Relations
              Relation.Nested = True
            Next
    
            'set EnforceConstraints to true.
            .EnforceConstraints = True
    
          End With
        End If
    
        'Dim xe0 As New XElement("root")
        ''
        'For Each dr1 As DataRow In DtSet.Tables("Product").Rows
        '  '
        '  Dim xe1 As New XElement("Product")
        '  xe0.Add(xe1)
        '  xe1.Add(New XAttribute("ProductID", dr1.Field(Of String)("ProductID")))
        '  '
        '  Dim xe2 As New XElement("Items")
        '  xe1.Add(xe2)
        '  '
        '  For Each dr2 As DataRow In dr1.GetChildRows("ProductToItems")
        '    Dim xe3 As New XElement(dr2.Field(Of String)("Items"))
        '    xe2.Add(xe3)
        '  Next
        '  '
        '  For Each dr3 As DataRow In dr1.GetChildRows("ProductToCategory")
        '    Dim xe4 As New XElement("Category")
        '    xe1.Add(xe4)
        '    xe4.Add(New XAttribute("CategoryID", dr3.Field(Of String)("CategoryID")))
        '    ' 
        '    Dim xe5 As New XElement("Items")
        '    xe4.Add(xe5)
        '    '
        '    For Each dr4 As DataRow In dr3.GetChildRows("CategoryToItems")
        '      Dim xe6 As New XElement(dr4.Field(Of String)("Items"))
        '      xe5.Add(xe6)
        '    Next
        '  Next
        'Next
        'xe0.Save("c:\temp\x2.xml")
    
      End Sub
    
      Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
        DtSet.WriteXml("c:\temp\x2.xml")
      End Sub
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Hallo Peter, is there a way to keep XML structure and constraints by changing a datatable design? if so how should my design my tables?
    Wednesday, July 17, 2019 9:39 AM