Parsing Vera User Data - Loadable as .NET Dataset

Latest revision as of 03:17, 15 June 2013

This is most likely not an elegant way to do this, 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.

Using the functions below, a small .NET web app that shows Rooms/Scenes/Devices (Code for that below) - Also see the forum post:

http://forum.micasaverde.com/index.php?topic=7950.0 [1]

Public Class _Default

   Inherits System.Web.UI.Page

   Dim UserDataset As DataSet
   Dim ud As UserData
   Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
       If Not Page.IsPostBack Then
           If Session("UserData") Is Nothing Then
               ud = New UserData
               Session("UserData") = ud.Parse_UserData_To_DataSet("d:\test")
           End If
       End If
       UserDataset = Session("UserData")
       GetData()
   End Sub

   Sub GetData()
       Dim RoomsTable As DataTable = UserDataset.Tables("rooms")
       Me.RoomsGrid.DataSource = RoomsTable
       Me.RoomsGrid.DataBind()
       RoomsGrid.ShowHeader = False
       Dim ScenesTable As DataTable = UserDataset.Tables("scenes")
       Me.ScenesGrid.DataSource = ScenesTable
       Me.ScenesGrid.DataBind()
       ScenesGrid.ShowHeader = False
       Dim DevicesTable As DataTable = UserDataset.Tables("devices")
       Dim drRemove As DataRow() = DevicesTable.Select("invisible=1")
       For Each dr As DataRow In drRemove
           DevicesTable.Rows.Remove(dr)
       Next
       Me.DevicesGrid.DataSource = DevicesTable
       Me.DevicesGrid.DataBind()
       DevicesGrid.ShowHeader = False
   End Sub

End Class

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