How do I get Newtonsoft to deserialize a date as it is and call it utc?

Question

I have a datetime in a sql database: 2020-07-21 13:55:22.990 This datetime is in UTC although(correct me if I'm wrong), there would be no way to know that within the confines of the database. For reference, I am in CST(UTC-6) and this question was written during daylight savings time (so I'm -5 from UTC at the moment).

I'm sending a query to that database and getting back json using the following method (I don't believe there are any issues with it, but I've included it just in case):

private static string GetJSONFromSQLQuery(string query, string connectionString)
{
    var dataTable = new DataTable();

    using (var sqlConnection = new SqlConnection(connectionString))
    using (var sqlCommand = new SqlCommand(query, sqlConnection))
    using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand))
    {
        sqlConnection.Open();
        sqlDataAdapter.Fill(dataTable);
    }

    var rowsAsDictionaries = new List<Dictionary<string, object>>();
    foreach (DataRow row in dataTable.Rows)
    {
        var rowAsDictionary = new Dictionary<string, object>();

        // ReSharper disable once LoopCanBeConvertedToQuery
        foreach (DataColumn column in dataTable.Columns)
            rowAsDictionary.Add(column.ColumnName, row[column]);

        rowsAsDictionaries.Add(rowAsDictionary);
    }

    return new JavaScriptSerializer().Serialize(rowsAsDictionaries);
}

This is a snippet of the json I get back (in a real call, there would be more properties in the object and generally more objects):

[
    {
        "Timestamp":"\\/Date(1595357722990)\\/",
    },
]

https://www.freeformatter.com/epoch-timestamp-to-date-converter.html says this is 7/21/2020, 1:55:22 PM under 'Convert epoch timestamp to date'

I then deserialize the json with newtonsoft:

var deserializedJson = JsonConvert.DeserializeObject<List<MyObjectType>>(json)

MyObjectType has a bunch of { get; set; } properties and one of them is:

public DateTime Timestamp { get; set; }

From the above json snippet and newtonsoft call, Timestamp would be set to 7/21, 6:55:22 and the Kind property would be set to Utc. This is incorrect not what I was expecting.

After some searching I learned about the DateTimeZoneHandling property in JsonSerializerSettings and made the following calls with each of the 4 enum options:

var local = JsonConvert.DeserializeObject<List<MyObjectType>>(json, new JsonSerializerSettings
{
    DateTimeZoneHandling = DateTimeZoneHandling.Local,

});

var roundtrip = JsonConvert.DeserializeObject<List<MyObjectType>>(json, new JsonSerializerSettings
{
    DateTimeZoneHandling = DateTimeZoneHandling.RoundtripKind,

});

var unspecified = JsonConvert.DeserializeObject<List<MyObjectType>>(json, new JsonSerializerSettings
{
    DateTimeZoneHandling = DateTimeZoneHandling.Unspecified,

});

var utc = JsonConvert.DeserializeObject<List<MyObjectType>>(json, new JsonSerializerSettings
{
    DateTimeZoneHandling = DateTimeZoneHandling.Utc,

});

Which gives me the following DateTime objects in MyObjectType (note these are snippets from a DateTime object):

deserializedJson:
    Time: 7/21 6:55:22,
    Kind: Utc

local:
    Time: 7/21 1:55:22,
    Kind: Local
    
roundtripKind:
    Time: 7/21 6:55:22,
    Kind: Utc
    
unspecified:
    Time: 7/21 6:55:22,
    Kind: Unspecified

utc:
    Time: 7/21 6:55:22,
    Kind: Utc

The Kind property is particularly important because I will be calling

TimeZoneInfo.ConvertTimeFromUtc(theDeserializedTimeStamp, TimeZoneInfo.Local);

My desired output is:

Time: 7/21 1:55:22,
Kind: Utc

Since that would be reflective of what is in the database and I would be able to call TimeZoneInfo.ConvertTimeFromUtc to convert the UTC time to whatever the local timezone is.

How do I get that output?

EDIT:

I found a workaround solution. I changed the return statement in GetJSONFromSQLQuery to:

return JsonConvert.SerializeObject(rowsAsDictionaries);

and all the DateTimeZoneHandling options left the time as 1:55 PM and had varying (but expected) results for the Kind property. Not sure why json from JavaScriptSerializer wouldn't play nicely, but I'm unstuck now at least.

The json that JsonConvert.SerializeObject looks like this:

[
   {
      "Timestamp":"2020-07-21T13:55:22.99"
   }
]

Answer 1

It's been a while. Nobody else has given an answer and I've been using the solution I posted in the Edit for quite some time. So, I'm going to mark it as the answer.

Ultimately, the serializer I was using was old and not good. I thought I was using Newtonsoft, but I was wrong.

The method needs to be changed to this:

private static string GetJSONFromSQLQuery(string query, string connectionString)
{
    var dataTable = new DataTable();

    using (var sqlConnection = new SqlConnection(connectionString))
    using (var sqlCommand = new SqlCommand(query, sqlConnection))
    using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand))
    {
        sqlConnection.Open();
        sqlDataAdapter.Fill(dataTable);
    }

    var rowsAsDictionaries = new List<Dictionary<string, object>>();
    foreach (DataRow row in dataTable.Rows)
    {
        var rowAsDictionary = new Dictionary<string, object>();

        // ReSharper disable once LoopCanBeConvertedToQuery
        foreach (DataColumn column in dataTable.Columns)
            rowAsDictionary.Add(column.ColumnName, row[column]);

        rowsAsDictionaries.Add(rowAsDictionary);
    }

    // The original return statement:
    //return new JavaScriptSerializer().Serialize(rowsAsDictionaries);

    // The correct return statement:
    return JsonConvert.SerializeObject(rowsAsDictionaries);
}

As PanagiotisKanavos said:

new JavaScriptSerializer() is not JSON.NET. It's an obsolete .NET serialised used as a stop-gap. It had a lot of quirks so people avoided it when possible. ASP.NET WebAPI and .NET Core up to 2.2 uses JSON.NET. .NET Core 3.1 uses a new JSON serialised that's even stricter

Also, a little out of scope, but if anyone wants an async method, this works:

private static async Task<string> GetJSONFromSQLQueryAsync(string query, string connectionString)
{
    var dataTable = new DataTable();

    using (var sqlConnection = new SqlConnection(connectionString))
    {
        await sqlConnection.OpenAsync();
        using var sqlCommand = new SqlCommand(query, sqlConnection);
        using var reader = await sqlCommand.ExecuteReaderAsync();

        dataTable.Load(reader);
    }

    var rowsAsDictionaries = new List<Dictionary<string, object>>();
    foreach (DataRow row in dataTable.Rows)
    {
        var rowAsDictionary = new Dictionary<string, object>();

        // ReSharper disable once LoopCanBeConvertedToQuery
        foreach (DataColumn column in dataTable.Columns)
            rowAsDictionary.Add(column.ColumnName, row[column]);

        rowsAsDictionaries.Add(rowAsDictionary);
    }

    return JsonConvert.SerializeObject(rowsAsDictionaries);
}