Search code examples
asp.netjsonweb-servicesgoogle-visualizationasmx

How to Format JSON output from web service asmx to use with Google visualization

I have a .asmx webservice that outputs a JSON string like this:

"d":"{\"cols\": [{\"type\": \"datetime\" ,\"id\": \"EvenDate\" ,\"label\": \"EvenDate\" }, {\"type\": \"number\" ,\"id\": \"Entries\" ,\"label\": \"Entries\" }], \"rows\" : [{\"c\" : [{\"v\": \"Date(2014, 5, 16, 0, 0, 0)\"}, {\"v\": 1}]}, {\"c\" : [{\"v\": \"Date(2014, 5, 22, 0, 0, 0)\"}, {\"v\": 1}]}, {\"c\" : [{\"v\": \"Date(2014, 5, 23, 0, 0, 0)\"}, {\"v\": 5}]}]}"}

Google Visualization requires the data to be like this. 

{
  "cols": [
        {"id":"","label":"Topping","pattern":"","type":"string"},
        {"id":"","label":"Slices","pattern":"","type":"number"}
      ],
  "rows": [
        {"c":[{"v":"Mushrooms","f":null},{"v":3,"f":null}]},
        {"c":[{"v":"Onions","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Olives","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Zucchini","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Pepperoni","f":null},{"v":2,"f":null}]}
      ]
}

My JSON output is good except that the webservice is adding the d: and also the \ but I think the \ are just escape characters. Is there a way to remove the d: from the JSON response?

This is my webservice:

' google datatable json wrapper
Imports Google.DataTable.Net.Wrapper
...
Dim dt As System.Data.DataTable
dt = tlsession.FillSessionTable(sqlstr)
Dim dataTable = SystemDataTableConverter.Convert(dt)
Dim jsonString As String = dataTable.GetJson()
Return jsonString

Can you please point me to the right direction on how I could fix the JSON ouput? Thanks!

Solution

  • The easiest way I found to get a Google DataTable directly from a .net web services is with the Google DataTable .Net Wrapper. Their documentation is clearly written so I won't get into too much detail with examples here. I will say that you can use the library to convert a System.Data.DataTable into an excellent JSON representation of a Google DataTable, and it even properly serializes DateTime objects into the more unusual format used by the Google Visualization API.

    Edit: Also, they have a NuGet package here.