.NET Core API - Convert DataTable to JSON (Deep)

Question

I am executing a stored procedure on my server which returns a DataTable. The DataTable has some columns that have JSON data in them. When I return the result to the client the top-level of the DataTable is properly formatted to JSON but the columns with JSON data are returned as strings. How can I convert the entire table (including columns with JSON) to JSON before returning to the client?

DataTable

ColA        ColB         ColC
1           Test         [{"ColD":2,"ColE":"Another Test"}]

What I'm Getting

[
  {
    "ColA": 1,
    "ColB": "Test",
    "ColC": "[{\"ColD\":2,\"ColE\":\"Another Test\"}]"
  }
]

What I Need

[
  {
    "ColA": 1,
    "ColB": "Test",
    "ColC": [
      {
        "ColD": 2,
        "ColE": "Another Test"
      }
    ]
  }
]

Does this answer your question https://stackoverflow.com/questions/17398019/convert-datatable-to-json-in-c-sharp/17398078#17398078 — MBB, Jun 05 '20 at 15:21

score 0 · Accepted Answer · edited Jun 05 '20 at 15:01

I had a breakthrough after posting the question. I created an extension method for the DataTable that would convert the DataTable to a List<Dictionary<string, object>>. In the process I check for values that start with [ or { and deserialize them using JsonConvert.DeserializeObject.

public static List<Dictionary<string, object>> ToJson(this DataTable dt)
{
    var list = new List<Dictionary<string, object>>();

    foreach (DataRow row in dt.Rows)
    {
        var dict = new Dictionary<string, object>();

        foreach (DataColumn col in dt.Columns)
        {
            if (row[col].ToString().StartsWith('{') || row[col].ToString().StartsWith('['))
            {
                dict[col.ColumnName] = JsonConvert.DeserializeObject(row[col].ToString());
            }
            else
            {
                dict[col.ColumnName] = row[col];
            }
        }
        list.Add(dict);
    }

    return list;
}

.NET Core API - Convert DataTable to JSON (Deep)

1 Answers1