These days there seems to be an infinte number of ways to take data returned from the database and map them into your object model.
This article aims to simple show you how to generically map the sql select result set into a list of object instances in your code.
First things first, the code illustrated here requires Sql Server 2005, and .Net 2.0+ framework.
Sql Server 2005 Queries
It doesn’t matter whether you write you queries in code or by using Sql Server SProcs, simple by adding - ‘FOR XML Path(’ElementName’) - the following to the end of you Select statement, Sql Server will return your result sets in Xml.
Syntax:
SELECT * FROM [TableName] WITH (nolock) FOR XML PATH('TableName')
Example:
SELECT * FROM [Channel] WITH (nolock) FOR XML PATH('Channel')
Results:
<Channel>
<Id>1</Id>
<Type>web</Type>
<Description>standard internet</Description>
</Channel>
<Channel>
<Id>3</Id>
<Type>mobile</Type>
<Description>mobile</Description>
</Channel>
The parameter passed into the Path() statement determines the top level element name - allowing you to shape the xml.
The Entity POCOs
Using the .Net built in xml to class deserialization process, the returned xml will map the top level element to a class name and each sub-element to a public property/member on the object class.
Let’s create the Plain Old C# Object to enable deserialization.
/// Represents a media channel (e.g. web, mobile, iphone, etc.)
public class Channel {
private int _id;
private string _type, _description;
public Channel(){}
public Channel(string type, string description) {
_type = type;
_description = description;
}
/// Get/Set the channel identifier
public int Id {
get { return _id; }
set { _id = value; }
}
/// Get/Set the channel type (e.g. web, mobile, iphone, etc.)
public string Type {
get { return _type; }
set { _type = value; }
}
/// Get/Set the description of the channel.
public string Description {
get { return _description; }
set { _description = value; }
}
}
NOTE: If there are differences in your database schema naming conventions to how you name things in your classes you will need to shape the xml somewhere to make the deserialization process work.
You have two options: you can decorate your class with appropriate Xml Serialization Attributes OR you can change your Sql Query.
Example: the Channel table has a column name desc but you need to map to the public property name Description.
Scenario 1: Shape the xml using the [XmlAttribute]
[XmlAttribute("desc")]
public string Description {
get { return _description; }
set { _description = value; }
}
OR
Scenario 2: Shape the xml in your Sql Query
SELECT [Id], [Type], [Desc] as Description
FROM [Channel]
WITH (nolock)
FOR XML PATH('Channel')
Data Access Code
Ok. You know how to get the data back from the DB in Xml. Now it’s time to read the results into a list of the entity POCOs.
private static readonly IDictionary xmlSerializers = new Dictionary();
/// Generic helper method for reading sql server table rows into entities.
internal IList GetEntities(DbCommand dbCommand) {
IList entities = new List();
using (XmlReader reader = ((SqlDatabase) Database).ExecuteXmlReader(dbCommand)) {
while (!reader.EOF) {
if (reader.IsStartElement()) {
entities.Add(Deserialize(reader.ReadOuterXml()));
}
}
}
return entities;
}
/// Deserialize an xml fragment into the specified Type.
public T Deserialize(string xml) {
using (StringReader sr = new StringReader(xml)) {
if (!xmlSerializers.ContainsKey(typeof (T))){
xmlSerializers.Add(typeof (T), new XmlSerializer(typeof (T), null, new Type[0], null, null));
}
XmlSerializer serializer = xmlSerializers[typeof (T)];
return (T) serializer.Deserialize(sr);
}
}
NOTE: I cache an XmlSerialzer instance for each Type because the .Net framework generates a class dynamically at runtime when the new XmlSerializer() constructor is called. If you have thousands of results coming back from the DB this is timely and memory consuming.
Usage Examples:
/// Get a specific Channel.
public Channel GetChannel(string name) {
IList list = GetEntities(Database.GetStoredProcCommand(”usp_GetChannels”, 0, name));
return (list != null && list.Count == 1) ? list[0] : null;
}
/// Get a list of all Channels.
public IList GetChannels() {
return GetEntities(Database.GetStoredProcCommand(”usp_GetChannels”, 0, null));
}
That’s it!
Update: 18-Feb-08 I’ve written a followup article that shows how to do the reverse process - Persisting objects into database using xml serialization
- until it came to deploying the updated database. Turns out you can’t export a 2005 backup and restore it onto 2000 - no real surprises there! So my next option was to generate scripts to recreate the database on a SQL Server 2000 server, but guess what - the Generate Scripts tool in 2005 does NOT generate scripts compatible with 2000. Now that was a surprise, given that one of the options actually allows you to specify that the scripts are for Sql Server 2000!