How do I set the DateTimeMode property when filling a DataTable

[Ken Wright]

[This is a repost of a message from a year ago. I was told that I will
receive a response if I post under my MSDN e-mail address.]

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

Thank you.

[WenYuan Wang]

Hi,

According to your description, I understand you want to know what is the
best way to the set DateTimeMode property when filling a DataTable. If I
misunderstand anything here, please don't hesitate to correct me.

As far as I know, the DateTimeMode cannot be modified after rows are added
to a DataColumn.
[DataColumn.DateTimeMode Property]
http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.datetimemode
aspx

In general, we always use this property for Typed Dataset. For this reason,
we will defined all property of table and then fill the data into it. But
if you want to retrieve data from database and set DateTimeMode property of
column, I can think the best way to achieve this is filling schema into
table , set DateTimeMode property, and then fill data into it.
For example:
SqlConnection conn = new SqlConnection("...");
SqlCommand cmd = new SqlCommand("...");

SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable("TryIt");

//*****************
adapter.FillSchema(dt, SchemaType.Source);

dt.Columns["utc"].DateTimeMode = DataSetDateTime.Utc;
dt.Columns["local"].DateTimeMode = DataSetDateTime.Local;

//*****************
adapter.Fill(dt);

Please feel free to reply me if you have any concern on it and I'm glad to
assist you.

Have a great weekend,
Best regards,
Wen Yuan Wang
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

[Ken Wright]

Thanks for answering so quickly, and you did understand my question
correctly. Your answer was what I was expecting. I wish the property could
be set AFTER the data has been loaded, but it sounds like it was designed
that way and I just have to set up the schema ahead of time.

Ken

""WenYuan Wang"" wrote:

> Hi,
>
> According to your description, I understand you want to know what is the
> best way to the set DateTimeMode property when filling a DataTable. If I
> misunderstand anything here, please don't hesitate to correct me.
>
> As far as I know, the DateTimeMode cannot be modified after rows are added
> to a DataColumn.
> [DataColumn.DateTimeMode Property]
> http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.datetimemode

> .aspx

[WenYuan Wang]

Hi Ken,
Thanks for your reply.

I'm very glad to hear my suggestion is helpful for you. You are welcome.
Please feel free to reply me if you have any further problem.

Have a great day.
Best regards,
Wen Yuan