How do I set the DateTimeMode property when filling a DataTable

I am investigating how best to manage UTC timestamps in ADO.Net. We
ran into a lot of issues with 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.

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");

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"];

.

Relevant Pages