Web API to connect to SQL Server and return response in JSON

Question

I am trying to create a Web API that queries the SQL Server and returns the response in a JSON. Below is what I am trying

 [HttpGet]
    public HttpResponseMessage Getdetails(string ROOM)
    {
        if (string.IsNullOrEmpty(ROOM))
        {
            return Request.CreateResponse(new { error = "Input paramete cannot be Empty or NULL" });
        }

       string commandText = "SELECT * from [TDB].[dbo].[results_vw] where ROOM = @ROOM_Data";
        string connStr = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
        var jsonResult = new StringBuilder();
        using (SqlConnection connection = new SqlConnection(connStr))
        {
            SqlCommand command = new SqlCommand(commandText, connection);
            command.Parameters.Add("@ROOM_Data", SqlDbType.VarChar);
            command.Parameters["@ROOM_Data"].Value = ROOM;
            connection.Open();
            var reader = command.ExecuteReader();
            if (!reader.HasRows)
            {
                jsonResult.Append("[]");
            }
            else
            {
                while (reader.Read())
                {
                    jsonResult.Append(reader.GetValue(0).ToString());
                }
            }
            var response = new HttpResponseMessage(System.Net.HttpStatusCode.OK);
            response.Content = new StringContent(jsonResult.ToString());
            return ResponseMessage(response);
        }

But looks like the return type ResponseMessage is not matching the HttpResponseMessage how can I connect the SQL server qand return the query response in JSON.

ResponseMessage returns IHttpActionResult, so either update function result or don't use ResponseMessage — Nkosi
– Nkosi, Commented Oct 10, 2018 at 15:09

Nkosi · Accepted Answer · 2018-10-10 15:11:07Z

2

ResponseMessage returns IHttpActionResult derived ResponseMessageResult,

ResponseMessageResult ResponseMessage(HttpResponseMessage response);

so either update function result accordingly

 public IHttpActionResult Getdetails(string ROOM)

or don't use ResponseMessage

return response;

answered Oct 10, 2018 at 15:11

Nkosi

249k38 gold badges472 silver badges506 bronze badges

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

Comments

Angel Dinev · Accepted Answer · 2018-10-10 15:23:37Z

0

You may use

return Request.CreateResponse(jsonResult.ToString());

which produces HttpResponseMessage object with 200(OK) Http status code + provided data object as a content.

answered Oct 10, 2018 at 15:23

Angel Dinev

5095 silver badges15 bronze badges

Collectives™ on Stack Overflow

Web API to connect to SQL Server and return response in JSON

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related