Linking fields when creating a dataset relation

See more:

VB

XML

The code works fine as is - with one exception.

The linking fields for the relation (EmpNo) shows up in the final XML document in both the Employee node and the WorkAssignment node. The WorkAssignment node should not have the EmpNo, just the other five fields.

Doing some research I think using GetChildRows might be what I need but I cannot find any code that shows me how to use that, in VB.Net, that also shows how to add it to the dataset so that the ds.WriteXML() final command works correctly.

Any help would be greatly appreciated.

Thank You - Greg Gregerson

What I have tried:

<pre>Imports System.IO

Public Class Form1
    Public watchfolder As FileSystemWatcher


    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles btnStart.Click

        ' Enable the file watcher
        watchfolder = New System.IO.FileSystemWatcher
        watchfolder.SynchronizingObject = Me

        ' Set the folder path
        watchfolder.Path = txtPath.Text

        ' Enable specific filters
        watchfolder.NotifyFilter = IO.NotifyFilters.DirectoryName
        watchfolder.NotifyFilter = watchfolder.NotifyFilter Or IO.NotifyFilters.FileName
        watchfolder.NotifyFilter = watchfolder.NotifyFilter Or IO.NotifyFilters.LastWrite
        watchfolder.NotifyFilter = watchfolder.NotifyFilter Or IO.NotifyFilters.Attributes

        ' Add the handler for each event
        AddHandler watchfolder.Changed, AddressOf logChange
        AddHandler watchfolder.Created, AddressOf logChange
        AddHandler watchfolder.Deleted, AddressOf logChange

        ' Start watching
        watchfolder.EnableRaisingEvents = True

        ' Set the buttons
        btnStart.Enabled = False
        btnStop.Enabled = True
        btnConvert.Enabled = False

    End Sub

    Private Sub logChange(ByVal source As Object, ByVal e As System.IO.FileSystemEventArgs)
        If e.ChangeType = IO.WatcherChangeTypes.Created Then
            txtFiles.Text &= e.FullPath & vbCrLf
        End If
        If e.ChangeType = IO.WatcherChangeTypes.Deleted Then
            txtFiles.Text &= "File " & e.FullPath &
                                    " has been deleted" & vbCrLf
        End If
    End Sub

    Private Sub btnStop_Click(sender As Object, e As EventArgs) Handles btnStop.Click
        watchfolder.EnableRaisingEvents = False
        btnStart.Enabled = False
        btnStop.Enabled = False
        btnConvert.Enabled = True
    End Sub

    Private Sub btnConvert_Click(sender As Object, e As EventArgs) Handles btnConvert.Click
        Dim dir As String = txtPath.Text
        Dim i As Integer = 1
        Dim fileEntries As String() = Directory.GetFiles(dir, "*.txt")
        Dim fileName As String

        ' Create the employee data table
        Dim tbl As DataTable = New DataTable("Employee")
        tbl.Columns.Add(New DataColumn With {.ColumnName = "XRefCode", .DataType = GetType(String)})
        tbl.Columns.Add(New DataColumn With {.ColumnName = "EmpNo", .DataType = GetType(String)})
        tbl.Columns.Add(New DataColumn With {.ColumnName = "First", .DataType = GetType(String)})
        tbl.Columns.Add(New DataColumn With {.ColumnName = "Last", .DataType = GetType(String)})
        tbl.Columns.Add(New DataColumn With {.ColumnName = "Gender", .DataType = GetType(String)})
        tbl.Columns.Add(New DataColumn With {.ColumnName = "SSN", .DataType = GetType(String)})
        tbl.Columns.Add(New DataColumn With {.ColumnName = "HireDate", .DataType = GetType(String)})
        tbl.Columns.Add(New DataColumn With {.ColumnName = "OriginalHire", .DataType = GetType(String)})
        tbl.Columns.Add(New DataColumn With {.ColumnName = "DOB", .DataType = GetType(String)})
        tbl.Columns.Add(New DataColumn With {.ColumnName = "StartDate", .DataType = GetType(String)})
        tbl.Columns.Add(New DataColumn With {.ColumnName = "SeniorityDate", .DataType = GetType(String)})

        ' Create the work assignment data table
        Dim watbl As DataTable = New DataTable("WorkAssignment")
        watbl.Columns.Add(New DataColumn With {.ColumnName = "EmpNo", .DataType = GetType(String)})
        watbl.Columns.Add(New DataColumn With {.ColumnName = "JobXRefCode", .DataType = GetType(String)})
        watbl.Columns.Add(New DataColumn With {.ColumnName = "DeptXRefCode", .DataType = GetType(String)})
        watbl.Columns.Add(New DataColumn With {.ColumnName = "OrgXRefCode", .DataType = GetType(String)})
        watbl.Columns.Add(New DataColumn With {.ColumnName = "IsPrimary", .DataType = GetType(String)})
        watbl.Columns.Add(New DataColumn With {.ColumnName = "EffectiveStart", .DataType = GetType(String)})

        ' Process the list of .txt files found in the directory. '
        For Each fileName In fileEntries

            ' Make sure the file exists (Note:  probably not needed)
            If (System.IO.File.Exists(fileName)) Then

                'Create a streamreader to read the contents of the file
                Dim fileString As String
                Dim sr As IO.StreamReader = New IO.StreamReader(fileName)

                While sr.Peek() >= 0
                    ' Read a row in the file 
                    fileString = sr.ReadLine
                    Dim temp() As Object = fileString.Split("|")

                    ' Create the array for the employee table
                    Dim tempE(10) As String
                    Array.Copy(temp, 0, tempE, 0, 11)

                    ' Create the array for the work assignment table
                    Dim tempW(5) As String
                    tempW(0) = temp(0)
                    tempW(1) = temp(11)
                    tempW(2) = temp(12)
                    tempW(3) = temp(13)
                    tempW(4) = temp(14)
                    tempW(5) = temp(15)

                    ' Add the array data to the table                    
                    tbl.Rows.Add(tempE)
                    watbl.Rows.Add(tempW)

                End While

            End If
        Next

        ' Create a DataSet and add the employees table to it
        Dim ds As DataSet = New DataSet("EmployeeImport")
        ds.Tables.Add(tbl)
        ds.Tables.Add(watbl)

        'Add DataRelation to DataSet:
        Dim pClmn, cClmn As DataColumn
        pClmn = tbl.Columns("EmpNo")
        cClmn = watbl.Columns("EmpNo")

        'Create DataRelation:
        Dim dr As DataRelation = New DataRelation("Emp_WA", pClmn, cClmn)
        dr.Nested = True

        ' Add the relation to the dataset
        ds.Relations.Add(dr)

        ' Write XML to file
        ds.WriteXml(dir & "\Test.xml")

    End Sub
End Class