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



Still Deserializing

Posted in Sql and Xml at Wednesday, November 19, 2003 11:54 PM Pacific Standard Time

Ok this will have to be my last post on this topic for awhile, otherwise even I might get bored with it. I received one more comment about my latest post that suggested the test scores were probably farther apart because I was using my local machine as the database. So I decided to run the tests again this morning, but instead of my local database I decided to hit the sqlxml.org server via my VPN connection to it. This should be a good simulation of a busy network since I'm going through the Internet to the remote location where my server is in order to hit my database.

Notice that the results are for one iteration, not 10 last the last test. Now one possible snag that I thought of which might be skewing these results would be the fact that all the data is encrypted as it moves between the servers. So the Base64 encoding would probably take a bigger hit since there would be more data. (Also there is some funny data in that database because I let people edit it as a demo on the sqlxml.org site)

Anyhow, I think it is interesting to see the results of using these different methods via the different connections. So that's all for now. If anyone is interested in other aspects of the test let me know and I look into it.

More Fun with Deserializing

Posted in Sql and Xml at Tuesday, November 18, 2003 5:50 AM Pacific Standard Time

Ok so I've gotten some feedback on my deserializing post, but I also received a suggestion. It was suggested that instead of making the separate SQL call to load the image I should just encode the image as Base64 data and return it with the XML (this can be done using "for xml auto, binary base64").

So it was back to my Sql2Class application to add in the photo field from the Northwind database. So I added a PictureBox to my form and a Photo property of type Byte[] to my Employee class.

When I tried loading the Byte[] into the PictureBox using Image.LoadFromStream it failed and gave me an error about invalid data. I found this kb article about the photo column in the employee database not being directly mappable to the Image type in .Net because of a header on the file. Ok, I've worked with headers before, so why not just remove it. By using a for loop I was able to figure out that if you skip the first 78 bytes of the data in the photo column then you will get a valid image.

Ok so now I have a valid image which I could load and display on my form. Next I added another button to load the image using Base64 encoding instead of my previous method. This was done by simply changing the “for xml” part of the query to “for xml auto, binary base64”. Next I had to modify my employee class to accept the encoded photo image which I did as follows:



/// Property EncodedPhoto (String)





public String EncodedPhoto



get {return Convert.ToBase64String(photo);}


set {photo = Convert.FromBase64String(value);}


This will allow the serializer to set the value of this field since I have aliased it in my for xml auto query as EncodedPhoto.

Now I'm ready to run my tests to see what kind of performance I'll be getting. Below is a screenshot of my test appliation.



Well as you can see, there is a pretty big performance hit when you encode the bits using Base64 in SQL Server. Granted, you have a cleaner solution (which can also be exported outside the bounds of your application).

Back from the PDC

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

BTW - I am back from the PDC. If you're one of the three people that actually reads my blog you might have been under the impression that aliens abducted me from the PDC and that I would never be seen again. But alas I have just been too lazy busy to post anything recently. I promise I will take down my PDC banner and countdown timer today soon.

I had a great time at the PDC and enjoyed meeting a lot of people. I think one of the highlights for me was meeting many of the guys on the SqlXml and ObjectSpaces dev teams (and of course hearing that the XmlDocument is dead). I went to the PDC with no idea of where SqlXml was going and left knowning that SqlXml is far from dead but is actually going places in Whidbey.

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