Recursively serialize multiple Datatable hierarchies to JSON object

Question

I want to serialize multiple DataTables to JSON which are interrelated and it is possible there will be additional table are all set in the mapping table. In this case, I have 3 DataTables.

Table A as parent
Table B as the child of Table A
Table C as the child of Table B

The JSON output should be

{
    "TableA": [
        {
            "ID": "2490",
            "TYPE": "Electronic",
            "TableB": [
                {
                    "ID": "2490",
                    "ITEM": "XMT123",
                    "RECEIPT_NUM": "59",
                    "TableC": [
                        {
                            "ID": "2490",
                            "ITEM": "XMT123",
                            "QUANTITY": "164"
                        }
                    ]
                },
                {
                    "ID": "2491",
                    "ITEM": "XMT234",
                    "RECEIPT_NUM": "12",
                    "TableC": [
                        {
                            "ID": "2491",
                            "ITEM": "XMT234",
                            "QUANTITY": "92"
                        }
                    ]
                }
            ]
        },
        {
            "ID": "2491",
            "TYPE": "Electronic",
            "TableB": [
                {
                    "ID": "2491",
                    "ITEM": "XMT456",
                    "RECEIPT_NUM": "83",
                    "TableC": [
                        {
                            "ID": "2491",
                            "ITEM": "XMT456",
                            "QUANTITY": "261"
                        }
                    ]
                },
                {
                    "ID": "2492",
                    "ITEM": "XMT567",
                    "RECEIPT_NUM": "77",
                    "TableC": [
                        {
                            "ID": "2492",
                            "ITEM": "XMT567",
                            "QUANTITY": "70"
                        }
                    ]
                }
            ]
        }
    ]
}

I've already tried code like this, but it doesn't seem to work

static void dataToJson(string connection_string, string query, string table_name)
{
    try
    {
        JArray jArray = new JArray();
        DataTable tbl = new DataTable();
        DataTable inner_tbl = new DataTable();
        SqlConnection conn = new SqlConnection(connection_string);
        conn.Open();
        var adapter = new SqlDataAdapter(query, conn); // query to get parent
        adapter.Fill(tbl);
        foreach (DataRow row in tbl.Rows)
        {
            JObject jo = new JObject();
            foreach (DataColumn col in tbl.Columns)
            {
                jo.Add(new JProperty(col.ColumnName.ToString(), row[col].ToString()));
            }
            jArray.Add(jo);

            query = "i have query to get child";
            dataToJson(connection_string, query, table_child);                  
        }
    }
    catch (Exception e)
    {
        WriteLog(e.Message, GetCurrentMethod(e));
    }
}

jhmt · Accepted Answer · 2015-10-25T21:52:21.490

You have to pass the parent or return the child in this recursive method. Also, you don't have to open SqlConnection every time if all the tables are in the same database.
Here is an example of passing the parent:

// Caller
JObject root = new JObject();
using (SqlConnection conn = new SqlConnection(connection_string))
{
    conn.Open();
    dataToJson(root, conn, query, "TableA");
}
Console.WriteLine(root.ToString());


static void dataToJson(JObject parent, SqlConnection conn, string query, string table_name)
{
    if (string.IsNullOrEmpty(table_name)) { return; }
    try
    {
        JArray jArray = new JArray();
        DataTable tbl = new DataTable();
        //DataTable inner_tbl = new DataTable();
        //SqlConnection conn = new SqlConnection(connection_string);
        //conn.Open();
        var adapter = new SqlDataAdapter(query, conn); // query to get parent
        adapter.Fill(tbl);
        foreach (DataRow row in tbl.Rows)
        {
            JObject jo = new JObject();
            foreach (DataColumn col in tbl.Columns)
            {
                jo.Add(new JProperty(col.ColumnName.ToString(), row[col].ToString()));
            }
            query = "i have query to get child";
            // Set the child table name to "table_child"
            dataToJson(jo, conn, query, table_child);  // Pass the JObject as the parent

            jArray.Add(jo); 
            parent.Add(new JProperty(table_name, jArray));
        }
    }
    catch (Exception e)
    {
        WriteLog(e.Message, GetCurrentMethod(e));
    }
}

By the way, I'm not sure how you get the child table's name so I used the following dictionary for testing.

static Dictionary<string, string> table_hierarchy = new Dictionary<string, string>();
// Initialize
table_hierarchy.Add("TableA", "TableB");
table_hierarchy.Add("TableB", "TableC");
table_hierarchy.Add("TableC", "");

It's work only 2 record on TableA. 3 next record it got error. Can not add property TableB to Newtonsoft.Json.Linq.JObject. Property with the same name already exists on object. — frenzybluez, Oct 26 '15 at 09:03
now it is working great, after catching with no exception. Many thanks. — frenzybluez, Oct 26 '15 at 18:23
Thank you for marking my answer as the best answer. Let me know if I need to update something on this. — jhmt, Oct 26 '15 at 19:50

Recursively serialize multiple Datatable hierarchies to JSON object

1 Answers1