Parsing Vera User Data - Loadable as .NET Dataset

Revision as of 17:58, 25 September 2011

This is most likely not an elegant way to dothis, but in case someone wants something similar feel free to use this. Please suggest if there are better ways of accomplishing this and if you happen to know so, update this page :)

1. This will give you a single xml file you could load in .NET through Dataset.Readxml and you will get about 48tables.

2. Nested tables like Devices have states and ControlURLs - these become a separate table with a new attribute added to refer what Device ID a row in these tables would relate to the devices table.

So, This is more like it!!!

Dim masterDs As New DataSet

   Sub Parse_UserData_To_DataSet(ByVal OutputFolder As String, _
                                         Optional ByVal veraURL As String = "http://myhome:49451")

       'Parse XML and create XML files to load in to dataset directly
       Dim settings As New XmlReaderSettings()
       settings.DtdProcessing = DtdProcessing.Parse
       Dim reader As XmlReader = XmlReader.Create(veraURL & "/data_request?output_format=xml&id=user_data", settings)
       Dim doc As New XmlDocument()
       doc.Load(reader)
       Dim root As XmlNode = doc.DocumentElement
       Dim ParseNodeEnum As IEnumerator = root.GetEnumerator
       While (ParseNodeEnum.MoveNext)
           Dim NodeRow As XmlNode = ParseNodeEnum.Current
           ParseElements(NodeRow, Nothing)
       End While
   End Sub

   Sub ParseElements(ByVal ParseNode As XmlNode, ByVal ParentRow As DataRow)
       Dim dt As DataTable = CreateTable(ParseNode, ParentRow)

       Dim ParseNodeEnum As IEnumerator = ParseNode.GetEnumerator
       While (ParseNodeEnum.MoveNext)
           Dim NodeRow As XmlNode = ParseNodeEnum.Current
           Dim dr As DataRow = dt.NewRow
           'Add Attributes as columns to Datatable
           Dim ienum As IEnumerator = NodeRow.Attributes.GetEnumerator()
           While ienum.MoveNext
               Dim atr As XmlAttribute = ienum.Current
               If dt.Columns.Contains(atr.Name) = False Then dt.Columns.Add(atr.Name)
               dr(atr.Name) = atr.Value
           End While
           If Not IsNothing(ParentRow) Then
               Dim ParentColumn As String = ParentRow.Table.TableName & "_OurID"
               If dt.Columns.Contains(ParentColumn) Then dr(ParentColumn) = ParentRow(ParentColumn)
           End If
           dt.Rows.Add(dr)
           For Each x As XmlNode In NodeRow.ChildNodes
               ParseElements(x, dr)
           Next
       End While
       If Not masterDs.Tables.Contains(ParseNode.Name) Then masterDs.Tables.Add(dt)
   End Sub

   Function CreateTable(ByVal Node As XmlNode, ByVal ParentRow As DataRow) As DataTable
       Dim dt As DataTable
       If masterDs.Tables.Contains(Node.Name) Then
           dt = masterDs.Tables(Node.Name)
           Return dt
       End If

       dt = New DataTable(Node.Name)
       'Add primary key col
       Dim KeyColumn As String = Node.Name & "_OurID"
       dt.Columns.Add(New DataColumn(KeyColumn, GetType(System.Int32)))
       dt.Columns(0).AutoIncrement = True
       dt.Constraints.Add(dt.TableName & "PrimaryID", dt.Columns(0), True)

       'Add parent ref column
       If Not IsNothing(ParentRow) Then
           Dim ParentColumn As String = ParentRow.Table.TableName & "_OurID"
           dt.Columns.Add(New DataColumn(ParentColumn, GetType(System.Int32)))
       End If
       Return dt
   End Function