I tried to serialize a DataTable
object to Json using Newtonsoft.Json version 'Newtonsoft.Json.10.0.3' in a database SQL server 2012.
The table has a column with type 'geography', which contains instances of type SqlGeography
.
The code used to generate json:
public string SerializeToJson()
{
var connstring1 ="Data Source=server1;Initial Catalog=database1;user=xxx;password=yyy";
var sql = "SELECT * FROM table_1 "; //table_1 has a column of type geography
using (var c1 = new SqlConnection(connstring1))
{
c1.Open();
var da = new SqlDataAdapter()
{
SelectCommand = new SqlCommand(sql, c1)
};
DataSet ds1 = new DataSet("table");
da.Fill(ds1, "table");
var dt = ds1.Tables[0];
//serialize to Json
try
{
var options = new JsonSerializerSettings
{
Formatting = Formatting.None
};
//this line fire exception for geography type
var json = JsonConvert.SerializeObject(dt, options);
return json;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
}
I already installed the assembly 'Microsoft.SqlServer.Types' from feature Pack of sql 2012
I have created a complete C# program (independent of sql server installation) using a datatable with SqlGeography column to show the problem Try it
I get the error:
Newtonsoft.Json.JsonSerializationException: Error getting value from 'Value' on 'System.Data.SqlTypes.SqlDouble'. --->
System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values. at System.Data.SqlTypes.SqlDouble.get_Value() at GetValue(Object ) at Newtonsoft.Json.Serialization.DynamicValueProvider.GetValue(Object target)
I reached https://github.com/JamesNK/Newtonsoft.Json/issues/993, but it can't help.
Any help to resolve the problem.
Edit:
Based on @dbc comments, I provided the complete source code used to generate json.
The complete error message is:
Newtonsoft.Json.JsonSerializationException: Error getting value from 'Value' on 'System.Data.SqlTypes.SqlDouble'. ---> >System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values. at System.Data.SqlTypes.SqlDouble.get_Value() at GetValue(Object ) at Newtonsoft.Json.Serialization.DynamicValueProvider.GetValue(Object target)
--- End of inner exception stack trace --- at Newtonsoft.Json.Serialization.DynamicValueProvider.GetValue(Object target)
at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.CalculatePropertyValues(JsonWriter writer, Object value, JsonContainerContract contract, JsonProperty member, JsonProperty property, JsonContract& memberContract, Object& memberValue) at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeObject (JsonWriter writer, Object value, JsonObjectContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty) at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue( JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member , JsonContainerContract containerContract, JsonProperty containerProperty) at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeObject (JsonWriter writer, Object value, JsonObjectContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty) at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue( JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member , JsonContainerContract containerContract, JsonProperty containerProperty) at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter, Object value, Type objectType) at Newtonsoft.Json.Serialization.JsonSerializerProxy.SerializeInternal(JsonWriter jsonWriter, Object value, Type rootType) at Newtonsoft.Json.Converters.DataTableConverter.WriteJson(JsonWriter writer, Object value, JsonSerializer serializer) at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeConver table(JsonWriter writer, JsonConverter converter, Object value, JsonContract contract, JsonContainerContract collectionContract, JsonProperty containerProperty)
at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue( JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member , JsonContainerContract containerContract, JsonProperty containerProperty) at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter, Object value, Type objectType) at Newtonsoft.Json.JsonSerializer.SerializeInternal(JsonWriter jsonWriter, Object value, Type objectType) at Newtonsoft.Json.JsonConvert.SerializeObjectInternal(Object value, Type type, JsonSerializer jsonSerializer) at Newtonsoft.Json.JsonConvert.SerializeObject(Object value, JsonSerializerSettings settings) at JsonTester.SerializeToJson() in F:\JsonTester.cs:line 104
Edit2:
I enable tracing as described by @dbc, and get the following log:
2017-08-24T19:45:31.941 Info Started serializing System.Data.DataTable with converter Newtonsoft.Json.Converters.DataTableConverter. Path ''.
2017-08-24T19:45:31.972 Info Started serializing Microsoft.SqlServer.Types.SqlGeography. Path '[0].f1'.
2017-08-24T19:45:32.003 Info Started serializing System.Data.SqlTypes.SqlInt32.Path '[0].f1.STSrid'.
2017-08-24T19:45:32.003 Info Finished serializing System.Data.SqlTypes.SqlInt32. Path '[0].f1.STSrid'.
2017-08-24T19:45:32.003 Info Started serializing System.Data.SqlTypes.SqlDouble. Path '[0].f1.Lat'.
2017-08-24T19:45:32.003 Info Finished serializing System.Data.SqlTypes.SqlDouble. Path '[0].f1.Lat'.
2017-08-24T19:45:32.003 Info Started serializing System.Data.SqlTypes.SqlDouble. Path '[0].f1.Long'.
2017-08-24T19:45:32.003 Info Finished serializing System.Data.SqlTypes.SqlDouble. Path '[0].f1.Long'.
2017-08-24T19:45:32.003 Info Started serializing System.Data.SqlTypes.SqlDouble. Path '[0].f1.Z'.
2017-08-24T19:45:32.003 Error Error serializing System.Data.SqlTypes.SqlDouble.Error getting value from 'Value' on 'System.Data.SqlTypes.SqlDouble'.
2017-08-24T19:45:32.003 Error Error serializing System.Data.DataTable. Error getting value from 'Value' on 'System.Data.SqlTypes.SqlDouble'.
IDataReader
returned fromcommand.ExecuteReader()
usingDataReaderConverter
from JSON.net serialize directly from oledbconnection? – dbcToString()
output of the exception including the exception type, message, traceback, and inner exception? – dbcSqlGeometry
but I'd like to confirm. 2) To get a data reader from aSqlCommand
see Retrieving Data Using a DataReader. – dbc