Write XML data to Database

Question

Hi,

I have a WPF application currently under development, which contains a window with many bound controls.  I am currently binding those controls to an in-memory XML document, such as the following:

<data_type Id="Main">
  <Item Id="Cmd" DataLen="2" Type="Short" Value="0"/>
  <Item Id="Data" DataLen="2" Type="Short" Value="0"/>
  <Item Id="Spare0" DataLen="2" Type="Short" Value="0"/>
  <Item Id="Spare1" DataLen="4" Type="Integer" Value="0"/>
  <Item Id="Spare2" DataLen="2" Type="Short" Value="0"/>
  <Item Id="Spare3" DataLen="2" Type="Short" Value="0"/>
  <Item Id="Spare4" DataLen="2" Type="Short" Value="0"/>
  (and many more)
</data_type>
<data_type Id="Backup">
  <Item Id="Cmd" DataLen="2" Type="Short" Value="0"/>
  <Item Id="Data" DataLen="2" Type="Short" Value="0"/>
  <Item Id="Spare0" DataLen="2" Type="Short" Value="0"/>
  <Item Id="Spare1" DataLen="4" Type="Integer" Value="0"/>
  <Item Id="Spare2" DataLen="2" Type="Short" Value="0"/>
  <Item Id="Spare3" DataLen="2" Type="Short" Value="0"/>
  <Item Id="Spare4" DataLen="2" Type="Short" Value="0"/>
  (and many more)
</data_type>

This in-memory document's Value attribues are continuously being updated (like 4 times per second!) by a data gathering thread.  The UI controls, bound to these <Item> elements will properly update whenever those values change.

Now there is a requirement to write this data continuously to a database.  This writing does not need to be done quite as frequently, perhaps only once per second or every other second.  I hope to accomplish this in another thread.

Each record would contain a date-time stamp, and a column for each item value above.  Something like:

DateTime, MainItem1, MainItem2,...BackupItem1, Backupitem2...

This structure seems the easiest way to retrieve historical data for any particular time.  Granted the records will be VERY WIDE, but I think it should still work.  Our plan is to do proof-of-concept using MySQL (which provides a .NET connector), and possibly later migrate it to SQL Server.

The question revolves around how to do this data saving in the most efficient manner.  It should be easy enough to iterate all of the <Item> elements and build a SQL string, then do a simple insert.  This seems easy yet cumbersome.  So is there a simple way to "slam" the data into the database using ADO.Net?  If needed, I could transform the XML document first, creating a simpler structure with only the <Item> elements.

All I would need would be the equivalent of an Insert statement to save the data.  Later, I may need a way to retrieve the data by date-time range and reload it into the original XML object, so my UI could have a "history" view.

Any ideas?  Thanks much in advance...

---

Ron Mittelman

Answer 1

Hi Ron

When you have datetime column, you don't need to store the backup Item again.

Have a nice day.

---

Ghost,
Call me ghost for short, Thanks
To get the better answer, it should be a better question.

Answer 2

Hi Ghost,

Thanks for the answer, but I'm unclear on what it means.  To clarify, the date-time column will be automatically populated in the database when the record is created, and has nothing to do with my XML data structure.

I am trying to add a record based on values in my in-memory XML document.  My question is how to do this the most efficient way.

I could loop through the appropriate XML elements and build an insert statement using a stringbuilder, then execute the insert statement.  This seems inefficient on the face of it.

I was hoping there was a simpler way to do this.  Googling revealed a MySQL LOAD XML method, but this seems to require an XML file name, whereas I have an in-memory XML document.  I am hoping there is a method to supply xml element objects (or even the outer xml string of the selected elements) to some function which will do an insert to the data table.

Thanks...

---

Ron Mittelman

Answer 3

Hi Ron,

My meaning is that: I assume the back up data is for backing up. But your record has datetime column, it represent the data is new or old. So you don't need the backup data. Hence, remove the backup part in the XML file. And here just the Main data, so your block is over.

Have a nice day.

---

Ghost,
Call me ghost for short, Thanks
To get the better answer, it should be a better question.

Answer 4

You are totally missing the point of my question.  It has nothing to do with backing up anything.  The word "Backup" in the XML should not be interpreted as having any special meaning.  The application is monitoring outputs from hardware units, of which there is a main unit and a backup unit.  I need to monitor both units.  Forget the name "Backup" as normally meant.

