代码之家  ›  专栏  ›  技术社区  ›  trx

用于查询SQL Server并返回结果的Web API未按预期工作

  •  1
  • trx  · 技术社区  · 6 年前

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

    此代码返回以下结果:

    333838362692368203368203368203362692368203359544362692368203362692368203362692368203368203
    

    我希望JSON

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

    现在我创建了一个名为 DatabaseResult

    public class DatabaseResult
    {
          public int r_id { get; set; }
          public string room { get; set; }
    }
    
    1 回复  |  直到 6 年前
        1
  •  3
  •   Nkosi    6 年前

    当前结果是因为您只返回每行第一列的值并将其添加到字符串生成器。

    创建模型的一个新实例,并使用读卡器中每行的值填充它。

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

    上面使用列名作为索引器从读取器获取值。