Parsing Vera User Data - Loadable as .NET Dataset
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.
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