Serializing A DataSet With DateTime Columns

by Scosby Friday, March 1, 2013

The Database

I was recently working on a legacy web service that returns a DataSet. Sometimes, these DataSets include DateTime columns with values that come from a database (Figure 1). On the client, it was confusing to see time zone offsets that were not in the database values. The clients could be in different time zones than the web server, so this behavior was easily overlooked during development.

Figure 1 –A Date Value Record

The DataSet

I wondered where the offsets were being added (Figure 2). Clearly, they weren't part of the database. I discovered they are created by the DataSet based on the time zone of the computer where it is serialized. This happens automatically when the web service returns a DataSet to the client. It also happens if you call the DataSet.GetXml method. In either case, 2003-04-06T00:00:00 was becoming 2003-04-06T00:00:00-05:00 and creating the confusion (albeit a different instance in time!).

Figure 2 - DataSet XML With An Offset

I needed to find a way to control how the DataSet was serializing its DateTime columns. After visualizing one of the DataSets (Figure 3), I noticed a property on the DataColumn called DateTimeMode. All of the columns had this property, even the columns that were not DateTime columns. I noticed they were all set to UnspecifiedLocal and decided to investigate.

Figure 3 – Visualizing Columns In A DataSet

 

The Server-Side Fix

Usually, it is not possible to change the DateTimeMode of a column once a DataSet has rows. However, it is possible to change the mode between UnspecifiedLocal and Unspecified because that only affects serialization and not the actual value. This is exactly what I needed and so I wrote the following code on the server side:

System.Data.DataSet data = new DataSet();

 

// ...

 

// Iterate the columns and set DataColumn.DateTimeMode

// to DataSetDateTime.Unspecified to remove offset

// when serialized.

foreach (DataTable table in data.Tables)

{

    foreach (DataColumn item in table.Columns)

    {

        // Switching from UnspecifiedLocal to Unspecified

        // is allowed even after the DataSet has rows.

        if (item.DataType == typeof(DateTime) &&

            item.DateTimeMode == DataSetDateTime.UnspecifiedLocal)

        {

            item.DateTimeMode = DataSetDateTime.Unspecified;

        }

    }

}

This code is pretty straight forward. Changing the column’s mode to Unspecified results in a DateTime value with no offset. This value now matches the database record exactly. This leaves the problem of formatting the value for display completely up to the client-side application.

Figure 4 - DataSet XML With No Offset

Summary

This post discussed how to control the formatting of DateTime columns when a DataSet is serialized. I showed an example of the default behavior which includes the time zone information offset. Finally, I showed a code snippet that changes the behavior to exclude the offset.

Tags: , , ,

IT | Programming

blog comments powered by Disqus