Improve DataObject Serialization Performance and the Gotchas

While working on a custom reporting app I came across a little ADO.Net serialization gotcha, when we use WriteXML() and ReadXML for serializing a DataSet to stream, any column with null values in all rows will be magically removed from the de-serialization! Ina  service arena, this probably isn't an issue, however when dealing with service consumers that include CSV output for users or HTML, dropping columns is a big NO NO, at least to a user, Yes the data is blank, and who really cares to look at a blank column, well report users LOVE their columns and having one disappear will result in a bug task.

After digging around, I found this stackoverflow that describes the issue, with an old invalid KB reference. The solutions remarked here are not really attractive by any means, certainly not looping over a result set just to add a String DataColumn with Empty string , yuck, Im gonna try a little Hack just to get by today: If DataTable.Rows.Count > 0, then for each DataColumn that is DBNull, set to empty string to see if that works for me. Better yet, after looking at the .Net FW, there's a param avail with 3.5+ (I believe) that does the trick:

 //convert to xml with the DataSet schema:
        StringWriter writer = new StringWriter();
        ds.WriteXml(writer, XmlWriteMode.WriteSchema);
        string xml = writer.ToString();




What the impact is on the string size Im not sure, will need to test performance before and after,

In peruzing related articles I came across a better final solution which will involve switching over to JSON, this article is a good ref for that:

8 ways to improve ASP.NET Web API performance
http://blog.developers.ba/8-ways-improve-asp-net-web-api-performance/

Lightweight DataTable Serialization
http://blogs.msdn.com/b/shitals/archive/2009/12/04/9932598.aspx