XML can be a wonderful thing. Storing XML in SQL Server can be wonderful, too. It gives you a place to store a lot of data in one field and is especially useful if that data is considered one unit of data. A good example of this would be storing user preferences, for example, a stripped down table like:
So that’s one field: preferences. Now let say that we have some items we want to store, like a few general preferences and some dialog boxes where the user checked the “Do not show this message again” option:
Public Class UserPreferences
Private _UserID As Integer
Private _General As GeneralPreferences
Private _DoNotShowMessages As DoNotShowMessagesPreferences
Public Property General As GeneralPreferences
Get
If _General Is Nothing Then _General = New GeneralPreferences
Return _General
End Get
Set(ByVal value As GeneralPreferences)
_General = value
End Set
End Property
Public Property DoNotShowMessages As DoNotShowMessagesPreferences
Get
If _DoNotShowMessages Is Nothing Then _DoNotShowMessages = New DoNotShowMessagesPreferences
Return _DoNotShowMessages
End Get
Set(ByVal value As DoNotShowMessagesPreferences)
_DoNotShowMessages = value
End Set
End Property
Public Sub New()
End Sub
Private Sub New(ByVal userID As Integer)
_UserID = userID
End Sub
Public Class GeneralPreferences
Public Property ShowSplashScreen As Boolean
Public Property UseAlternateColorScheme As Boolean
Public Property NumberOfItemsinGrids As Integer
End Class
Public Class DoNotShowMessagesPreferences
Public Property HideNoResultsMessage As Boolean
Public Property HideCloseConfirmation As Boolean
End Class
End Class
This gives us two nested classes that store our values in nice groups, held in a class that allows us to access those nested classes and set the values. Now we want to create an XML document that we can save and load in SQL. So saving would be something like:
Public Sub SaveXML()
Dim doc As Xml.XmlDocument
Dim parentNode As XmlNode
Dim childNode As XmlNode
Dim parms As New Generic.List(Of SqlClient.SqlParameter)
doc = New XmlDocument
doc.LoadXml("<UserPreferences />")
parentNode = doc.DocumentElement.AppendChild(doc.CreateNode(XmlNodeType.Element, "General", doc.NamespaceURI))
childNode = parentNode.AppendChild(doc.CreateNode(XmlNodeType.Element, "ShowSplashScreen", doc.NamespaceURI))
childNode.InnerText = Me.General.ShowSplashScreen.ToString
childNode = parentNode.AppendChild(doc.CreateNode(XmlNodeType.Element, "UseAlternateColorScheme", doc.NamespaceURI))
childNode.InnerText = Me.General.UseAlternateColorScheme.ToString
childNode = parentNode.AppendChild(doc.CreateNode(XmlNodeType.Element, "NumberOfItemsInGrids", doc.NamespaceURI))
childNode.InnerText = Me.General.NumberOfItemsInGrids.ToString
parentNode = doc.DocumentElement.AppendChild(doc.CreateNode(XmlNodeType.Element, "DoNotShowMessages", doc.NamespaceURI))
childNode = parentNode.AppendChild(doc.CreateNode(XmlNodeType.Element, "HideNoResultsMessage", doc.NamespaceURI))
childNode.InnerText = Me.DoNotShowMessages.HideNoResultsMessage.ToString
childNode = parentNode.AppendChild(doc.CreateNode(XmlNodeType.Element, "HideCloseConfirmation", doc.NamespaceURI))
childNode.InnerText = Me.DoNotShowMessages.HideCloseConfirmation.ToString
With parms
.Add(New SqlClient.SqlParameter("@UserID", _UserID))
.Add(New SqlClient.SqlParameter("@Preferences", doc.OuterXml))
End With
SqlHelper.ExecuteNonQuery(CONN_STRING, CommandType.Text, _
"insert userpreferences(userid,preferences) values(@UserID,@Preferences)", parms.ToArray)
End Sub
and would give us an xml document saved to the server like:
<UserPreferences>
<General>
<ShowSplashScreen>True</ShowSplashScreen>
<UseAlternateColorScheme>False</UseAlternateColorScheme>
<NumberOfItemsInGrids>3</NumberOfItemsInGrids>
</General>
<DoNotShowMessages>
<HideNoResultsMessage>False</HideNoResultsMessage>
<HideCloseConfirmation>True</HideCloseConfirmation>
</DoNotShowMessages>
</UserPreferences>
We’d also need a corresponding LoadXML method to read and parse out the XML and set the internal values. That seems pretty good and it prevents us from having to modify the database table every time we add a new preference.
But, in a more critical and more annoying way, we have to modify the SaveXML and LoadXML methods every time we add a new preference. Not only that, but we have to compensate for previously-saved versions of the XML, saved before new preferences were added, otherwise we’ll get errors when we try to read nodes that don’t exist. This is a path of misery and spaghetti.
Don’t be discouraged. There is an easy way. In fact, it’s so easy it’s near unbelievable. You add this code once and never change it. Add all the preferences/properties you want and the code works with whatever is available. It uses the XMLSerializer to do all the work.
First, all the classes need to be marked as <Serializable()>. Then we create a method to instantiate the UserPreferences class:
Shared Function GetInstance(ByVal userID As Integer) As UserPreferences
Dim up As UserPreferences
Dim ser As XmlSerializer
Dim dt As DataTable
Dim dr As DataRow
dt = SqlHelper.ExecuteDataset(CONN_STRING, CommandType.Text, _
"select preferences from userpreferences where userid=" & userID).Tables(0)
If dt.Rows.Count <> 0 Then
dr = dt.Rows(0)
ser = New XmlSerializer(GetType(UserPreferences))
up = TryCast(ser.Deserialize(New IO.StringReader(CStr(dr("Preferences")))), UserPreferences)
up._UserID = userID
Else
up = New UserPreferences(userID)
End If
dt.Dispose()
Return up
End Function
Then we add a public method to save the class:
Public Sub Save()
Dim ser As XmlSerializer
Dim xmlData As IO.StringWriter
Dim parms As New Generic.List(Of SqlClient.SqlParameter)
ser = New XmlSerializer(GetType(UserPreferences))
xmlData = New IO.StringWriter
ser.Serialize(xmlData, Me)
With parms
.Add(New SqlClient.SqlParameter("@UserID", _UserID))
.Add(New SqlClient.SqlParameter("@Preferences", xmlData.GetStringBuilder.ToString))
End With
SqlHelper.ExecuteNonQuery(CONN_STRING, CommandType.Text, _
"insert userpreferences(userid,preferences) values(@UserID,@Preferences)", parms.ToArray)
End Sub
Seriously, that’s it. Three lines to turn an object into XML. Two lines to create an object from XML. Missing and extraneous properties get skipped with no errors, which lets you change the classes whenever and however you need. And the structure of the XML is the same as shown previously, with child classes/properties as nested elements.
In a test app, you can load, change, and save preferences:
With the preferences in an object, the UI code is extremely simple – one of the great benefits to using objects:
Dim _prefs As UserPreferences
Private Sub cmdLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLoad.Click
_prefs = UserPreferences.GetInstance(CInt(txtUserID.Text))
With chkGeneralPreferences
.SetItemChecked(0, _prefs.General.ShowSplashScreen)
.SetItemChecked(1, _prefs.General.UseAlternateColorScheme)
End With
txtNumberOfItemsInGrid.Value = _prefs.General.NumberOfItemsInGrids
With chkDoNotShowMessages
.SetItemChecked(0, _prefs.DoNotShowMessages.HideNoResultsMessage)
.SetItemChecked(1, _prefs.DoNotShowMessages.HideCloseConfirmation)
End With
End Sub
Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
With _prefs.General
.ShowSplashScreen = chkGeneralPreferences.GetItemCheckState(0) = CheckState.Checked
.UseAlternateColorScheme = chkGeneralPreferences.GetItemCheckState(1) = CheckState.Checked
End With
_prefs.General.NumberOfItemsInGrids = CInt(txtNumberOfItemsInGrid.Value)
With _prefs.DoNotShowMessages
.HideNoResultsMessage = chkDoNotShowMessages.GetItemCheckState(0) = CheckState.Checked
.HideCloseConfirmation = chkDoNotShowMessages.GetItemCheckState(1) = CheckState.Checked
End With
_prefs.Save()
End Sub
