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.

@Daniel I'm a new employee, I don't have the ability to install/reference new modules yet so I'm hoping to do this with what I have available. — Dave

Anubrij Chandra Anubrij Chandra · Accepted Answer · 2014-05-07T18:57:35

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;

VB.Net SQL results to JSON for Ajax

3 Answers