VB.Net SQL results to JSON for Ajax

Question

New programmer, I'm building an aspx page with VB.Net code and I'm trying to create a D3 chart with data from our database, but I can't get the return value into the correct format.

In my VB code I can get a string into the exact format I want:

[{ name: "PROAIR", value: 7}],{ name: "NASONEX", value: 4}] 

but when I pass it back it ends up with:

{"d":"[{name: \"PROAIR\", value: 7},{name: \"NASONEX\", value: 4}]"}

I know (now) that it's attempting to serialize the return value so I have tried using a datatable and serializing that, and then a List(Of Object) and serializing that, but every time I get different results with more quotes and escape backspaces in the return value. I've looked at the Microsoft Person example and some of the Stack Contacts examples, but is it really necessary for me to build a whole new class and add new items to it? My string builder code below:

Dim sData As String
Dim sqlCmd As New SqlCommand(strSql, cn)
Using sdr As SqlDataReader = sqlCmd.ExecuteReader()
  Dim sb As New StringBuilder()
  sb.Append("[")
  While sdr.Read()
    sb.Append("{")
    System.Threading.Thread.Sleep(50)
    sb.Append(String.Format("name: '{0}', value: {1}", sdr(0), sdr(1)))
    sb.Append("},")
  End While
  sb = sb.Remove(sb.Length - 1, 1)
  sb.Append("]")
  sData = sb.ToString
  sData = sData.Replace("'", ControlChars.Quote)
  cn.Close()

  Return sData
End Using

If I try and serialize that string:

Dim serializer As New JavaScriptSerializer()
Dim SerializedResult = serializer.Serialize(sData)
Return SerializedResult 

I end up with : {"d":"\"[{name: \\\"PROAIR\\\", value: 7},{name: \\\"NASONEX\\\", value: 4}]\""}

I tried to deserialize it with Dim deserializedResult = serializer.Deserialize(Of String)(sData) but it errors out with an array type failure so apparently you can't do that :)

In the aspx page I have hard coded:

success: function (r) {
var data = [{ name: "PROAIR", value: 7}],{ name: "NASONEX", value: 4}]

and my chart builds perfectly, so it's just a matter of returning the sql data in the correct format and changing the script code to var data = r;

EDIT Working code below:

aspx page:

    $(function () {
        GetData();
    });

    function GetData() {
        $.ajax({
            type: "post",
            url: "D3PieChart.aspx/GetData",
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (r) {

                data = r.d;
                data = jQuery.parseJSON(data)

-- Build D3 chart commands in this section --

            },
            error: function (xhr, status, error) {
                OnFailure(error);
                alert('Error');
            }
        });
   }

</script>

VB page:

<WebMethod()> _
Public Shared Function GetData() As String     

    Dim sConnect As String = ConfigurationManager.ConnectionStrings("SQLConnectionString").ConnectionString
    Dim cn As New SqlConnection(sConnect)
Dim strSql As String = "SELECT name and a count of stuff "
strSql += "FROM a bunch of tables WHERE stuff = stuff "

    Dim dtb As New DataTable
    cn.Open()
    If (cn.State And ConnectionState.Open) <> 0 Then
        Dim sqlCmd As New SqlCommand(strSql, cn)
        Dim sqlDad As New SqlDataAdapter(sqlCmd)
        sqlDad.Fill(dtb)
        dtb.Columns(0).ColumnName = "Name"
        dtb.Columns(1).ColumnName = "value"
        Dim sjData As String = GetJson(dtb)
        Return sjData
    End If
    Return -1
End Function



Public Shared Function GetJson(ByVal dt As DataTable) As String
    Dim serializer As New System.Web.Script.Serialization.JavaScriptSerializer()
    serializer.MaxJsonLength = Integer.MaxValue

    Dim rows As New List(Of Dictionary(Of String, Object))()
    Dim row As Dictionary(Of String, Object) = Nothing
    Dim row2 As Dictionary(Of Integer, Object) = Nothing
    For Each dr As DataRow In dt.Rows
        row = New Dictionary(Of String, Object)()
        row2 = New Dictionary(Of Integer, Object)()
        For Each dc As DataColumn In dt.Columns
            row.Add(dc.ColumnName.Trim(), dr(dc))
        Next
        rows.Add(row)
    Next
    Return serializer.Serialize(rows)
End Function
End Class

This is a simple chart that doesn't take any parameters it's all hard coded in, but it's pulling the chart data from a SQL Server database.

Solution

Get your Sql result in a datatable and the simple pass it to the GetJson method to get the json string.

Dim da As New OleDb.OleDbDataAdapter(strSql, cn)
Dim dt As DataTable = New DataTable
da.Fill(dt)

Dim sData As string = GetJson(dt)



 Public Shared Function GetJson(ByVal dt As DataTable) As String
        Dim serializer As New System.Web.Script.Serialization.JavaScriptSerializer()
        serializer.MaxJsonLength = Integer.MaxValue

        Dim rows As New List(Of Dictionary(Of String, Object))()
        Dim row As Dictionary(Of String, Object) = Nothing
        For Each dr As DataRow In dt.Rows
            row = New Dictionary(Of String, Object)()
            For Each dc As DataColumn In dt.Columns

                row.Add(dc.ColumnName.Trim(), dr(dc))

            Next
            rows.Add(row)
        Next
        Return serializer.Serialize(rows)
    End Function

Add reference of System.Collections.Generic

On aspx page

success: function (r) {
var data = r.d;

Comments

[Dave]

I populated a datatable, added columnnames and called your GetJson function Dim sData As String = GetJson(dt) Return sData

[Dave]

ran out of time on commentI populated a datatable, added columnnames and called your GetJson function Dim sData As String = GetJson(dt) Return sData But if I add an alert(data) to the aspx page, I get [{"Name":"PROAIR","Value":7},{"Name":"NASONEX","Value":4}] with quotes around the columns and I don't get any chart. I need to drop the " off the columnnames.

[Anubrij Chandra]

use jQuery.parseJSON(r.d)

[Dave]

in the aspx page I have var data = r.d; var d2 = jQuery.parseJSON(data) alert(JSON.stringify(d2)); alert(d2); The stringify shows the names of the pairs in quotes, the alert just shows [object][object]. In order for D3 to ready the dataset, I need to just remove the quotes around the word "Value" in the returned set and it "should" work (I think).

[Dave]

So actually, I could have either removed the quotes from "value", or added quotes to the integer. I changed my SQL query to CONVERT the count to a varchar and the datatable was serialized in a format that should be readable to D3. (It's not, but that's a different question). When I can upvote or mark as answer I will. Thanks for the help.

[Anubrij Chandra]

if your problem is only quotes then you can replace it r.d.replace(/\"/g, "") , this will replace all the quotes from the string.

[Dave]

Once I got the int in the SQL converted to a varchar, I went back and added the jQuery.parseJSON(r.d) and it's working. Will be update question with updated code that works. Thanks again for all your help.