SQL Dataset to JSON

Question

Alright I have been trying to figure this out and I read the MSDN page on the JavaScriptSerializer. However, I still can't figure out how to get this to work on an asp.net page. I want to convert my dataset into a json string so I can graph it using FLOT(graphing tool)

---

THIS MADE IT WORK THANKS FOR YOUR HELP GUYS: this is in vb.net for future people

Imports System.Web.Script.Serialization

....

Dim myObject = dataset.GetXml()
Dim jsonString = (New JavaScriptSerializer()).Serialize(myObject)

Solution 1

Reference the assembly System.Web.Extensions.dll and then do this:

using System.Web.Script.Serialization;

....

var myObject = ... your stuff ...
var jsonString = new JavaScriptSerializer().Serialize(myObject);

Check out the MSDN page for more info.

Solution 2

I did the following when I was working on a Project using PlotKit. I create a webservice to return the data and set the response format to Jason...this was while ago...should also work in 3.5

Here is a sample

<WebMethod()> _
    <Script.Services.ScriptMethod(UseHttpGet:=True, ResponseFormat:=ResponseFormat.Json)> _
      Public Function GetSales(ByVal a As String) As Generic.List(Of Sale)
        Dim _conn As SqlConnection = New SqlConnection(connstr)
        Dim _dr As SqlDataReader
        Try
            Dim _cmd As SqlCommand = New SqlCommand("select * from sales", _conn)
            _conn.Open()
            _dr = _cmd.ExecuteReader(CommandBehavior.CloseConnection)

            If _dr.HasRows Then
                Dim s As Sale
                Dim c As New Generic.List(Of Sale)
                While _dr.Read
                    s = New Sale
                    With s
                        .Month = _dr("monthname")
                        .TheSale = _dr("sale")
                    End With
                    c.Add(s)
                End While

                Return c
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            _conn.Close()
        End Try

              End Function

End Class

Here is the object class...notice I am serializing the object.

<Serializable()> _
Public Class Sale
    Private _month As String
    Private _sale As String

    Public Property Month() As String
        Get
            Return _month
        End Get
        Set(ByVal value As String)
            _month = value
        End Set
    End Property

    Public Property TheSale() As String
        Get
            Return _sale
        End Get
        Set(ByVal value As String)
            _sale = value
        End Set
    End Property
End Class

Solution 3

public static string DStoJSON(DataSet ds)
{
   StringBuilder json = new StringBuilder();

   foreach (DataRow dr in ds.Tables[0].Rows)
   {
       json.Append("{");

       int i = 0;
       int colcount = dr.Table.Columns.Count;

       foreach (DataColumn dc in dr.Table.Columns)
       {
           json.Append("\"");
           json.Append(dc.ColumnName);
           json.Append("\":\"");
           json.Append(dr[dc]);
           json.Append("\"");

           i++;
           if (i < colcount) json.Append(",");

       }
       json.Append("\"}");
       json.Append(",");
   }
   return json.ToString();
}

Probably most useful to you is the dataset loop instead of the stringbuilder. You could loop these into an object, then use the javascript serializer library.

Or even better, if you are using asp.net mvc, you can just do this:

return Json(List<myobject>, JsonRequestBehavior.AllowGet);

but this way is quick & easy! -- I didn't quite test this! the appended comma might be wrong (or code can be improved) and the final row comma needs to be handled

I use the mvc way & never looked back :)

Solution 4

Check out the DataContractJsonSerializer, and this article on MSDN

View more solutions