How do I set the DateTimeMode property when filling a DataTable
- From: kenandcorey@xxxxxxxxx
- Date: 31 Jan 2006 08:59:04 -0800
[ 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"];
.
- Prev by Date: Bug in TableAdapter Code Generator?
- Next by Date: Generating an SqlParameter from a value in a DataSet
- Previous by thread: Bug in TableAdapter Code Generator?
- Next by thread: Generating an SqlParameter from a value in a DataSet
- Index(es):
Relevant Pages
|
|
