Geeks With Blogs
Krishna's weblog Frozen thoughts of a budding developer - jQuery, ASP.NET, AJAX and more...

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)           
            colIndex += 1        
        '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).    
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:)

Posted on Wednesday, April 22, 2009 5:52 PM jQuery | Back to top

Comments on this post: Converting ASP.NET DataTable to JSON using JayRock

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © novogeek | Powered by: