NovoGeek's Blog (Archive)

Technical insights of a web geek

Converting ASP.NET DataTable to JSON using JayRock

Beginners using jQuery plugins and jQuery AJAX with ASP.NET get stuck with one common problem-converting datatable to JSON. This conversion is necessary since JSON can be easily parsed in JavaScript and also most plugins expect data in JSON format, though that can be altered.

On googling, I found that .Net 3.5 has inbuilt support for this, but learnt from my Tech Arch (Mr.Praveen Reddy) that for .NET 2.0, the simplest solution is to use JayRock. The concept is very simple. Just loop through all rows, all columns of your datatable and add them to JSON arrays, objects.

Download JayRock and add Json.dll to your project’s references and go ahead with the below code (C# lovers may use this utility to convert the below code from VB.NET to C#):

'Import Jayrock.Json namespace, which is defined in json.dll
Imports Jayrock.Json 
 
'Write the below code in your specific events/methods
Dim rowIndex As Integer = 0 
Dim jRowArray As New JsonArray
Dim jTable As New JsonObject
 
If ds.Tables(0).Rows.Count > 0 Then
    'Loop through all the rows of datatable.              
    For Each row As DataRow In ds.Tables(0).Rows         
        Dim colIndex As Integer = 0
        'Create a new JSON array using JayRock 
        Dim jColArray As New JsonArray
        'Create a new JSON object               
        Dim jRowObj As New JsonObject               
        'Loop through all the cells of a row               
        For Each col As DataColumn In ds.Tables(0).Columns  
        'Add the value of each cell of a row to a JSON Array (jColArray)           
            jColArray.Add(row.Item(colIndex))               
            colIndex += 1        
        Next        
        'jColArray now contains data from all columns. Add this to a JSON object (jRowObj)       
        jRowObj("datarow") = jColArray                      
        rowIndex += 1        
        'Now add this JSON object to another JSON Array (jRowArray).    
        jRowArray.Add(jRowObj)                              
    Next 
End If 
jRowArray is the final JSON string, which can now be returned to your JavaScript.
The advantage of using JayRock is, you can customize what JSON data you want to return from the server. E.g., you may require to pass additional data to your JavaScript code such as refresh interval which may not be present in your datatable.
Click here to see the SQL data which will be filled in datatable.

Click here to see the JSON output (in Firebug) of the above data, taken from datatable.

Please share your thoughts. Thanks to Praveen for that! Happy Coding:)

Comments (3) -

  • mysteria

    4/28/2009 10:22:58 PM |

    How about just call the following method?
    Jayrock.Json.Conversion.JsonConvert.ExportToString(anyObj);

  • krishna

    4/30/2009 5:59:52 PM |

    Thank you!
    @mysteria: yeah...there are many methods to convert. I wanted to highlight the usage of JsonObject and JsonArray of Jayrock, which enable you to add custom json objects to your output. I'm also finding json.net quite interesting these days...

  • Lakshmanan

    6/24/2009 11:29:24 AM |

    Hi

    When you have a big recordset , looping over them and creating JSON string is time consuming. Rather convert DataTable to Serialiazble object and convert to JSON string using .NET DataContractJsonSerializer  

    System.ServiceModel.Web.dll, and you can locate this under  Namespace:  System.Runtime.Serialization.Json, But we need to decorate the class with DataContract and DataMember attributes

Comments are closed