Change format of JSON data using Web API ASP.NET framework

Question

I want to change the format of JSON data. i am using the Visual Studio 2022 doing a Web API and I didn't class for my API.

This is what I expect:

The result shows:

[{"Id":45,"Name":"Emily","Age":3},{"Id":19,"Name":"Peter","Age":1}]

Here is my code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;


namespace ApiTesting.Controllers
{
    public class TestController : ApiController
    {
        SqlConnection con = new SqlConnection(@"server=DESKTOP-US2AF5N; database=dbTest; Integrated Security = true;");

        public string Get()
        {
            SqlDataAdapter da = new SqlDataAdapter("Select * From tblTest", con);
            DataTable dt = new DataTable();
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                return JsonConvert.SerializeObject(dt);
            }
            else
            {
                return "No Data Found";
            }
        }
}

The attributes in your result ("UserID", "Name" and "Access") do not match the expected attributes ("Id","Name" and "Age"). Those attributes should be defined in the class that is serialized. It is UNKNOWN how anyone could give a correct answer, because we do not know the value of "Age" (which is in your expected result). — Luuk, Jul 23 '22 at 07:43
You might start by changing the SQL to: `Select UserID as Id, Name, Access as Age From tblTest`, (But this will not get the correct value for the "Age"!) — Luuk, Jul 23 '22 at 07:46
The image is just a example, first time using stack overflow, sorry — Reborn, Jul 23 '22 at 07:47
To solve the problem with the image, please read: [Why not upload images of code/errors when asking a question?](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question) — Luuk, Jul 23 '22 at 07:49
After your edit, the question is unclear. Or is it really only the format? Then please tak a look at: [How do I get formatted JSON in .NET using C#?](https://stackoverflow.com/questions/2661063/how-do-i-get-formatted-json-in-net-using-c) — Luuk, Jul 23 '22 at 07:53
Are you using JSON.NET? In which case, you can pass `Formatting.Indented` as the 2nd argument to the `JsonConvert.SerializeObject` method to get a formatted output. — snixtho, Jul 23 '22 at 07:53
no i am using asp.net framework, and i also tried use `Formatting.Intented` — Reborn, Jul 23 '22 at 07:59
It looks like you don't know what you are using. JsonConvert is from Json.Net, no? As said adding Formatting.Intended to SerializeObject() does it. — Cetin Basoz, Jul 23 '22 at 08:04
If I don't use `JsonConvert.SerializeObject`, what i can use for show data in web api in json format? — Reborn, Jul 23 '22 at 08:05
Try to change the format of json data in the webapi with my code — Reborn, Jul 23 '22 at 08:09

score 1 · Answer 1 · answered Jul 23 '22 at 08:23

1

To convert it to an intended format, all you need is to add Formatting.Intended parameter. ie:

string connectionString = @"server=.;database=dbTest;Trusted_Connection=yes;";

public string Get()
{
    DataTable dt = new DataTable();
    new SqlDataAdapter("Select Id, Name, Age From tblTest", connectionString).Fill(dt);
    if (dt.Rows.Count > 0)
    {
        return JsonConvert.SerializeObject(dt, Newtonsoft.Json.Formatting.Indented);
    }
    else
    {
        return "No Data Found";
    }
}

Here is a sample using Northwind sample database:

void Main()
{
    DataTable tbl = new DataTable();
    new SqlDataAdapter("select top(5) customerId, companyName, ContactName from customers",
    @"server=.;Database=Northwind;Trusted_Connection=yes").Fill(tbl);


    string output = JsonConvert.SerializeObject(tbl, Newtonsoft.Json.Formatting.Indented);
    Console.WriteLine(output);
}

And the output is:

[
  {
    "customerId": "ALFKI",
    "companyName": "Alfreds Futterkiste",
    "ContactName": "Maria Anders"
  },
  {
    "customerId": "ANATR",
    "companyName": "Ana Trujillo Emparedados y helados",
    "ContactName": "Ana Trujillo"
  },
  {
    "customerId": "ANTON",
    "companyName": "Antonio Moreno Taquería",
    "ContactName": "Antonio Moreno"
  },
  {
    "customerId": "AROUT",
    "companyName": "Around the Horn",
    "ContactName": "Thomas Hardy"
  },
  {
    "customerId": "BERGS",
    "companyName": "Berglunds snabbköp",
    "ContactName": "Christina Berglund"
  }
]

answered Jul 23 '22 at 08:23

Cetin Basoz

22,495
3
31
39

This XML file does not appear to have any style information associated with it. The document tree is shown below. `[ { "Id": 1, "Name": "Emily", "Age": 3 }, { "Id": 2, "Name": "Peter", "Age": 1 }, { "Id": 3, "Name": "John", "Age": 10 }, { "Id": 4, "Name": "Simon", "Age": 12 }, { "Id": 5, "Name": "Dickson", "Age": 21 } ]` This what I have in the chrome – Reborn Jul 23 '22 at 08:34
@Reborn It looks to me that you have configured ASP.NET to return XML-formatted output by default. Normally you can simply return the object from the controller action and it auto-formats for you. What you are doing is to return a string from the controller, and the internal formatter outputs the string as XML. Here is more info about output formatting in ASP.NET that might help you (Check under *Configure formatters*): https://learn.microsoft.com/en-us/aspnet/core/web-api/advanced/formatting?view=aspnetcore-6.0 – snixtho Jul 23 '22 at 09:09
@Reborn, it is not JSON. It is XML. Try with accept encoding of JSON. Also return Json(...). – Cetin Basoz Jul 23 '22 at 10:02
how to do the return json if mind is not correct? – Reborn Jul 23 '22 at 10:27
You are not even saying what you are really using. It looks too early for you to do this. First you should learn basics. Check config.Formatters.JsonFormatter.SupportedMediaTypes.Add(newMediaTypeHeaderValue("text/html")); (HttpConfig) – Cetin Basoz Jul 23 '22 at 10:48
i have tried your code before it removed the `` but the output is still the same, the result doesn't look like your code – Reborn Jul 23 '22 at 11:06
what does it look like :) – Cetin Basoz Jul 23 '22 at 12:05

Change format of JSON data using Web API ASP.NET framework

1 Answers1