This technique allows you to easily expand the types of information your database holds without altering the database and with only minimal code additions. In fact, once everything is set up, to start persisting a new property to the database means simply adding that property to the
POCO.
Create your Database
The only special thing that happens here is that each table that you want to store XML data in will need a column to store the XML string for each record.
An example table might look like this:
CREATE TABLE Employee
(
EmployeeID INT NOT NULL AUTOINCREMENT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
XMLProperties XML
);
Notice the 4th column: "XMLProperties" and notice the datatype "XML".
Anything extra that we want to store for an Employee object will be stored in this XMLProperties field. The management of this XML data will all happen in code. That's all there is to it for the database.
Create your objects
The first step is to create your POCO objects. I will use the LINQ-to-SQL tool in VS to automatically generate my objects.
The LINQ-to-SQL tool generates partial classes (a class for each database table).
We will take advantage of this and create supplementary partial classes to add those extra properties we wish to store in XML.
Add Properties to be stored as XML
Your POCO's will have properties for the columns of your associated SQL table in the generated partial classes. Lets make our own supplemental partial classes and add our extra properties that will be persisted to XML:
[DataContract(Name = "Employee", Namespace = "http://www.contoso.com")]
public partial class Employee
{
[DataMember(Name = "DOB")]
public DateTime DOB { get; set;}
[DataMember(Name = "Telephone")]
public String Telephone { get; set;}
}
Notice the two attributes we are using here [DataContract] and [DataMember]. [DataContract] specifies that the class can be used by .NET's DataContractSerializer, and [DataMember] specifies which properties will be serialized.
Have some way of fetching them and re populating the POCO
Now comes the tricky part, getting the data in and out seamlessly. While there may be more refined methods of doing this, my solution consists of 2 parts:
2) A static "Adapter" utility class to serialize and deserialize the objects to and from XML.
I plan on writing my own post on custom model binding in the future. If you are not familiar with the subject, see the link above.
The Adapter Class (Psudo code)
public static class XMLAdapter
{
public static LoadXMLProperties(T Model)
{
foreach(Property p in T.GetProperties())
{
var temp = Deserialize(Model.XMLProperties);
//use .NET's reflection framework to get and set the properties
if(p is a "datamember property")
{
Model.p.value = temp.p.value;
}
}
}
public static SaveXMLProperties(T Model)
{
//only the properties with the [DataMember] attribute will be serialized.
Model.XMLProperties = Serialize(Model);
}
}
In our custom model binder's Bind() method, we will call our SaveXMLProperties(T Model) method on the incoming model. This will take all of our XML backed properties and save them as an XML string in the XMLProperties field of our POCO. Now, when the POCO is persisted, its XML Backed properties will be saved as well.
To repopulate these XML Backed properties into the POCO (when fetching them from the database), we can call the LoadXMLProperties(T Model) on the object in our code wherever it is loaded from the database (such as in the controller).
I plan to revisit this topic later in greater detail.
Hopefully you found this post helpful, if for nothing else than to push you in the right direction.
Post your questions and comments below! Thanks.
