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; }
}

Answer 1

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.