How to create a json in API?

Question

My first API and my first JSON

I want to convert results from SQL Query

I want to put the results in a JSON so when users call the API they get a JSON text to consume.

I found this question here that help

How to use SqlCommand and SqlDataReader to return a Json result in C#

but when I try to apply the same in 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 System.Web.Mvc;

namespace CDAPIs.Controllers
{
    public class RepsSelect : ApiController
    {
        string gDBConn = "Data Source=Server;Database=DB1;User Id=user;Password=*******;";

        [Route("api/RepsSelect/{RepID}/{Tag}")]
        public string Get(string RepID, string Tag)
        {
            SqlConnection conn = new SqlConnection(gDBConn);
            SqlCommand cmd = new SqlCommand("RepsSelect", conn);
            cmd.CommandTimeout = 0;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@RepID", RepID));
            cmd.Parameters.Add(new SqlParameter("@Tag", ""));

            try
            {
                conn.Open();

                SqlDataReader rdr = cmd.ExecuteReader();

                var dt = new DataTable();
                dt.Load(rdr);
                List<DataRow> result = dt.AsEnumerable().ToList();
                rdr.Close();

                // Error is here
                return Json(result, JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                var error = ex.Message;
                return View(error);
            }
        }
    }
}

I get these errors

CS1503 : Argument 2: cannot convert from "System.Web.Mvc.JsonRequestBehavior" to "Newtonsoft.Json.JsonSerializedSettings"

I tried using Newtonsoft.Json instead of System.Web.Mvc but I got this error

Never paste the error messages as images. If you fix it I'll reverse the vote. — Jeremy Thompson
– Jeremy Thompson, Commented May 10, 2021 at 5:28
@JeremyThompson the error message itself is already above the image!! — asmgx
– asmgx, Commented May 10, 2021 at 5:54

MikeSmith · Accepted Answer · 2021-05-10 02:56:30Z

2

When working with APIs - we want to work with the http response protocol (best practice).

Try changing this

public HttpResponseMessage Get(string RepID, string Tag)

and then

return Request.CreateResponse(HttpStatusCode.OK, result, Configuration.Formatters.JsonFormatter);

In the catch block you will want to return

Request.CreateErrorResponse(HttpStatusCode.NotFound, "Data Not Found");

Kia Kaha asmgx,

Mike Smith

edited May 10, 2021 at 2:56

answered May 10, 2021 at 2:38

MikeSmith

3892 silver badges12 bronze badges

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

2 Comments

Brett Caswell Over a year ago

can you validate your claim of best practice in any way?

MikeSmith Over a year ago

Good question. Generally from the API you want to handle error responses as well as your success responses with the appropriate HTTP result from the API - such as (200 Ok), (404 Not Found) etc. You could return a string/json as the return type but I personally would avoid that as I prefer to inform the client of the correct response type. Cheers

Son0nline · Accepted Answer · 2021-05-10 02:33:10Z

2

you need to change

    [Route("api/RepsSelect/{RepID}/{Tag}")]
    public string Get(string RepID, string Tag)

to

    [Route("api/RepsSelect/{RepID}/{Tag}")]
    public JsonResult Get(string RepID, string Tag)

answered May 10, 2021 at 2:33

Son0nline

512 silver badges5 bronze badges

1 Comment

Brett Caswell Over a year ago

why? why do this as oppose to serializing in the method and not modifying the return method signature? i.e. why is it necessary to make that change?

Collectives™ on Stack Overflow

How to create a json in API?

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related