Transform a DataSet to JSON using Json.NET

It was my duty recently to convert some data from SQL Server 2005 into JSON. Initially, I started writing a bunch of UDF’s in SQL Server that built up a string representation of the data in JSON format. The problem with this method is that it is fiddly, slow and does not take into account JSON data types. So I turned to Json.NET by James Newton-King. What I liked about this library is it’s support for LINQ and XML.

Here is a basic method for converting a DataSet that contains one table into JSON. Note the call to AsEnumerable() on the DataTable so that we can then use the LINQ support provided by Json.NET.

private static string ConvertDataToJson()
{
    JObject trans =
      new JObject(
        new JProperty("data",
          new JArray(
            from r in ds.Tables[0].AsEnumerable()
            select new JObject(
                ConvertRowToJPropertyArray(r)
              ))));
    return trans.ToString();
}

And the ConvertRowToJPropertyArray method looks like this:

private static JProperty[] ConvertRowToJPropertyArray(DataRow dr)
{
	JProperty[] o = new JProperty[dr.Table.Columns.Count];
	for (int i=0; i<dr.Table.Columns.Count; i++) {
		o[i] = new JProperty(dr.Table.Columns[i].ColumnName, dr[dr.Table.Columns[i]]);
}
return o;

In the next post I will consider a DataSet that contains multiple tables and relations.