How do I set the DateTimeMode property when filling a DataTable

[kenandcorey]

[ I posted this in Microsoft.Public.Data.Ado, but I haven't had a
response. I thought I would try here too. ]

I am investigating how best to manage UTC timestamps in ADO.Net. We
ran into a lot of issues with UTC DateTime serialization in .NET 1.1.
With .NET 2.0, there are new properties to help identify when a
DateTime (as part of a DataTable or as a regular object) is UTC or
local.

I have been able to handle all cases so far except one. In our
database we store all timestamps in UTC. I want to retrieve the data
into a DataTable whose column has the DateTimeMode property set to
DataSetDateTime.Utc. I can't figure out how to do this.

Any suggestions?

I have tried setting the DateTimeMode after the DataTable has been
populated, but that causes an exception. I haven't been able to find
anything in SQL Server 2005 that would allow me to specify that a
DateTime column is a "UTC" DateTime. The only way I've been able to do
it is by adding the columns manually before calling adapter.Fill(). I
hope there is a better way.

Here is my test code. I know it is poor coding, but it is only a test.
For simplicity, I just use a raw SQL statement.

SqlConnection conn = new
SqlConnection(@"server=(local);database=master;uid=sa;pwd=password;");

SqlCommand cmd = new SqlCommand("SELECT GetDate() AS local,
GetUTCDate() AS utc", conn);
cmd.CommandType = CommandType.Text;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable("TryIt");
dt.Columns.Add("utc", typeof(DateTime));
dt.Columns["utc"].DateTimeMode = DataSetDateTime.Utc;
dt.Columns.Add("local", typeof(DateTime));
dt.Columns["local"].DateTimeMode = DataSetDateTime.Local;

try
{
adapter.Fill(dt);
dt.Columns["utc"].DateTimeMode = DataSetDateTime.Utc;
dt.Columns["local"].DateTimeMode = DataSetDateTime.Local;
}

catch (Exception)
{

throw;

}

DateTime local = (DateTime)dt.Rows[0]["local"];
DateTime utc = (DateTime)dt.Rows[0]["utc"];

 

[kenandcorey]

No reply at all (so far), so I guess that is the best one can do at
this time.