How do I get a complex json object from a database

Question

Hi,

I have a stored procedure that returns Sites and Clients. One Site can have many Clients.

How can I serialize it to a json object so that I get this:

var posterArray = ([
                           { SiteId: 1,
                             SiteName: "Mail",
                             ClientSite:[
                                { ClientId: 1, ClientName: "Mail Client A" }, { ClientId: 1, ClientName: "Mail Client B"}] 
                           },

                           { SiteId: 2,
                             SiteName: "DSAC",
                             ClientSite:
                                { ClientId: 1, ClientName: "DSAC Client A" }
                           }
    ]);

Do I have to user For Xml Auto and get an xml back and then convert it to json?

There would be quite a lot of working in transforming from xml to json. I hope there is a better way.

Cheers

C

Answer 1

Any suggestion?

Cheers

C

Answer 2

Hi Claudio,

What you need is the Json.NET framework, which has the feature of "Flexible JSON serializer for converting between .NET objects and JSON", that meets your situation:
http://json.codeplex.com/.

Have a nice day,

---

Leo Liu [MSFT]
MSDN Community Support | Feedback to us

Answer 3

Hi Leo,

Thanks for your reply. I had a look at the framework you suggested but could not find anything relating to storec procs.

This is the sort of store procs I am using

select c.companyid, c.companyname, u.firstname, u.lastname from tblcompany c
inner join tbluser u on c.companyid = u.companysubscriberid

As you can see the inner joind returns a flat structure.

companyid companyname firstname lastname
7 Cleve                                 Joe Dennehy
7 Cleve                                 Kathy Dunasky
17 Media, Inc.                       Roslyn Liberti
17 Media, Inc.                       Mark Bugni
17 Media, Inc.                       Alison McCauley
17 Media, Inc.                       Tom Hennessey
43 Med Society                     NEJM Shared

I would like to convert this result set to a hierarchical json object with only one company per user
7 Cleve
      user a
      user b
17 Media
      user c
      user d

etc..

Will I have to create an object to mimic my object graph?

Could you please provide some sample code?

Cheers

C

 

Answer 4

Any more help?

Cheers

C

Answer 5

Hi Claudio,

For your sql part, you can implement it via Linq to SQL GroupBy() method.
http://blog.csainty.com/2008/01/linq-to-sql-groupby.html
While there is a dedicated forum: LINQ to SQL Forum.

Then for Json.NET framework related problems please post in the dedicated discussion board as below:
http://json.codeplex.com/discussions.

Thanks for your understanding and have a nice day,

---

Leo Liu [MSFT]
MSDN Community Support | Feedback to us

Answer 6

Hi Claudio,

How is it going now with my suggestion?
We are looking forward to hearing from you.

Have a nice day,

---

Leo Liu [MSFT]
MSDN Community Support | Feedback to us

Answer 7

Hi Leo,

Thanks for your help.

I still have not figured out how to do it. I had a look at the tutorial about LINQ to SQL you suggested and it is quite useful. However, since I am not using the Entity Framework to fecth data (in this case I am using legacy ADO.NET with Stored Procs to fetch data) I was wondering if the group statement is going to work.

Any suggestions?

Cheers

C

Answer 8

Hi Leo,

I have trid this code below but I am not able to get the other properties apart from the Key.

Could you please shed some light? 

var cps = from comp in Core.Data.GetAllCompanies(clientID, posterSiteID, null)
                      group comp by comp.companyId into g
                      select new
                      {
                          CompanyID = g.Key,
                          name = comp.companyName //ERROR HERE I have also tried g.companyName
                      };

Cheers,

C

Answer 9

Hi Claudio,

You used linq group by, while here it will get you confused that it generates an expression tree.
Here we can just use the extension method IEnumerable<T>.GroupBy method:

var cps = Core.Data.GetAllCompanies(clientID,posterSiteID,null)
         .GroupBy(c => new { c.companyId, c.companyName })

To iterate through the grouped result, we can perform like this:

            foreach (var i in cps)
            {
                Console.WriteLine(i.Key);

                foreach (var c in i)
                    Console.WriteLine("\t{0}", c.companyName);
            }

Have a nice day,

---

Leo Liu [MSFT]
MSDN Community Support | Feedback to us

Answer 10

Hi Leo,

Thanks for the sample code. I will try that and let you know how I get on.

Cheers

C