nPath Ltd.

In this post I will discuss how I stored a list of strings in a single database field as xml.

The application I’m working on has a list of email addresses for each contact. Rather than creating a new table with forgein keys, stored procedures, Data access componants, and business layer logic I decided to take a regular email field and expand it to store a list of emails. My Datastore is SQL Server. I only changed the field to nvarchar(max).

To save the list

1. edit the list as needed
2. pull the listbox.items collection and convert to an array
3. Serialize the array and store the text in the database

To get it back

1. read the text from the database
2. de-serialize to an array
3. load the array as items in the listbox

The issue I had was that the xmlserializer works with streams. This I was not used to. I needed to convert from string to stream and backagain. Looking back it isn’t hard but for me it was a learning curve.

Here is the code then pulls the listbox and converts the items to an XML string:

        'create and populate an array, the items collection is not serializable
         Dim emails As New ArrayList()

For Each itm As ListItem In ListBox1.Items
emails.Add(itm.Value)
Next

‘create an XmlSerializer and use it to populate a memory stream
Dim xs As New System.Xml.Serialization.XmlSerializer(GetType(ArrayList))
Dim ms As New System.IO.MemoryStream
Try
xs.Serialize(ms, emails)
Catch ex As Exception
End Try

‘rewind the stream
ms.Seek(0, 0)

‘read the stream to string, I use a StreamReader to take advantage of ReadToEnd
Dim sr As New System.IO.StreamReader(ms)
Dim str As String = sr.ReadToEnd

‘now we can save the string to a database

Here is the code that re-populates the list from an XML string

‘we’ll start with a string of xml called str. I’ll assume it’s already pulled from the database

       'To convert a string to a stream  we have to convert to a byte array first.
       'aStr is str converted to an array of bytes
        Dim uniEncoding As New UnicodeEncoding()
        Dim aStr As Byte() = uniEncoding.GetBytes(str)

      'wrtie the byte array to a stream and rewind it

        Dim ms As New System.IO.MemoryStream
        ms.Write(aStr, 0, aStr.Length)
        ms.Seek(0, 0)

      'de-serialize from xml to an array of strings
        Dim emails As New ArrayList()
        Dim xs As New System.Xml.Serialization.XmlSerializer(GetType(ArrayList))

        Try
            emails = xs.Deserialize(ms)
        Catch ex As Exception
        End Try

      'load the array into the listbox's items collection
        ListBox1.Items.Clear()
        For Each itm As Object In emails
            ListBox1.Items.Add(itm.ToString)
        Next