Ok so I admit it, I like serialization. In fact, I've started to use it alot. I know, I know, I lose some performance, but hey who wants to write:
private static MyObject LoadMyObject(IDataReader reader)
{
MyObject obj = new MyObject();
obj.ObjID = (int)reader["ObjID"];
if (reader["MyVal"] != DBNull.Value)
obj.MyVal = (string)reader["MyVal"];
...
return obj;
}
When instead you can use the following code to load all of your objects:
public static Object LoadObject(XmlReader reader, Type type)
{
XmlSerializer xs = new XmlSerializer(type);
Object o = null;
if (xs.CanDeserialize(reader))
{
o = xs.Deserialize(reader);
}
return o;
}
Not only does it handle all my objects (including collections), but if I want to add/remove columns I can just add/remove column in my stored procedure. Of course you do pay for this, but not too heavily.
Ok, so I've been having fun with serialization and everything was going great until I came across the need to store images in the database. What's wrong with images you say, well if you've worked with “for xml” queries you know that images don't translate well to XML. If images don't translate to XML, then they can't be translated to objects using serialization.
Since I read most of the questions in the sqlserver.xml newsgroup, I know that people have a hard time with this one. So I decided to try to tackle this in a way that would allow me to populate image data in an object via the serializer that would be a generic approach that would (hopefully) work in most situations.
So first, if you have an image column in your table and you run a “for xml auto” query against it, what do you get back in place of the image? You get XPath. For example:
select employeeid, photo
from employees
where employeeid = 1
for xml auto
Returns...
I'm getting the XPath to the column I want. So with that information in mind I was able to tackle this problem and load the image using the XPath.
First, in my object, which in this case would my employee object, I need to ignore the property. This is done using the XmlIgnoreAttribute of the System.Xml.Serialization namespace. Below is my Photo property.
///
/// Property Photo (Byte[])
///
[XmlIgnore]
public Byte[] Photo{
get {return this.bits;}
set {this.bits = value;}
}
Next I need to modify my object deserializer code so that I can catch the missing column that has my XPath in it.
public static Object LoadObject(XmlReader reader, Type type)
{
XmlSerializer xs = new XmlSerializer(type);
Object o = null;
xs.UnknownNode += new XmlNodeEventHandler(xs_UnknownNode);
if(xs.CanDeserialize(reader))
{
o = xs.Deserialize(reader);
}
return o;
}
Now when the serializer gets to my Photo attribute which contains the XPath, instead of trying to stick the XPath string value into the Employee Photo property it will hit my event handler xs_UnknownNode. This will allow me to take the XPath and get the bits from the photo column and populate my Employee object with the bits. Below is the part of the code that works this magic.
private static void xs_UnknownNode(object sender, XmlNodeEventArgs e)
{
// Used to load Image data
Type type = e.ObjectBeingDeserialized.GetType();
foreach (PropertyInfo pi in type.GetProperties())
{
if (pi.Name == e.Name && pi.PropertyType == typeof(Byte[]))
{
// unknown type is byte which can't be passed in XML
// so we need to load it directly from the database.
pi.SetValue(e.ObjectBeingDeserialized, LoadDbObject(e.Text), null);
}
}
}
I said this snippet does part of the magic. In the code above reflection is used in order to get the properties of the object we are trying to load. The one we are looking for is a type of Byte[] and has the same name. Once we find that property we can set its value to the bits stored in Sql Server using LoadDbObject. The LoadDbObject method is what is used to parse the XPath and query the database directly. Below is the code that does this.
private static Byte[] LoadDbObject(String xPath)
{
Byte[] bits = null;
// The first part of the string should be dbobject so we can strip this off
xPath = xPath.Substring(8);
Int32 firstBracket = xPath.IndexOf("[");
Int32 lastBracket = xPath.LastIndexOf("]");
// The table name is after the first slash and before the first bracket
String table = xPath.Substring(1, firstBracket - 1);
// The column name is the last part of the xpath
String column = xPath.Substring(lastBracket + 2).Replace("@", String.Empty);
// The where clause is between the two brackets
String where = xPath.Substring(firstBracket + 1,
lastBracket - (firstBracket + 1)).Replace("@", String.Empty).Replace("%20", " ");
// Create the SQL Statement
String sql = String.Format("select {0} from {1} where {2}", column, table, where);
SqlDataReader reader = SqlWebDal.ExecuteReader(CommandType.Text, sql);
if (reader.Read())
{
bits = (Byte[])reader[0];
}
reader.Close();
return bits;
}
The SqlWebDal is just an implementation of the SqlHelper class found in .Text. It returns a reader that has one column which is our Image column. Then we set the bits variable to the value of this column. The column is now populated.
Ok so I cheated a little and didn't really use serialization to load the entire object, but I came pretty close. I haven't done any performance tests on this code yet so I could be in for an ugly surprise. It seems to perform pretty well and it seems to be generic enough to work for most objects.
All in all, I'm still having fun with deserializing...