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

Answer 1

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

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