Serialization of UTC DateTime from SQL database is still awkward

Although treatment of UTC DateTime in .NET 2.0 is significantly improved, handling data selected from SQL database is still awkward.


If you try to serialize a DataSet selected freshly from SQL database, value of a DateTime column will look like this:

<starttime>2006-12-19T15:57:13-08:00</starttime>

While, I really want to see something like this:
<starttime>2006-12-19T15:57:13Z</starttime>

This behavior is to keep compatibility with .NET 1.1. However, trying to change this default behavior is awkward. The best I can find is to change DataSet schema before rows are added (after rows are added, you can only change between "Unspecified" and "UnspecifiedLocal"). Anyway, here are a couple of lines of code that eventually helped me to get the desired output:

   1:        /// <summary>
   2:        /// fills the dataset using the select command. 
   3:        /// In this function, we assume all DateTime in database is UTC, and fixed
   4:        /// types here.
   5:        /// </summary>
   6:        public int FillDataSet(DataSet dataSet)
   7:        {
   8:            int numRowAffected = 0;
   9:            SqlConnection connection;
  10:            SqlCommand command;
  11:            //code to initiate connection, command
  12:          
  13:            connection.Open();
  14:            SqlDataAdapter sqlDA = new SqlDataAdapter();
  15:            sqlDA.SelectCommand = command;
  16:            sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
  17:          
  18:            //a detour: 
  19:            //it is still awkward to serialize UTC DateTime from database.
  20:            //fix DateTime type, assuming all are saved in UTC time
  21:            //we cannot change type after rows are added, so 
  22:            sqlDA.FillSchema(dataSet, SchemaType.Source);
  23:            Utilities.SetDateTimeMode(dataSet, DataSetDateTime.Utc);
  24:          
  25:            numRowAffected = sqlDA.Fill(dataSet);
  26:            connection.Close();
  27:            return numRowAffected;
  28:        }
  29:      
  30:        /// <summary>
  31:        /// Set all the data columns to speicified mode.
  32:        /// (only Unspecified or UnspecifiedLocal can be set after rows are added.)
  33:        /// </summary>
  34:        /// <param name="data"></param>
  35:        /// <param name="dtMode"></param>
  36:        public static void SetDateTimeMode(DataSet data, DataSetDateTime dtMode)
  37:        {
  38:            foreach (DataTable dt in data.Tables)
  39:            {
  40:                SetDateTimeMode(dt, dtMode);
  41:            }
  42:        }
  43:      
  44:        /// <summary>
  45:        /// Set all the data columns to speicified mode.
  46:        /// (only Unspecified or UnspecifiedLocal can be set after rows are added.)
  47:        /// </summary>
  48:        /// <param name="dt"></param>
  49:        /// <param name="dtMode"></param>
  50:        public static void SetDateTimeMode(DataTable dt, DataSetDateTime dtMode)
  51:        {
  52:            foreach (DataColumn col in dt.Columns)
  53:            {
  54:                if (col.DataType == typeof(DateTime))
  55:                {
  56:                    col.DateTimeMode = dtMode;
  57:                }
  58:            }
  59:        }

This newsgroup thread has help me develop the above strategy: How do I set the DateTimeMode property when filling a DataTable. It is posted on microsoft.public.dotnet.framework.adonet