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