The question in my post has not been addressed at all.  I need to know the most efficient way of saving data from an in-memory XML document to my database.  I thought this was the proper forum for that.

Can you or anyone else out there advise me on saving XML data to a database table?

Thanks...

---

Ron Mittelman

Answer 5

Hi Ron,

Yes, this is the right forum.

Sorry for misunderstanding this question.

So your major question is save the xml data to Database.

Here just an idea:

Because there will be a lot of Items. Would you like to load it in a Datatable for Main data and the other for Backup data. Each item will be a row. You can also put the two datatable in a dataset.

When you need to save the data, you can just get the copy of the datatable: http://msdn.microsoft.com/en-us/library/system.data.datatable.copy.aspx Although the save operation will take some times, but it doesn't matter. it doesn't affect the original datatable. That means you can keep changing the values in that datatable. You can use DataAdapter to save the changes, it will save you time to code by set the update/insert command.

Have a nice day.

---

Ghost,
Call me ghost for short, Thanks
To get the better answer, it should be a better question.

Answer 6

Hey Ghost,

The XML snippet I gave you is a very small part of the total XML, just for example.  There are other sections besides "Main" and "Backup".  Some item ID's are duplicated, but not within a particular section.  In order to avoid confusion, the table will look something like this:

record_timestamp timestamp
Main_Cmd smallint
Main_Data smallint
..other columns...
Backup_Cmd smallint
Backup_Data smallint
..other columns...

Therefore, I can have 1 wide record with all of the field data, regardless of whether it is Main, Backup, or any other category.

My hope is that I can write the data very quickly to the database, because the object is to continuously monitor all statuses and values contained in the XML.  It can't be a slow operation.  Whatever the current values are in the XML object, they should be written to the database every second or two.  The write itself can't take as long as a second to accomplish, or I would get data collisions or other bad data conditions, right?

I have seen articles that show how to write XML directly to database, both for SQL Server, and MySQL.  Unfortunately, they always seem to want the name of the XML file, and obviously read it from disk and do the insert.  This would be way too slow.  I'm looking for a way to do a direct write from the in-memory XML object.  I don't mind doing an XSL transform to another in-memory XML object which exactly mirrors the database table structure.  This would be fairly simple to accomplish.  What I DON'T want to do is have to write it to disk before saving to database table.

I'm a bit short of knowledge in the ADO.NET area, so not exactly familiar with differences between datatable and dataset.  Also, why would using either take a lot of time?

I have heard of techniques to directly copy XML data to a dataset or datatable, but don't know if this can be done from the in-memory XML object itself.

Also, this project is at the very beginning, so if it is MUCH more efficient to bind my UI controls to an ADO.NET data object rather than XML, I'm open to that.  The reason I'm using XML is because it is so nice for both holding data AND is self-documenting (no more Word design documents being obsolete).

If I can keep my dynamic data in XML, so much the better.  If it is easy to load XML data (not the file from disk) to a dataset or directly to the database, that would be good.

If it is almost as quick to loop through the in-memory xml and build an insert statement (the XML elements will contain both the column names and the values), I don't mind the coding effort.  I think that's a simple looping structure with not more than 10-20 lines of code to build the entire insert statement.  I'm much more concerned with speed of operation.

Hope this isn't TMI.  Thanks...

---

Ron Mittelman

Answer 7

Hi Ron,

>> The write itself can't take as long as a second to accomplish, or I would get data collisions or other bad data conditions, right?

Since you just need to write the update, so I think this will be accomplished soon.

The data in datatable is in-memory, and the advantage is it can easily to get the updated value. I am not very familiar with XML, maybe it can also only get the updated item, if so, this may not be an advantage. So you can confirm this by yourself.

>>If it is easy to load XML data (not the file from disk) to a dataset or directly to the database, that would be good.

What is the in-memory xml format? Is it a memroystream? Or a XML document? When you show me the exactly format, I will show you the specific loading way. In all, it can load into a datatable easily.

>>I'm much more concerned with speed of operation.

All the operation is in memory, so it should meet your requirement.

Best regards,

---

Ghost,
Call me ghost for short, Thanks
To get the better answer, it should be a better question.

Answer 8

Thanks for the reply.

The time to do the actual insert of new record I am not concerned about.  I only mention this because your earlier post said the save operation would take some time.

