Formatting JSON response

Question

====Summary====

I need some help trying to format my JSON response. I'm using ASP.Net with a Model and a Controller.

====Information====

I am working on a web api in ASP.Net. I have a SQL backend in which I fetch some data and put it into a DataTable. My DataTable looks like this:

+----+-------+-------+
| ID | Title | Users |
+----+-------+-------+
|  1 | Test  | user1 |
|  1 | Test  | user2 |
+----+-------+-------+

NOTE: One record can have multiple users which is why ID is "1" for both rows ("ID" is not the unique ID of the actual SQL table row but rather a foreign key...anyway, I digress... )

I have created a Model in C# that looks like this:

    public class Record
        {
            public int ID { get; set; }
            public string Title {get; set;}
            public string Users {get; set;}
        }

Finally my Controller looks like this

 DataBaseHelper db = new DataBaseHelper();

    public IEnumerable Get_Record(string id)
    {
        // Get DataTable
        DataTable dt = new DataTable();
        dt = db.GetRecord(id);

        foreach (DataRow row in dt.Rows)
        {
            yield return new Record
            {
                ID = row.Field("ID"),
                Title = row.Field("Title"),
                Users = row.Field("Users")
            };
        }

    }

When I call the API I get this:

    [
      -{
         ID: 1,
         Title: "Test",
         Users: "user1"
       },

      -{
         ID: 1,
         Title: "Test",
         Users: "user2"
       }
    ]

====QUESTION====

How would I get the JSON response to look something sort of like this (if possible):

    {
     "Response":
       [
        {
          ID: 1,
          Title: "Test",
          Users: 
           [
             {name: "user1"},
             {name: "user2"}
           ]
        }
       ]
    }

If that is not possible then this would be great as well:

    "Response":
    [
      {
         ID: 1,
         Title: "Test",
         Users: "user1"
      },

      {
         ID: 1,
         Title: "Test",
         Users: "user2"
      }
    ]

Answer 1

You can return your own format with anonymous Object.

Please See this:

public HttpResponseMessage Get()
{
    return this.Request.CreateResponse(
        HttpStatusCode.OK,
        new { Message = "Hello", Value = 123 });
} 

Answer 2

To expand upon @Shahrooz Jefri's answer, you can return an HttpResponseMessage which will allow you to return an anonymous object, but to get the result formatted as required, you will need to modify the Get_Record method:

public HttpResponseMessage Get_Record(string id)
{
    // Get DataTable
    DataTable dt = new DataTable();
    dt = db.GetRecord(id);

    var records = dt.Rows.Select(r => new Record()
    {
        ID = row.Field("ID"),
        Title = row.Field("Title"),
        Users = row.Field("Users")
    });

    return this.Request.CreateResponse(HttpStatusCode.OK,
        new
        {
            Response = new
            {
                ID = records.First().ID,
                Title = records.First().Title,
                Users = records.Select(r => r.Users)
            }
        });
}

With this approach, we first transform the results from a call to db.GetRecord into an IEnumerable<Record> of your your Record object.

From there, we can shape an anonymous object to match your formatting requirements. Note that in my answer I have not applied any error checking; you should, as the case where db.GetRecord returns no results will cause an exception when we populate the ID and Title fields of the response object due to calls to records.First().

Answer 3

Well, What I have experienced so far, creating a generic response will be the best practice for your case. For instance, you may have a class something like this.

public enum Status
{
    OK = 1,
    ERROR = -1,
}

public class ResponseDTO
{
    public static ResponseDTO CreateDynamicResponse(object content)
    {
        return new ResponseDTO {Status = Status.OK, Content = content};
    }

    public static ResponseDTO CreateDynamicResponseFromException(RovlerBaseException ex)
    {
        return new ResponseDTO
        {
            Status = Status.ERROR,
            Message = ex.Message,
        };
    }

    [JsonProperty(NullValueHandling = NullValueHandling.Ignore)] 
    public Status Status;

    [JsonProperty(NullValueHandling = NullValueHandling.Ignore)]
    public object Content { get; set; }

    [JsonProperty(NullValueHandling = NullValueHandling.Ignore)]
    public string Message { get; set; }
}

After the execution of action I create an ActionFilterAttribute in order to check if there is an error on response that action just returned. Then I user CreateDynamicResponse or CreateDynamicResponseFromException with respect to the status of returned object.