Add constrains to data table relations

[Shan1986]

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>

Continue reading...