CODEWITHSUNDEEP
c#sqljson
Uladz Kha
Uladz Kha

Reputation: 2354

How to get data from sql in json format

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

enter image description here

How i can convert data to Json ?

Upvotes: 1

Views: 5503

Answers (3)

Ian
Ian

Reputation: 3149

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.

Upvotes: 1

safin chacko
safin chacko

Reputation: 1390

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')

Upvotes: 2

Vadim Martynov
Vadim Martynov

Reputation: 8892

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

Upvotes: 4

Related Questions