1

Closed

Null values should be represented as System.DBNull when deserializing a DataTable

description

Originally posted here: http://stackoverflow.com/questions/5902350/how-can-i-deserialize-an-ado-net-datatable-that-contains-null-values-using-json-n
 
I am attempting to use Newtonsoft.Json.Net35 Version 4.0.2.0 to deserialize an ADO.NET DataTable that contains null values. Serialization works fine:
 
[Test]
public void SerializeDataTableWithNull()
{
    var table = new DataTable();
    table.Columns.Add("item");
    table.Columns.Add("price", typeof(double));
    table.Rows.Add("shirt", 49.99);
    table.Rows.Add("pants", 54.99);
    table.Rows.Add("shoes"); // no price
 
    var json = JsonConvert.SerializeObject(table);
    Assert.AreEqual(@"["
        + @"{""item"":""shirt"",""price"":49.99},"
        + @"{""item"":""pants"",""price"":54.99},"
        + @"{""item"":""shoes"",""price"":null}]", json);
}
Deserialization works fine if values are missing:
 
[Test]
public void DerializeDataTableWithImplicitNull()
{
    const string json = @"["
        + @"{""item"":""shirt"",""price"":49.99},"
        + @"{""item"":""pants"",""price"":54.99},"
        + @"{""item"":""shoes""}]";
    var table = JsonConvert.DeserializeObject<DataTable>(json);
    Assert.AreEqual("shirt", table.Rows[0]["item"]);
    Assert.AreEqual("pants", table.Rows[1]["item"]);
    Assert.AreEqual("shoes", table.Rows[2]["item"]);
    Assert.AreEqual(49.99, (double)table.Rows[0]["price"], 0.01);
    Assert.AreEqual(54.99, (double)table.Rows[1]["price"], 0.01);
    Assert.IsInstanceOf(typeof(System.DBNull), table.Rows[2]["price"]);
}
If, however, values are explicitly null:
 
[Test]
public void DerializeDataTableWithExplicitNull()
{
    const string json = @"["
        + @"{""item"":""shirt"",""price"":49.99},"
        + @"{""item"":""pants"",""price"":54.99},"
        + @"{""item"":""shoes"",""price"":null}]";
    var table = JsonConvert.DeserializeObject<DataTable>(json);
    Assert.AreEqual("shirt", table.Rows[0]["item"]);
    Assert.AreEqual("pants", table.Rows[1]["item"]);
    Assert.AreEqual("shoes", table.Rows[2]["item"]);
    Assert.AreEqual(49.99, (double)table.Rows[0]["price"], 0.01);
    Assert.AreEqual(54.99, (double)table.Rows[1]["price"], 0.01);
    Assert.IsInstanceOf(typeof(System.DBNull), table.Rows[2]["price"]);
}
DeserializeObject throws "System.ArgumentException : Cannot set Column 'price' to be null. Please use DBNull instead." It looks like this is a bug which is easily fixed by replacing
 
dr[columnName] = reader.Value;
 
with
 
if (reader.Value == null)
    dr[columnName] = System.DBNull.Value;
else
    dr[columnName] = reader.Value;
 
in Newtonsoft.Json.Converters.DataTableConverter.
Closed May 7, 2011 at 2:05 AM by JamesNK
Fixed

comments

Frank235711 wrote May 5, 2011 at 8:41 PM

Further bikeshedding:
replace
dr[columnName] = reader.Value;
with
dr[columnName] = reader.Value ?? System.DBNull.Value;

Frank235711 wrote May 7, 2011 at 12:12 PM

Thanks James. I now see the I can upload patches (still getting used to CodePlex) and will do that instead in the future.