Convert DataTable to JSON

Question

I've reviewed the answers here:

But they don't help for my particular use case. I am retrieving a DataTable from a SQL data adapter and want to convert the DataTable into a List (that's easy enough) and then serialize the List into JSON (using JSON.net, that's easy enough).

The problem is that I seem to have to use List. Ok, fine - so I have this code:

DataTable result = GoMagicallyGatherSomeData();
List<DataRow> ret = new List<DataRow>();
if (result != null && result.Rows.Count > 0)
{
  foreach (DataRow curr in result.Rows)
  {
    ret.Add(curr);
  }
}

or

DataTable result = GoMagicallyGatherSomeData();
List<DataRow> ret = result.AsEnumerable().ToList();

When I go to serialize the List, it... isn't what I expect.

What I would like to get back is:

[  
   {  
      "TestId":1,
      "AccountId":1,
      "SomeString":"This is an updated test",
      "SomeTimestamp":"2016-01-01T00:00:00Z",
      "SomeDecimal":5.55
   },
   {  
      "TestId":3,
      "AccountId":1,
      "SomeString":"This is a third test",
      "SomeTimestamp":"2016-01-01T00:00:00Z",
      "SomeDecimal":5.55
   },
   { ... removed for brevity ... }
]

And what I actually get back is:

[  
   {  
      "RowError":"",
      "RowState":2,
      "Table":[  
         {  
            "TestId":1,
            "AccountId":1,
            "SomeString":"This is an updated test",
            "SomeTimestamp":"2016-01-01T00:00:00Z",
            "SomeDecimal":5.55
         },
         {  
            "TestId":3,
            "AccountId":1,
            "SomeString":"This is a second test",
            "SomeTimestamp":"2016-01-01T00:00:00Z",
            "SomeDecimal":5.55
         }, 
         { ... removed for brevity ... }
      ],
      "ItemArray":[  
         1,
         1,
         "This is an updated test",
         "2016-01-01T00:00:00Z",
         5.55
      ],
      "HasErrors":false
   },
   {  
      "RowError":"",
      "RowState":2,
      "Table":[  

      ... there seems to be an instance of this for every row in the result ...

      ],
      "ItemArray":[  
         1,
         1,
         "This is an updated test",
         "2016-01-01T00:00:00Z",
         5.55
      ],
      "HasErrors":false
   }
]

The other challenge is that I need to do this without awareness of the actual type of the data.

Any insight? Anyone have a suggestion on the best way to do this? Could I get away with copying out the 'table' array from the first serialized DataRow, or, could subsequent serialized DataRows actually contain different data than the first?

You need a DTO to generate the JSON you're after. – rism Sep 16 '16 at 02:29 — rism, Sep 16 '16 at 02:29

score 7 · Accepted Answer · edited May 23 '17 at 11:48

You could convert the DataTable into a List<dynamic> and then convert it to json. For sample, to convert to a dynamic list:

public static List<dynamic> ConvertToDataTable(DataTable dataTable)
{
    var result = new List<dynamic>();
    foreach (DataRow row in dataTable.Rows)
    {
        dynamic dyn = new ExpandoObject();      
        foreach (DataColumn column in dataTable.Columns)
        {
            var dic = (IDictionary<string, object>)dyn;
            dic[column.ColumnName] = row[column];
        }
        result.Add(dyn);
    }
    return result;
}

You also could make an extension method like this post. Then use it and to convert to List<dynamic> and finally convert the list to json using the JsonConvert from Newtonsoft.Json (a.k.a. Json.Net). For sample:

var list = ConvertToDataTable(dataTable);

var json = JsonConvert.SerializeObject(list);

You are my hero. This is exactly what I needed. Perfect. Thank you! — joelc, Sep 16 '16 at 02:44
Neat solution as it implicitly consider a `DataRow` as `IDictionary`, which helps in effective Json Serialization — Mrinal Kamboj, Sep 16 '16 at 10:32

Convert DataTable to JSON

1 Answers1