DateTime type and Time Zones
Our WinForms app needs to make adjustments for time zones and dates as the data is accessible via .NET Remoting. This means a user can be in Texas and the data store could be in Virginia.
The solution? Convert on the server, convert on the client, 4 times.
ToLocalTime and ToUniversalTime will be your best friends here.
The trick is how to handle truly modified DataRows. The above code handles this.
Suppose the user updates a field X in DataRow A.
Suppose also that DataRow A has 5 datetime types.
You do not want to convert these types if they were not modified by the client. Now, on an Added DataRow you do want to convert all of the datetime types.
Client code to pull and push a DataSet:
Private Enum DateTimeConversion
ToLocalTime
ToUniversalTime
End Enum
PrivateShared Sub ConvertDateTimes(ByVal ds As DataSet, ByVal conv As DateTimeConversion)
If ds Is Nothing Then Return
Dim dateTimeType As System.Type = GetType(DateTime)
Dim indices() As Integer
Select Case conv
Case DateTimeConversion.ToLocalTime
If Not ds.HasChanges() Then Return
For Each dt As DataTable In ds.Tables
ReDim indices(-1)
For i As Integer = 0 To dt.Columns.Count - 1
If dt.Columns(i).DataType Is dateTimeType Then
ReDim Preserve indices(indices.Length)
indices(indices.Length - 1) = i
End If
Next
If indices.Length > 0 Then
For Each dr As DataRow In dt.Rows
Select Case dr.RowState
Case DataRowState.Added
For Each i As Integer In indices
dr(i) = DirectCast(dr(i), DateTime).ToLocalTime()
Next
Case DataRowState.Modified
For Each i As Integer In indices
If Not dr(i, DataRowVersion.Original) = dr(i, DataRowVersion.Current) Then
dr(i) = DirectCast(dr(i), DateTime).ToLocalTime()
End If
Next
End Select
Next
End If
Next
Case DateTimeConversion.ToUniversalTime
For Each dt As DataTable In ds.Tables
ReDim indices(-1)
For i As Integer = 0 To dt.Columns.Count - 1
If dt.Columns(i).DataType Is dateTimeType Then
ReDim Preserve indices(indices.Length)
indices(indices.Length - 1) = i
End If
Next
If indices.Length > 0 Then
For Each dr As DataRow In dt.Rows
For Each i As Integer In indices
If TypeOf dr(i) Is DateTime Then dr(i) = DirectCast(dr(i), DateTime).ToUniversalTime()
Next
Next
End If
Next
ds.AcceptChanges()
End Select
End Sub
Server code to push and pull a DataSet:
Private Function ConvertDateTimeToLocalTime(ByVal ds As DataSet)
Dim cols As New ArrayList
For Each tbl As DataTable In ds.Tables
For i As Integer = 0 To tbl.Columns.Count - 1
If tbl.Columns(i).DataType.FullName = "System.DateTime" Then cols.Add(i)
Next
If tbl.Rows.Count > 0 AndAlso cols.Count > 0 Then
For Each row As DataRow In tbl.Rows
For Each i As Integer In cols
If TypeOf row(i) Is DateTime Then row(i) = DirectCast(row(i), DateTime).ToLocalTime
Next
Next
End If
Next
If ds.HasChanges Then ds.AcceptChanges()
End Function ' ConvertDateTimeToLocalTime
Private Function ConvertDateTimeToUniversalTime(ByVal ds As DataSet)
Dim cols As New ArrayList
For Each tbl As DataTable In ds.Tables
For i As Integer = 0 To tbl.Columns.Count - 1
If tbl.Columns(i).DataType.FullName = "System.DateTime" Then cols.Add(i)
Next
If tbl.Rows.Count > 0 AndAlso cols.Count > 0 Then
For Each row As DataRow In tbl.Rows
If row.RowState = DataRowState.Added Then
For Each i As Integer In cols
row(i) = DirectCast(row(i), DateTime).ToUniversalTime
Next
ElseIf row.RowState = DataRowState.Modified Then
Dim curr, orig As String
For Each i As Integer In cols
curr = row(i, DataRowVersion.Current).ToString().Trim()
orig = row(i, DataRowVersion.Original).ToString().Trim()
If (Not curr.Equals(orig) OrElse _
curr <> orig OrElse _
String.CompareOrdinal(curr, orig) <> 0) AndAlso _
Not (curr = "-" AndAlso orig = String.Empty) Then
If TypeOf row(i) Is DateTime Then row(i) = DirectCast(row(i), DateTime).ToUniversalTime
End If
Next
End If
Next
End If
Next
End Function ' ConvertDateTimeToUniversalTime