Parsing Vera User Data - Loadable as .NET Dataset
(10 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
− | + | [[Category:Development]][[Category:How_To]] | |
− | 1. This will give you a single xml file you could load in .NET through Dataset.Readxml and you will get about | + | 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. | ||
<br/> | <br/> | ||
<br/> | <br/> | ||
− | 2. Devices have states and ControlURLs | + | 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. |
<br/> | <br/> | ||
<br/> | <br/> | ||
Line 10: | Line 12: | ||
<br/> | <br/> | ||
+ | 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 [http://forum.micasaverde.com/index.php?topic=7950.0] | ||
− | + | 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 | ||
− | + | 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 | Next | ||
− | + | Me.DevicesGrid.DataSource = DevicesTable | |
− | + | Me.DevicesGrid.DataBind() | |
− | + | DevicesGrid.ShowHeader = False | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
End Sub | End Sub | ||
+ | End Class | ||
− | + | <br/> | |
− | + | So, This is more like it!!! | |
− | + | <br/> | |
− | + | ||
− | + | ||
− | + | ||
− | + | 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() | Dim settings As New XmlReaderSettings() | ||
settings.DtdProcessing = DtdProcessing.Parse | settings.DtdProcessing = DtdProcessing.Parse | ||
− | + | Dim reader As XmlReader = XmlReader.Create(veraURL & "/data_request?output_format=xml&id=user_data", settings) | |
− | Dim reader As XmlReader = XmlReader.Create( | + | |
Dim doc As New XmlDocument() | Dim doc As New XmlDocument() | ||
doc.Load(reader) | doc.Load(reader) | ||
Dim root As XmlNode = doc.DocumentElement | Dim root As XmlNode = doc.DocumentElement | ||
− | Dim | + | Dim ParseNodeEnum As IEnumerator = root.GetEnumerator |
− | + | While (ParseNodeEnum.MoveNext) | |
− | While ( | + | Dim NodeRow As XmlNode = ParseNodeEnum.Current |
− | + | ParseElements(NodeRow, Nothing) | |
− | Dim | + | 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 | 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 | End While | ||
− | + | If Not masterDs.Tables.Contains(ParseNode.Name) Then masterDs.Tables.Add(dt) | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
End Sub | End Sub | ||
− | + | Function CreateTable(ByVal Node As XmlNode, ByVal ParentRow As DataRow) As DataTable | |
− | Dim | + | Dim dt As DataTable |
− | + | If masterDs.Tables.Contains(Node.Name) Then | |
− | Dim | + | dt = masterDs.Tables(Node.Name) |
− | + | Return dt | |
− | + | End If | |
− | + | dt = New DataTable(Node.Name) | |
− | End | + | 'Add primary key col |
− | Return | + | 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 | End Function |
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