How to convert SQL Server data list into a structured JSON object with C# MVC4

Question

I am developing an MVC application that retrieves data from multiple tables in SQL Server. The query builds a structured hierarchical JSON file for me that I embed in my d3.js tree view file. This is all working great for me at the prototype level. Now I need to build the JSON dynamically in C# and pass it to a view to consume.
I need assistance building the C# logic to convert data into a structured JSON object. I have reviewed this article but still need assistance Converting flattened hierarchical data from SQL Server into a structured JSON object with C#/Linq .

Here is a link to JSfiddle.net of a working demo http://jsfiddle.net/hoopkjaz/d5Azm/ - it has the embedded JSON that my question is about starting on line 229.

As you can see the JSON can have up to 10 levels of children. The JSON I am using here is the largest it will ever get.

Here is the tsql that creates the JSON

set nocount on

declare
  @sponsors table
  (num int,
   id int)

declare
  @customer_id int,
  @sponsor_id int,
  @sponsor_num int,
  @last_sponsor_id int,
  @last_customer_id int,
  @new_sponsor int,
  @tmp_num int,
  @tmp_id int,
  @json_text nvarchar(400);

declare data_cursor cursor for
with cte (customerid,
          thepath) as (
  SELECT customerid,
         convert(varbinary(max), customerid) as thepath
    FROM customers
    where customerid = 10013
    union all
    SELECT a.customerid,
         c.thepath + convert(varbinary(max), a.customerid) as thepath
    FROM customers a
         inner join cte c on
           a.sponsorid = c.customerid)
            select cus.customerid,
                   cus.sponsorid,
                   '{"ID": "' + convert(varchar(10),cus.customerid) + '",' +
            '"name": "' + 'cus.firstname ' + ' ' + 'cus.lastname' + '",' +
            '"fname": "' + cus.firstname + '",' +
            '"type": "' + case cus.customertypeid when 1 then 'C' else 'MP' end + '",' +
            '"phone": "' + '602-555-1212' + '",' +
            '"email": "' + 'dev@gmail.com' + '",' +
            '"paidranktxt": "' + isnull(pr.rankdescription,'') + '",' +
            '"ranktxt": "' + isnull(r.rankdescription,'') + '",' +
            '"PQV": "' + convert(varchar(10),isnull(pv.volume3,0)) + '",' +
            '"GQV": "' + convert(varchar(10),isnull(pv.volume5,0)) + '"' as json
            from cte
                  inner join customers cus
                 on cte.customerid = cus.customerid
                 and cus.customerstatusid <> 0
                   inner join periods per
                 on per.periodtypeid = 1
                 and per.periodid =
                 (select max(pv.periodid)
                    from periodvolumes pv
                   where pv.periodtypeid = 1)
               left join periodvolumes pv
                 on pv.customerid = cus.customerid
                 and per.periodid = pv.periodid
                 and per.periodtypeid = pv.periodtypeid
               left join ranks pr
                 on pv.paidrankid = pr.rankid
               left join ranks r
                 on pv.rankid = r.rankid
        order by cte.thepath;

    declare sponsor_cursor cursor for
      select num, id
        from @sponsors
       order by num desc;

    open data_cursor

    fetch next from  data_cursor
    into @customer_id,
         @sponsor_id,
         @json_text

    set @last_customer_id = 0;
    set @last_sponsor_id = 0;
    set @sponsor_num = 0;

    while @@fetch_status = 0
    begin
      set @new_sponsor = 0;

      if @last_customer_id = @sponsor_id
        begin
          print ', "children": ['
          set @sponsor_num = @sponsor_num + 1
    --print 'Insert: ' + convert(varchar(10),@sponsor_num) + ' ' + convert(varchar(10),@last_customer_id)
          insert into @sponsors (num, id)
            select @sponsor_num, @last_customer_id
        end
      else if @last_sponsor_id = @sponsor_id
        print '},'

      else if @last_customer_id > 0
        begin
          print '}'

          open sponsor_cursor

          fetch next from  sponsor_cursor
            into @tmp_num,
                 @tmp_id

          while @@fetch_status = 0
            begin
    --print 'Number: ' + convert(varchar(10),@tmp_num)
    --print 'ID: ' + convert(varchar(10),@tmp_id)

              if @sponsor_id = @tmp_id
                begin
                  print ','
                  set @new_sponsor = @tmp_num
                  break
                end
              else
                begin
                  set @sponsor_num = @sponsor_num - 1
                  print '] }'
                end

              fetch next from  sponsor_cursor
                into @tmp_num,
                     @tmp_id
            end
          close sponsor_cursor;
        end

      if @new_sponsor > 0
        delete from @sponsors where num > @new_sponsor

    --print 'Sponsor: ' + convert(varchar(10),@sponsor_id)
      print @json_text

      set @last_customer_id = @customer_id;
      set @last_sponsor_id = @sponsor_id;

      fetch next from data_cursor
      into @customer_id,
           @sponsor_id,
           @json_text
    end

    close data_cursor;

    deallocate data_cursor;

       begin
          print '}'

          open sponsor_cursor

          fetch next from  sponsor_cursor
            into @tmp_num,
                 @tmp_id

          if @@fetch_status <> 0
            print '] }'

          while @@fetch_status = 0
            begin
              if @sponsor_id = @tmp_id
                begin
                  print '] }'
                  set @new_sponsor = @tmp_num
                  break
                end
              else
                begin
                  set @sponsor_num = @sponsor_num - 1
                  print '] }'
                end

              fetch next from  sponsor_cursor
                into @tmp_num,
                     @tmp_id
            end
          close sponsor_cursor;
        end

