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