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:)