Here is the class that should hold the list

public class Node
    {
        public Node() {
       Children = new List<Node>();
    }

    public int ID { get; set; }
    public string Name { get; set; }
    public string Fname { get; set; }
    public string Type { get; set; }
    public string Phone { get; set; }
    public string Email { get; set; }
    public string Paidranktxt { get; set; }
    public string Ranktxt { get; set; }
    public string PQV { get; set; }
    public string GQV { get; set; }
    public List<Node> Children { get; set; }
}   

Here is the method I use to pull the list from SQL

 public static IEnumerable<Node> GetNode(int? customerId)
    {
        try
        {

            const string query = @"  SQL from above goes here";                           

            var jsonResult = TransactionManager.Current.Entities.Database.SqlQuery<Node>(query,new SqlParameter("CustomerID", customerId));                                                                          
            return jsonResult;
        }
        catch (Exception e)
        {
            // log the error in Elmah
            e.LogToElmah();
            return null;
        }

Here is an example of the SQL before formatting. We use the sponsorid to determine the children

ID  sponsorid name  fname   type phone        email    paidranktxt ranktxt  PQV GQV
10013 10000   first Brian   MP   602-555-1234 d@m.com  TLeader      TL  0.00    13740.00
10624 10013   first Brian   MP   602-555-1234 d@m.com  TLeader      TL  0.00    13740.00
10975 10624   first Brian   MP   602-555-1234 d@m.com  TLeader      TL  0.00    13740.00

Answer 1

Ok so if I understand correctly you will stop using the t-sql code to generate JSON, and instead want to convert a hierarchy of Node objects into JSON using C#.

Assuming that is correct, my suggestion is to simply use JSON.Net, and if you are using WebAPI (ie you will get the data using javascript in an async request for example), then this will be as simple as having a controller with an action like this:

[System.Web.Http.HttpGet]
public IEnumerable<Node> GetNodes() {
   IEnumerable<Node> nodes;
   ...
   // code to get Nodes from DB goes here
   ...

   return nodes;
}

To configure JSON.Net to be used in WebAPI, something like this in your Global.asax will work:

var config = GlobalConfiguration.Configuration;
var index = config.Formatters.IndexOf(config.Formatters.JsonFormatter);
config.Formatters[index] = new JsonMediaTypeFormatter
{
    SerializerSettings = new JsonSerializerSettings
    {
        //ContractResolver = new CamelCasePropertyNamesContractResolver(),
        DateTimeZoneHandling = DateTimeZoneHandling.Local
    }
};

Finally to convert your flat data structure into a hierarchical one:

class Program
{
    public class Node
    {
        public Node()
        {
            Children = new List<Node>();
        }

        public int ID { get; set; }
        public List<Node> Children { get; set; }
    }

    public class NhNode
    {
        public int ID { get; set; }
        public int SponsorId { get; set; }
    }

    static Node ConvertToHierarchy(NhNode nhnode, IEnumerable<NhNode> nodes)
    {
        var node = new Node()
        {
            ID = nhnode.ID
        };

        foreach (var item in nodes.Where(p => p.SponsorId == nhnode.ID))
        {
            node.Children.Add(ConvertToHierarchy(item, nodes));
        }

        return node;
    }

    static void Main(string[] args)
    {
        List<NhNode> nhnodes = new List<NhNode>() {
            new NhNode() { ID = 10013, SponsorId = 10000 },
            new NhNode() { ID = 10624, SponsorId = 10013 },
            new NhNode() { ID = 10975, SponsorId = 10624 }
        };

        var node = ConvertToHierarchy(nhnodes.First(p => p.SponsorId == 10000), nhnodes);
    }
}