Newtonsoft.Json.JsonConvert.DeserializeObject issue with null values

Question

I am using a SqlBulkCopy from json data using C# and SQL Server 2016.

The original string is: "1979-09-30T23:00:00.000+0000"

But when copied into the SQL Server table using SqlBulkCopy, the string becomes "01/10/1979 00:00:00"

I am using varchar(100) as data type for this column.

I tried with date, datetime and datetimeoffset data types, but I was getting conversion errors each time.

create table Employees(
matricule  nvarchar(20),
pname  nvarchar(100),
birthdate  varchar(100),
hiredate varchar(100))

-- I tried with date, datetime and datetimeoffset data types, but I was getting conversion errors each time. )

How to do in order to preserve the original data?

After analysis I found the issue root the Json has null values in date field the date is json is like this

"field_date_name":null

the json serializer is getting error with this field

public static void BulkCopy(DataTable myDataTable)
{           
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["2"].ConnectionString))
    {
        connection.Open();

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
        {
            foreach (DataColumn c in myDataTable.Columns)
                bulkCopy.ColumnMappings.Add(c.ColumnName, c.ColumnName);

            bulkCopy.DestinationTableName = myDataTable.TableName;

            try
            {
                bulkCopy.WriteToServer(myDataTable);
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);                        
            }
        }
    }
}

Here is the method calling it

public static void Employee()
        {
            try
            {                
                DataTable MyTable = Clapi.GetApiData(ConfigurationManager.AppSettings.Get("api_employe"), "imp_Employees").GetAwaiter().GetResult();
                log.Info(String.Format("Table: {0} Count {1}", MyTable.TableName, MyTable.Rows.Count));
                Cldb.BulkCopy(MyTable);
            }
            catch(Exception ex)
            {
                log.Error(ex.Message);
            }
        }

// and here GetApiData

public static async Task<DataTable> GetApiData(string Url ,string TableName)
        {
            try
            {
                var client = new HttpClient();
                var request = new HttpRequestMessage(HttpMethod.Get, Url);
                var response = await client.SendAsync(request);
                var contents = response.Content.ReadAsStringAsync().Result;
                var Mytable = JsonConvert.DeserializeObject<DataTable>(contents);
                Mytable.TableName = TableName;
                return Mytable;
            }
            catch(Exception ex)
            {
                log.Error("GetApiData; url="+Url+";TableName:"+TableName+ex.Message);
                return null;
            }
        }

Json data

[{ "matricule": "0009", "pname": "xxxx",
"birthdate": "1961-02-25T23:00:00.000+0000", "hiredate": "1976-02-14T23:00:00.000+0000"
}]

After bulkcopy to sql server these 2 dates become

"1961-02-25T23:00:00.000+0000" becomes 02/26/1961 00:00:00
"1976-02-14T23:00:00.000+0000" becomes 15/02/1976 00:00:00

I also tried with setting , but still getting error

NullValueHandling = NullValueHandling.Ignore

Answer 1

I had a similar issue. DB had null values and the DeserializeObject was throwing error not being able to convert null value to datetime, even though my model was setup as nullable DateTime.

This fixed the issue for me:

var jsonSettings = new JsonSerializerSettings
{
    NullValueHandling = NullValueHandling.Ignore
};
return JsonConvert.DeserializeObject<MyModel>(return_data, jsonSettings);

I actually found the solution in the question asked here.

How to handle null/empty values in JsonConvert.DeserializeObject

It did not work for the person posting the question, but worked in my situation.

Answer 2

Use JsonSerializationSettings with DateTimeZoneHandling = DateTimeZoneHandling.Utc then declare table date columns as datetime data type.

public static async Task<DataTable> GetApiData(string Url, string TableName)
{
    try
    {
        var client = new HttpClient();
        var request = new HttpRequestMessage(HttpMethod.Get, Url);
        var response = await client.SendAsync(request);
        var contents = response.Content.ReadAsStringAsync().Result;
        var settings = new JsonSerializerSettings
        {
            DateTimeZoneHandling = DateTimeZoneHandling.Utc
        };
        var Mytable = JsonConvert.DeserializeObject<DataTable>(contents, settings);
        Mytable.TableName = TableName;
        return Mytable;
    }
    catch (Exception ex)
    {
        log.Error("GetApiData; url=" + Url + ";TableName:" + TableName + ex.Message);
        return null;
    }
}

Answer 3

you're probably using DateTimeOffset in your code.

DateTimeOffset = DateTime+Offset(from UTC)

the difference probably is a cause of the offset between the client and server (two different zones)

if you are using DateTime you won't have this issue.

1) first try to debug your code to see that if the value that is going to be saved in the database is the same as the value saved.

2) So your data is already representing Client's Local date and time. Just cast it to DateTime and you will get the client's local Date and time.

more link