1

I want to use .Net Framework built-in library to convert my DataTable / DataSet to JSON. How is this possible.

Saravanan
  • 7,637
  • 5
  • 41
  • 72

1 Answers1

1

DataTable and DataSet objects cannot be JSON serialized directly. You'll need to convert them first to something like 

IDictionary<string, IEnumerable<IDictionary<string, object>>>

Once you do that, you can use JavaScriptSerializer to do the actual conversion to JSON.

Update based on your comment:

If you are trying to convert the data obtained from a SQL query into JSON, represent the data in a simple class first: 

public class Employee
{
    public int EmployeeID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Read the results into: 

List<Employee>

Now, convert the list to JSON using JavaScriptSerializer.

stepanian
  • 11,373
  • 8
  • 43
  • 63
  • is there any way to get the json format of output from the sql query results. – Saravanan Aug 12 '11 at 06:06
  • The best way would be to create a class that represents the data and then put the results in a generic list of that class. Then, you can easily convert that to JSON using JavaScriptSerializer. – stepanian Aug 12 '11 at 06:09
  • actually my query outputs will be different for each time. at times i will have a 5 column output and othertimes a 10 column outputs and like.. Mainly i face this problem when using jqGrid with JSON, is there a way to bind the jqGrid with xml datasource, so that i could use dataset.getxml() directly – Saravanan Aug 12 '11 at 06:12
  • Why don't you get the xml and then convert it to JSON? http://stackoverflow.com/questions/814001/json-net-convert-json-string-to-xml-or-xml-to-json-string – stepanian Aug 12 '11 at 06:17
  • any other jquery grid is available for use in asp.net mvc which does not need us to specify the column names etc.. – Saravanan Aug 12 '11 at 06:44
  • I didn't realize you are using ASP.NET MVC. With MVC, you don't need to use the JavaScriptSerializer directly. Just use JsonResult and use the Json method to convert the object to JSON. In terms of grids, I usually prefer to write the presentation code myself. – stepanian Aug 12 '11 at 07:05
  • Thanks for your reply. I am getting the circular reference error when using JSON with datatable, dataset and datarows.`A circular reference was detected while serializing an object of type 'System.Globalization.CultureInfo'`. Anyway I can generate the JSON now with the help of code from `http://www.isolutionteam.co.uk/json-jquery-ajax-aspnet-and-c-to-get-ajaxed-data-table-rows-passing-multiple-parameters/`. However if you can give me the presentation logic alone with some jquery plugin, it would be fine for me so i can learn and use it. – Saravanan Aug 12 '11 at 08:17
  • i used the idea from `http://www.isolutionteam.co.uk/json-jquery-ajax-aspnet-and-c-to-get-ajaxed-data-table-rows-passing-multiple-parameters/` to convert to JSON. FYI. – Saravanan Aug 12 '11 at 09:57