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));
    }
}

Answer 1

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", "");