Converting datatable into hierarchical data structure (JSON) using C#

Question

I am executing a SQL query into datatable. The query can return multiple columns. The result is in key value format and represent hierarchical data. See the below screenshot.

The image shows 3 parts. First the data, then the hierarchical representation of data and JSON equivalent.

Currently the image shows 4 level of data, but we can have 6-7 levels of data. The format will remain same but the number of columns can change.

How can i get the desired result using C#? ? I know it is basic programming but I am having hard time with it.

Answer 1

With a fixed-level hierarchy like you are showing in your example, you can use a LINQ query with grouping to generate the tree structure for your JSON. Here is an example with a three-level hierarchy:

static void Main(string[] args)
{
    var data = new List<Data>
    {
        new Data("Food", "1_g", "beverage", "2_b", "hot", "3_h"),
        new Data("Food", "1_g", "beverage", "2_b", "cold", "3_c"),
        new Data("Food", "1_g", "fruit", "2_f", "juicy", "3_j"),
        new Data("Food", "1_g", "fruit", "2_f", "solid", "3_s"),
        new Data("Food", "1_g", "cookie", "2_c", "chocolate", "3_cho"),
    };

    var tree = from l1 in data
                group l1 by new { key = l1.Key_L1, name = l1.L1 } into group1
                select new
                {
                    key = group1.Key.key,
                    name = group1.Key.name,
                    children = from l2 in group1
                                group l2 by new { key = l2.Key_L2, name = l2.L2 } into group2
                                select new
                                {
                                    key = group2.Key.key,
                                    name = group2.Key.name,
                                    children = from l3 in group2
                                                select new { key = l3.Key_L3, name = l3.L3 }
                                }
                };

    var serializer = new JavaScriptSerializer();
    Console.WriteLine(serializer.Serialize(tree));
    Console.ReadLine();
}

class Data
{
    public Data(string l1, string k1, string l2, string k2, string l3, string k3)
    {
        L1 = l1; Key_L1 = k1;
        L2 = l2; Key_L2 = k2;
        L3 = l3; Key_L3 = k3;
    }
    public string L1 { get; set; }
    public string Key_L1 { get; set; }
    public string L2 { get; set; }
    public string Key_L2 { get; set; }
    public string L3 { get; set; }
    public string Key_L3 { get; set; }
}

The above is a working example of the technique using POCOs.

You mention "datatable"; I assume this is referring to the .NET DataTable class? If so, you can use LINQ to query a DataTable. You just need to convert it to an enumerable using the DataSetExtensions. See: LINQ query on a DataTable

Then in the LINQ statement, you replace the list with your data table .AsEnumerable() and replace the property references with .Field<string>(""). Like so:

DataTable dt = // load data table
var tree = from l1 in dt.AsEnumerable()
           group l1 by new { key = l1.Field<string>("Key_L1"), name = l1.Field<string>("L1") } into group1
           select new
           {
               // etc.
           };

For a variable number of columns, you have to use a recursive approach, like so:

var tree = Descend(dt.AsEnumerable(), 1, 3);

private static System.Collections.IEnumerable Descend(IEnumerable<DataRow> data, int currentLevel, int maxLevel)
{
    if (currentLevel > maxLevel)
    {
        return Enumerable.Empty<object>();
    }
    return from item in data
            group item by new
            {
                key = item.Field<string>("Key_L" + currentLevel),
                name = item.Field<string>("L" + currentLevel)
            } into rowGroup
            select new
            {
                key = rowGroup.Key.key,
                name = rowGroup.Key.name,
                children = Descend(rowGroup, currentLevel + 1, maxLevel)
            };
}

One thing to note is that this approach creates an empty children collection on the leaf nodes.