Click here to Skip to main content
15,867,308 members
Articles / Programming Languages / XML
Tip/Trick

Convert DataSet or XML to JSON using VB.NET

Rate me:
Please Sign up or sign in to vote.
4.87/5 (11 votes)
18 Apr 2011CPOL1 min read 111.3K   26   10
Convert DataSet or XML to JSON using VB.NET
I searched high and wide for something simple to do this and every function I found basically reinvented the wheel from scratch. If you do any amount of searching, you can find the JavaScriptSerializer object which will convert objects to JSON. Sounds easy enough...WRONG! Even converting an empty DataSet will return a circular reference exception.

On a similar note, I found several articles claiming to convert XML to JSON using the same function. The "solution" was something like this:

VB
Dim jss As New JavaScriptSerializer
Dim jsonString As String = jss.Serialize(DataSet1.GetXML())


I tried this thinking it made sense. But instead of serializing the data in the XML, it simply dumps the entire XML string into a single element in the JSON string. I'm pretty sure that's not what anyone wants it to do.

So how do you do it? Well, there are a number of methods, but I found the quickest and easiest is to just convert your DataSet to a Dictionary object, then use the JavaScriptSerializer to make the conversion.

Here's the code:

VB
Function DataSetToJSON(ds As DataSet) As String
    Dim dict As New Dictionary(Of String, Object)

    For Each dt As DataTable In ds.Tables
        Dim arr(dt.Rows.Count) As Object

        For i As Integer = 0 To dt.Rows.Count - 1
            arr(i) = dt.Rows(i).ItemArray
        Next

        dict.Add(dt.TableName, arr)
    Next

    Dim json As New JavaScriptSerializer
    Return json.Serialize(dict)
End Function


If you're looking to convert XML to JSON and write the least amount of code, you can just write it to a DataSet on the fly like this:

DataSetToJSON(New DataSet().ReadXml(xmlString))


I know it sounds crazy, but both of these methods are really simple and execute really fast. And you don't need to know about XLT or anything else. Oh, and the only libraries you need are System.Data and System.Web.Script.Serialization.

When it comes around to referencing the JSON object from your browser, you can access the elements like so:

JavaScript
jsonString.TableName[rowIndex][columnIndex]


If anyone knows how to make the conversion, you can reference it by jsonString.TableName.Column[rowIndex], I would love to see it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Systems Engineer
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionEven simpler and cleaner solution than using those whacky dictionaries (VB.Net) Pin
Rick Levin14-Nov-14 11:30
Rick Levin14-Nov-14 11:30 
QuestionNice and simple Pin
karenpayne29-Jul-13 11:12
karenpayne29-Jul-13 11:12 
QuestionNice solution but what about column names? Pin
morganrg10-Jan-13 2:28
morganrg10-Jan-13 2:28 
AnswerRe: Nice solution but what about column names? Pin
digthewells23-Jan-13 5:47
digthewells23-Jan-13 5:47 
GeneralRe: Nice solution but what about column names? Pin
Rosstur16-Dec-13 10:40
Rosstur16-Dec-13 10:40 
QuestionNice article but . . . Pin
wrlucas11-Oct-12 12:50
wrlucas11-Oct-12 12:50 
Jesse said:

When it comes around to referencing the JSON object from your browser, you can access the elements like so:

jsonString.TableName[rowIndex][columnIndex]
______

How is the client side aware of something you created on the on server? How would you get at this object with Javascript or JQuery?
AnswerRe: Nice article but . . . Pin
Jesse Fatherree11-Oct-12 13:41
Jesse Fatherree11-Oct-12 13:41 
GeneralRe: Nice article but . . . Pin
wrlucas12-Oct-12 6:12
wrlucas12-Oct-12 6:12 
GeneralRe: Nice article but . . . Pin
Jesse Fatherree12-Oct-12 7:20
Jesse Fatherree12-Oct-12 7:20 
GeneralWhat if I want to convert Json to a dataset? Pin
mmakar5-Nov-11 17:32
mmakar5-Nov-11 17:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.