Feed Icon  


  • Bryant Likes
  • Send mail to the author(s) E-mail
  • twitter
  • View Bryant Likes's profile on LinkedIn
  • del.icio.us
Get Microsoft Silverlight
by clicking "Install Microsoft Silverlight" you accept the
Silverlight license agreement

Hosting By

Hot Topics


Open Source Projects



Fun With Deserializing

Posted in Sql and Xml at Monday, November 17, 2003 9:19 AM Pacific Standard Time

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


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[])
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);

    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];
  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...


Monday, November 17, 2003 4:11:00 PM (Pacific Standard Time, UTC-08:00)
Personally I dislike putting images into the database itself for the following reasons:
<br>1. Small rows that could have been put on one page ( 4k ) would be now split over several pages because of the size of the image. This adversely affects performance.
<br>2. Large images would be more appropriately stored on a single server. Having scaled out a database, it makes much more sense to just refer to a URI for the image such as:
<br>3. For the most part, no processing is done on images beyond saving them to disk, retrieving them, and deleting them. The most common operation begin, of course, retrieval. Consider your average webpage, wouldn't it be easier to just put in a tag like:
<br><img src="//ImageServer/NorthwindDB/Products/ChaiZoom.jpg" />
<br>than to actually read byte by byte from the db the image, and then write it out byte by byte to the webpage ?
<br>Of course, if you do need to perform image processing, it <u>may</u> be appropriate to store it in the db. This is rare, from my experience.
Tuesday, November 18, 2003 1:33:00 AM (Pacific Standard Time, UTC-08:00)
Thanks for the feedback!
<br>I do agree, you take a performance hit by storing images in the database. However, I think it is a fair price to pay for the managability you get from storing images in this way. For instance, things like website portals or intranet sites where you have users managing images, it seems more robust to use a database.
<br>I generally try to have a separate images table which helps (I assume) avoid some of the performance issues associated with page splits. Maybe for my next blog article I write up some performance stats to see what kind of price I'm paying.
Comments are closed.