CODEWITHSUNDEEP
mysqljson.net
ghiscoding
ghiscoding

Reputation: 13214

Trying to use JSon.net newton with MySQL date but get NullReferenceException

I have an ASP MVC project which I connect to 2 different types of database, 1 is SQL Server which is running fine the JSon.net Newton and then I manage to write a proper function to read as well data from a second DB type of MySQL. It's running fine until I reach the JSON serialize with JSon.net and it took me a while to find out that it just doesn't like the MySQL date type (datetime or timestamp). Now I understand that .net date type is completely different from MySQL date time, but is there a way to bypass this problem? And actually my dates are all in ISO format, so in theory I don't even want to convert them or anything, I want to leave them untouched (yyyy-mm-dd) and display them as is, but the json.net seems to be trying to convert them anyway :(

I am getting this exception: NullReferenceException and my code for MySQL is the following:

MySqlDataReader rdr = cmd.ExecuteReader();
DataTable dt = new DataTable("test");

if (rdr.HasRows)
{
    dt.BeginLoadData();
    dt.Load(rdr);
    dt.EndLoadData();
}

output = JsonConvert.SerializeObject(
    dt,
    new JsonSerializerSettings
    {
        ReferenceLoopHandling = ReferenceLoopHandling.Ignore,
        Formatting = Formatting.None,
        DateFormatString = "yyyy-MM-dd"
    });

If I look at the datatable object with the Quick Watch, it looks like a normal working date and I also compared the result from both DB and I see no differences whatsoever on both DB datable object results but it shoots the exception right after. I am guessing that JSon.net Newton does not allow another date type, but is there a way to bypass that and let it serialize it as a regular string (untouched)?

If I exclude the date field from my query, then everything is fine, so I know it's just the date fields that are problematic. Any suggestions?


EDIT
The exception StackTrace returned is the following:

   at System.Object.GetType()
   at Newtonsoft.Json.JsonWriter.CreateUnsupportedTypeException(JsonWriter writer, Object value)
   at Newtonsoft.Json.JsonWriter.WriteValue(JsonWriter writer, PrimitiveTypeCode typeCode, Object value)
   at Newtonsoft.Json.JsonWriter.WriteValue(JsonWriter writer, PrimitiveTypeCode typeCode, Object value)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter, Object value, Type objectType)
   at Newtonsoft.Json.Converters.DataTableConverter.WriteJson(JsonWriter writer, Object value, JsonSerializer serializer)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeConvertable(JsonWriter writer, JsonConverter converter, Object value, JsonContract contract, JsonContainerContract collectionContract, 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.SerializeObject(Object value, Type type, Formatting formatting, JsonSerializerSettings settings)
   at IntranetApplication.Controllers.ReportsController.ListFilteredData(FormCollection collection) in c:\Users\sesa213338\Documents\Visual Studio 2012\Projects\Viconics\IntranetApplication\Controllers\ReportsController.cs:line 192
   at lambda_method(Closure , ControllerBase , Object[] )
   at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
   at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass37.<>c__DisplayClass39.<BeginInvokeActionMethodWithFilters>b__33()
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49()

Upvotes: 0

Views: 1265

Answers (1)

ghiscoding
ghiscoding

Reputation: 13214

After few hours spent on this issue, I found that my problem was related to the fact that some of my fields had the default zero dates into MySQL. The JSon.net of Newtown does not like them unless you convert them to a proper format. That process can easily be applied by one simple property Convert Zero Datetime=True which you need to add inside the MySQL connection string. So basically, the connection string would look something like this:

server=localhost;uid=yourusername;pwd=yourpassword;database=yourdbname;Convert Zero Datetime=True;

So now instead of being 0000-00-00 format inside MySQL, it actually shows them as 0001-01-01 inside the JSON output result. I can now use the exact same code of serializing into JSON for both database type.

Upvotes: 1

Related Questions