DataTable Datetime column deserialization from web API

Question

I have a Web API which returns a datatable

public async Task<IHttpActionResult> SelectExtension(DateTime start, DateTime end, string institution)
{
    var identity = (ClaimsIdentity)User.Identity;
    var facility= claimIdentity.GetIdentityValue("Institution", identity).First();
    DataTable table = new DataTable("Result");
    var cmd = new SqlCommand();
    using (SqlConnection con = connection.DB())
    {
        try
        {
            con.Open();
            var query = generalData.SelectExtension(start, end, facility, institution);
            cmd = command.CreateQuery(query.Query, query.ProcdureParameters, con);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
            sqlDataAdapter.Fill(table);

        }
        catch (Exception e)
        {
            var error = $"Code: {e.HResult}\n Message: {e.Message}\n InnerException: {e.InnerException}\n Stack: {e.StackTrace}";
            await stream.WriteAsync(error);
            return InternalServerError();
        }
    }
    return Ok(table);
}

which has a column with a datatype of DateTime

table.Rows[6]["ApprovedExtendedEndDate"]
{11/25/2021 12:00:00 AM}
    Date: {11/25/2021 12:00:00 AM}
    Day: 25
    DayOfWeek: Thursday
    DayOfYear: 329
    Hour: 0
    Kind: Unspecified
    Millisecond: 0
    Minute: 0
    Month: 11
    Second: 0
    Ticks: 637733952000000000
    TimeOfDay: {00:00:00}
    Year: 2021

When this is return to the client the datatype of the column is changed to string

Http Client Code

public async Task<DataTable> SelectExtension(DateTime start, DateTime end, string institution)
{
    try
    {
        var response = await ClientHttp.HttpClient.Client.GetAsync($"{controller}/SelectExtension/{start:yyyy-MM-dd}/{end:yyyy-MM-dd}/{institution}");
        if (response.IsSuccessStatusCode)
        {
            var result = await response.Content.ReadAsAsync<DataTable>();
            return result;
        }
        else if (response.StatusCode == HttpStatusCode.Unauthorized)
        {
            FormAction.Alert("You do not have permission to access this resource", FormAction.enmType.Error);
            return null;
        }
        return null;
    }

The value in the column now becomes a string

result.Rows[6]["ApprovedExtendedEndDate"]
"11/25/2021 00:00:00"

Can anyone please help me to identify why it is not able to convert it to a dateTime.
It is not the only datetime column in the datatable but this particular one is return a string.

DataTable is a poor choice for serialization. I wouldn't be surprised if it worked if you used a List or array. — Crowcoder, Nov 25 '21 at 15:49
How are you sending the `DataTable` over the wire? Is it with XML, or with JSON, or something else? If JSON be aware that JSON [does not have a primitive for dates and times](https://stackoverflow.com/q/10286204) and so `DateTime` will get serialized as a string in JSON. Thus on the client side, what happens depends on the serializer. Json.NET has some hueristics to "guess" whether a `DataTable` column is a `DateTime` based on the first cell encountered, but other serializers don't do this. If you are not using JSON, we need to know what you are using. — dbc, Nov 29 '21 at 04:33
You might consider replacing your `DataTable` with a typed data model. If you have a typed data model your properties will be serialized appropriately for the type. — dbc, Nov 29 '21 at 04:34

score 0 · Answer 1 · answered Aug 29 '22 at 15:54

I've seen this behavior whenever the first row contains a null value in the DateTime column, for some reason the serializer doesn't do anything and sets the default data type (string), and other row's values are set by calling ToString method which formats the date with the default Thread.CurrentCulture

DataTable Datetime column deserialization from web API

1 Answers1