Transform a DataSet to JSON using Json.NET (Part 2)
19 Oct 2009Here is how to transform a DataSet which contains multiple DataTable and DataRelations, well at least, one way you can do it! See Transform a DataSet to JSON using Json.NET (Part 1) for details on how to transform a single DataTable into JSON using Json.NET. **
Note that this is designed to work when loading one root row at a time. For example, if you have a database of people and there were related tables for work experience and work experience details then you would load one person and all their related work experience into the DataSet and then use this solution to convert it into JSON.
The DataSet might be designed as follows:
We start with the ConvertDataToJson() method which has the same principal purpose as the example in Part 1 except it calls another method GetRelatedData() to return the JSON. Therefore the ConvertDataToJson() is straightforward and looks as follows:
The GetRelatedData() method converts the related data into JSON. We first need to calculate the number of related DataTables in the DataSet. That gives us the size we need for our JProperty array. Then, as we loop through each DataTable, we set the name of each JProperty to the name of the DataTable. Note, however, that we check if ds.Tables[i].ParentRelations.Count is greater than 0 before creating a new JArray. This is because we only want to convert the related data into a JArray once and therefore have it be part of a related JSON element once.
If the current table does not have any parent relations we create a JArray and using Linq we enumerate through each row in the table calling ConvertRowToJObject(r) and GetChildRows(r). Here is the complete method:
In case your interested the code to calculate the relatedDataArraySize is:
The GetChildRows(DataRow) method is as follows. Notice that it is a recursive method so that it will keep calling itself until there are no more child rows for the current DataRow.