I will share with you one of my solutions I recently created, and I just took some time to add some comments. This approach will allow you to 'append' tables and/or columns on the fly and will not overwrite your source XML or data therein. Please read carefully the comments and learn from this solution, do not just copy/paste your way to 6 pack abs
FormMain: (start form)
ModuleData: (where your dataset/schema lives)Code:Public Class FormMain 'Path where your XML Data will live Dim DataPath As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\InvoiceData\" 'Create a var to the DataSet Dim _Dset As DataSet = ModuleData.Dset 'Create a var to the DataSet that contains schema only Dim _DsetSchema As DataSet = ModuleData.DsetSchema Private Sub FormMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load Width = Screen.PrimaryScreen.Bounds.Width - 75 Height = Screen.PrimaryScreen.Bounds.Height - 75 CenterToParent() Try 'If path to souce doesnt exist, create it If Not My.Computer.FileSystem.DirectoryExists(DataPath) Then My.Computer.FileSystem.CreateDirectory(DataPath) 'If source path and XML exist, read it in "_Dset" (this is your data) If My.Computer.FileSystem.FileExists(DataPath & "InvoiceData.XML") Then _Dset.ReadXml(DataPath & "InvoiceData.XML") 'Sub routine to create DataSet that contains Schema "_DsetSchema" ModuleData.SetDsetSchema() 'Sub routine that compares LoadSchema(_DsetSchema, _Dset) 'Add relations if they do not exist If Not _Dset.Relations.Contains("InvoicePeopleRelation") Then _Dset.Relations.Add(New DataRelation("InvoicePeopleRelation", _Dset.Tables("People").Columns("PersonID"), _Dset.Tables("Invoice").Columns("PersonID"), True)) End If If Not _Dset.Relations.Contains("InvoiceItemsRelation") Then _Dset.Relations.Add(New DataRelation("InvoiceItemsRelation", _Dset.Tables("Invoice").Columns("InvoiceID"), _Dset.Tables("Items").Columns("InvoiceID"), True)) End If Catch ex As Exception MsgBox(ex.ToString) End Try End Sub Private Sub LoadSchema(SourceDS As DataSet, TargetDS As DataSet) 'Boolean to flag if there are Schema Changes Dim SchemaChanged As Boolean = False Try 'Iterate tables in _DsetSchema For Each Tbl As DataTable In SourceDS.Tables 'Check if table exists in _Dset (your data), if not add it If Not TargetDS.Tables.Contains(Tbl.TableName) Then TargetDS.Tables.Add(New DataTable With {.TableName = Tbl.TableName}) 'Iterate columns within table iteration For Each Dcol As DataColumn In Tbl.Columns 'If column doesnt exist then add it If Not TargetDS.Tables(Tbl.TableName).Columns.Contains(Dcol.ColumnName) Then TargetDS.Tables(Tbl.TableName).Columns.Add(New DataColumn With { .ColumnName = Dcol.ColumnName, .DataType = Dcol.DataType, .AutoIncrement = Dcol.AutoIncrement}) 'If columns were added then schema has changed, set flag to true SchemaChanged = True End If Next Next 'If schema flad is true,'rewrite' the XML (your data) If SchemaChanged Then _Dset.WriteXml(DataPath & "InvoiceData.XML", XmlWriteMode.WriteSchema) End If Catch ex As Exception MsgBox(ex.ToString) End Try End Sub End Class
I suppose I could submit this to the vault as well...Code:Module ModuleData 'Declare your Data DataSet (this is your Data) Public WithEvents Dset As New DataSet 'Declare your Schema DataSet (Schema only) Public WithEvents DsetSchema As New DataSet 'Sub Routine to create your Schema Public Sub SetDsetSchema() With DsetSchema .Tables.Add(New DataTable With {.TableName = "People"}) With .Tables("People") .Columns.Add(New DataColumn With { .ColumnName = "PersonID", .AutoIncrement = True, .DataType = GetType(Integer)}) .Columns.Add("DateCreate", GetType(Date)) .Columns("DateCreate").DefaultValue = Now.Date .Columns.Add("FullName", GetType(String)) .Columns.Add("Company", GetType(String)) .Columns.Add("Email", GetType(String)) .Columns.Add("Phone", GetType(String)) .Columns.Add("Address", GetType(String)) .Columns.Add("City", GetType(String)) .Columns.Add("State", GetType(String)) .Columns.Add("ZIP", GetType(String)) End With .Tables.Add(New DataTable With {.TableName = "Invoice"}) With .Tables("Invoice") .Columns.Add(New DataColumn With { .ColumnName = "InvoiceID", .AutoIncrement = True, .DataType = GetType(Integer)}) .Columns.Add("PersonID", GetType(Integer)) .Columns.Add("FullName", GetType(String)) .Columns.Add("Company", GetType(String)) End With .Tables.Add(New DataTable With {.TableName = "Items"}) With .Tables("Items") .Columns.Add(New DataColumn With { .ColumnName = "ItemID", .AutoIncrement = True, .DataType = GetType(Integer)}) .Columns.Add("InvoiceID", GetType(Integer)) .Columns.Add("PersonID", GetType(Integer)) .Columns.Add("ItemDesc", GetType(String)) End With End With End Sub End Module




Reply With Quote
