如何使用默认为 DateTimeKind.Utc 的 DateTime 值进行 DataSet.Fill？
我有一个应用程序，它可以从 SQL 读取数据并通过 WCF 将其发送到客户端应用程序，其方式类似于：
SqlDataAdapter da = new SqlDataAdapter( cmd )
DataSet ds = new DataSet();
da.Fill( ds );
return ds;
所有日期/时间都以 UTC 形式存储在数据库中。我注意到，如果运行应用程序的计算机上的时钟出现偏差，则客户端收到的日期/时间也会出现偏差。看来，如果 DateTime 类型是未指定的类型，WCF 会在内部将其表示为本地时间，并按此发送，因此应用程序和客户端之间的任何时间差都会导致日期/时间发生变化。
我当然可以在检索数据集时对其进行检查并修复日期/时间字段，但是这里有人会想到一些更好的方法来填充数据集，以便每个 DateTime 字段都会自动成为 da.Fill() 上的 DateTimeKind.Utc 吗？
I have an application which would read data from SQL and send it via WCF to a client app, in a manner similar to this:
SqlDataAdapter da = new SqlDataAdapter( cmd )
DataSet ds = new DataSet();
da.Fill( ds );
return ds;
All the date/times are stored in the database as UTC. What I noticed is that if clock on computer running the application is skewed, the date/times received by clients will be skewed as well. It seems that in case when DateTime type is of unspecified kind, WCF will represent it as local time internally, and send as such, so any time difference between the application and the client will cause date/time to shift.
I could certainly go through datasets as they are retrieved and fix date/time fields, but would anybody here think of some better way to fill the dataset, so that every DateTime field would automatically be DateTimeKind.Utc on da.Fill()?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您使用的是 SQL Server 2008，那么“正确”的解决方案是使用 SQL
datetimeoffset数据类型而不是 SQL 的 datetime。datetimeoffset是 SQL 2008 中新增的时区感知日期/时间类型，将在 ADO.NET 中转换为 CLRSystem.DateTimeOffset类型始终相对于 UTC。这是博客文章有关于此的更多详细信息。 MSDN 上DateTimeOffset< 的前几个搜索结果/code>提供额外的背景信息。如果您无法更改 SQL 架构，ADO.NET 有一个针对这种情况定制的属性：
DataColumn.DateTimeMode，控制数据集序列化时是否添加本地时区（DateTimeMode=DataSetDateTime.UnspecifiedLocal，这是默认值）或是否在序列化时不添加时区信息 (DateTimeMode=DataSetDateTime.Unspecified)。你想要后者。如果我对 MSDN 文档的阅读是正确的，您应该能够在填充数据集后为相关的
DataColumn设置DateTimeMode=DataSetDateTime.Unspecified。这应该序列化没有时区的列。如果您想要真正正确（或者如果上述方法不起作用），您可以提前创建
DataTable并设置该列的DateTimeMode=DataSetDateTime.Utc在用行填充之前。然后您就可以放心发送 UTC 日期。If you're using SQL Server 2008, then the "proper" solution is to use the SQL
datetimeoffsetdata type instead of SQL's datetime.datetimeoffsetis a timezone-aware date/time type new to SQL 2008, and will be translated in ADO.NET into the CLRSystem.DateTimeOffsettype which is always relative to UTC. Here's a blog post going into more details about this. The first few search results on MSDN forDateTimeOffsetprovide additional background info.If you can't change your SQL schema, ADO.NET has a property custom-made for this situation:
DataColumn.DateTimeMode, which controls whether local timezone is added when the dataset is serialized (DateTimeMode=DataSetDateTime.UnspecifiedLocal, which is the default) or whether no timezone info is added on serialization (DateTimeMode=DataSetDateTime.Unspecified). You want the latter.If my reading of the MSDN docs is correct, you should be able to set
DateTimeMode=DataSetDateTime.Unspecifiedfor theDataColumnin question after the DataSet is filled. This should serialize the column without a timezone.If you want to be really proper (or in case the approach above doesn't work), you can create the
DataTableahead of time and set that column'sDateTimeMode=DataSetDateTime.UtcbeforeFill-ing it with rows. Then you're assured of sending the date as UTC.就我而言，将 XSD 架构中相关列的 DateTimeMode 属性值更改为“Unspecialed”，而不是默认值“UnspecialedLocal”。
In my case, change related columns' DateTimeMode property value to "Unspecialed" rather than default value "UnspecialedLocal" in XSD schema.