Web API to query SQL Server and return result is not working as expected

Question

I am trying to connect to SQL Server from the Web API and return a result set as JSON. But my code shown here is not working as expected. I am trying to return the entire query response as a JSON:

[HttpGet]
public HttpResponseMessage Getdetails(string ROOM)
{
    string commandText = "SELECT * from [TDB].[dbo].[results_vw] where ROOM = @ROOM_Data";

    string connStr = ConfigurationManager.ConnectionStrings["TDBConnection"].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());

        connection.Close();

        return response;
    }
}

This code returns this result:

333838362692368203368203368203362692368203359544362692368203362692368203362692368203368203

Where I am expecting the JSON as

 {"data":
  [
  {"R_ID":"368203","ROOM":"K2"}, 
  {"R_ID":"368203","ROOM":"K2"}
  ]}

Now I created a model class called DatabaseResult to store the response but I am not sure how I can store the result in to the model class in the controller

public class DatabaseResult
{
      public int r_id { get; set; }
      public string room { get; set; }
}

That is because you are only return the the value from the first column of each row — Nkosi
– Nkosi, Commented Oct 10, 2018 at 18:06
I would suggest using the Dapper library to help with your ADO calls. — Nkosi
– Nkosi, Commented Oct 10, 2018 at 18:09
@Nkosi can I just store the response using the Model class instead of using the extra library? — trx
– trx, Commented Oct 10, 2018 at 18:11
Yes. I was just suggesting it as it would make things easier. — Nkosi
– Nkosi, Commented Oct 10, 2018 at 18:11
@Nksoi I am not sure how to store them in the Model Class though — trx
– trx, Commented Oct 10, 2018 at 18:12

Nkosi · Accepted Answer · 2018-10-10 18:56:56Z

3

The current result is because you are only return the the value from the first column of each row and adding it to the string builder.

Create a new instance of the model and populate it using the values from the reader for each row.

[HttpGet]
public IHttpActionResult Getdetails(string ROOM) {
    string commandText = "SELECT * from [TDB].[dbo].[results_vw] where ROOM = @ROOM_Data";
    string connStr = ConfigurationManager.ConnectionStrings["TDBConnection"].ConnectionString;
    var jsonResult = new StringBuilder();
    using (SqlConnection connection = new SqlConnection(connStr)) {
        using (SqlCommand command = new SqlCommand(commandText, connection)) {
            command.Parameters.Add("@ROOM_Data", SqlDbType.VarChar);
            command.Parameters["@ROOM_Data"].Value = ROOM;
            connection.Open();
            List<DatabaseResult> records = new List<DatabaseResult>();
            using (var reader = command.ExecuteReader()) {
                while (reader.Read()) {
                    var row = new DatabaseResult {
                        r_id = (int)reader["r_id"],
                        room = (string)reader["room"],
                        //...other properties.
                    };
                    records.Add(row);
                }
                return Ok(records);
            }
        }
    }
}

The above uses the column names as the indexer to get the values from the reader.

edited Oct 10, 2018 at 18:56

answered Oct 10, 2018 at 18:21

Nkosi

249k38 gold badges472 silver badges506 bronze badges

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

6 Comments

trx Over a year ago

@Nksoi Thanks a ton. I am getting an error at room room = (string)reader["room"], saying it doesnot exists in context

Nkosi Over a year ago

@trx I made an assumption about column name. what is the column that K2 data could come from? The column names need to those from the table

trx Over a year ago

It was my mistake I had a semicolon next to r_id = (int)reader["r_id"], Now I have the issue with return Ok(records) as it says it doesnot exists with the correct context. Is it because I am using HttpResponseMessage as return type

Nkosi Over a year ago

@trx Yes. Look at the code in my answer and you will see the return type for the action. It simplifies what you had. under the hood it basically does the same thing. This way has a simpler syntax

trx Over a year ago

Thanks it worked. Just checking can we dump the response from the query in to model class with iterarting through each column names something like JsonConvert.DeserializeObject<DatabaseResult>(queryResponse)? because I get Unable to cast object of type 'System.DBNull' to type 'System.String'.

|

Collectives™ on Stack Overflow

Web API to query SQL Server and return result is not working as expected

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related