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