How to get data from sql in json format

Question

i use the next for getting data from sql db and show it in console(for example)

DataContextDataContext ob = new DataContextDataContext();
foreach (var emp1 in ob.DimProducts)
{
    Console.WriteLine(emp1);
}

foreach (var emp2 in ob.DimProductCategories)
{
     Console.WriteLine(emp2);
}

foreach(var emp3 in ob.DimProductSubcategories)
{
    Console.WriteLine(emp3);
}

How i can convert data to Json ?

Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. Some RDBMS have native support for JSON - others not — marc_s
– marc_s, Commented Feb 12, 2016 at 8:33

Vadim Martynov · Accepted Answer · 2016-02-12 08:30:28Z

4

You should use Json.NET library and JsonConvert.SerializeObject() for it.

DataContextDataContext ob = new DataContextDataContext();
foreach (var emp1 in ob.DimProducts)
{
    Console.WriteLine(JsonConvert.SerializeObject(emp1, Formatting.Indented));
}
foreach (var emp2 in ob.DimProductCategories)
{
     string jsonEmp2 = JsonConvert.SerializeObject(emp2, Formatting.Indented)
     Console.WriteLine(jsonEmp2);
}

There are many examples in the documentation.

You can also install Json.NET from NuGet console:

PM> Install-Package Newtonsoft.Json

answered Feb 12, 2016 at 8:30

Vadim Martynov

8,9705 gold badges36 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

safin chacko · Accepted Answer · 2016-02-12 08:45:32Z

2

JSON Data (SQL Server) - I think you can use like this in your query Ex:

"SELECT name, surname FROM emp FOR JSON AUTO"

Result will be like this :

    [ 
   { "name": "John" },
   { "name": "Jane", "surname": "Doe" }
]

Another example query is

  SELECT * FROM OPENJSON(@json, N'lax $.info')

answered Feb 12, 2016 at 8:45

safin chacko

1,3901 gold badge11 silver badges18 bronze badges

1 Comment

Dorian Farrimond Over a year ago

You need SQL Server 2016 for this though.

Ian · Accepted Answer · 2017-09-04 14:24:50Z

1

If you're using Azure or Sql Server 2016 you can get JSON directly from the sql server by adding FOR JSON PATH to the end of your SQL query.

answered Sep 4, 2017 at 14:24

Ian

3,1992 gold badges32 silver badges33 bronze badges

Collectives™ on Stack Overflow

How to get data from sql in json format

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related