As far as the format of the XML, it is indeed an XMLDocument object.  It "looks" just like the XML snippet I posted earlier.  The document contains a series of objects, nodes, which themselves can contain other nodes or elements.  This looks much like a Treeview architecture.  Any of the elements can contain attributes, such as Type and Value shown above.  The elements are accessed by name, using XPath syntax, which is functionally similar to Where clause in SQL.

My code to get and save the values would be something like this (this is pseudocode, so may not end up exactly like this):

Dim Doc As New XMLDocument
Doc.Load("file name")
Dim Items As XMLNodeList = Doc.SelectNodes("//Item[@Active='True']")
Dim Sql as String = "Insert Into MyTable ("
Dim SqlVals as string = ") Values ("
For each Elem As XMLElement in Items
    Sql += Elem.GetAttribute("Id") & ", "
    SqlVals += Elem.GetAttribute("Value") & ", "
Next
Sql = Left(Sql, Len(Sql)-2) 'Remove last comma-space
SqlVals = Left(SqsVals, Len(SqlVals)-2) 'Ditto
Sql &= SqlVals & ")"
MyConnection.Execute(Sql)

This pseudocode basically builds a SQL string from the active XML elements, then executes it, thus inserting a record.

This method may be the best, if I can get it to work properly.  I was hoping there may be a more direct way to accomplish this, but so far cannot figure that out.  Do you think the above method should work?

Thanks...

---

Ron Mittelman

Answer 9

Ok, I think I have this figured out, without any datasets or datatables.

    Public Function SavePLCData() As Boolean
        Dim isOK As Boolean
        Dim sqlCommand As String

        'Build SQL command.
        Dim sbSQL As New StringBuilder("Insert into history (")
        Dim sbVals As New StringBuilder(") Values(")
        Dim dataTypes As XmlNodeList = _settings.SelectNodes("//data_type")
        For Each dataType As XmlElement In dataTypes
            Dim dataTypeId As String = dataType.GetAttribute("Id")
            If dataTypeId.StartsWith("conveyor_plc") Then

                Dim plcTypes As XmlNodeList = dataType.SelectNodes("plc_type")
                For Each plcType As XmlElement In plcTypes
                    Dim plcTypeId As String = plcType.GetAttribute("Id")
                    Dim myItems As XmlNodeList = plcType.SelectNodes("Item[@Active='True']")
                    For Each myItem As XmlElement In myItems

                        'Append to fields list.
                        sbSQL.Append(plcTypeId)
                        sbSQL.Append("_")
                        sbSQL.Append(myItem.GetAttribute("Id"))
                        sbSQL.Append(",")

                        'Append to values list (presently none are String types).
                        Dim myType As String = myItem.GetAttribute("Type")
                        Dim myValue As String = myItem.GetAttribute("Value")
                        If myType = "Boolean" Then
                            Dim result As Boolean
                            If Boolean.TryParse(myValue, result) Then
                                myValue = result.ToString
                            Else
                                myValue = "False"
                            End If
                        Else
                            If myValue = "" Then
                                myValue = "0"
                            End If
                        End If
                        sbVals.Append(myValue)
                        sbVals.Append(",")

                    Next 'Item.
                Next 'plcType.

            End If 'Valid dataTypeId.

        Next 'dataType.
        If sbSQL.Length <> 0 Then

            sbSQL.Remove(sbSQL.Length - 1, 1)
            sbVals.Remove(sbVals.Length - 1, 1)
            sbVals.Append(")")

            sqlCommand = sbSQL.ToString & sbVals.ToString

            Dim connString As String = "server=localhost;User Id=root;Password=root;database=strawberryfield"
            Using myConn As New MySqlConnection(connString)
                Using myCmd As New MySqlCommand
                    With myCmd
                        .CommandText = sqlCommand
                        .Connection = myConn
                        .CommandType = CommandType.Text
                    End With
                    Try
                        myConn.Open()
                        myCmd.ExecuteNonQuery()
                        isOK = True
                    Catch ex As Exception
                        isOK = False
                        _lastError = ex.Message
                    Finally
                        myConn.Close()
                    End Try
                End Using
            End Using


        End If 'Collected any items.
        Return isOK

    End Function

I tried a loop to call this function 1,500 times, and it successfully wrote those records into a MySQL table in 4-6 seconds, much faster than I need to worry about.

Thanks for all the help...

---

Ron Mittelman