Json from DataTable returns single element as an array

Question

I'm trying to create an api endpoint that returns an element by id , but JsonResult makes an array from DataTable which is supposed to have only one object

[HttpGet("{id:int}")]
    public JsonResult GetUser(int id)
    {
        string query = @"select id,number,name,lastName from dbo.Users where id=" + id;
        DataTable table = new DataTable();
        string sqlDataSource = _configuration.GetConnectionString("UsersDb");
        SqlDataReader myreader;
        using (SqlConnection myCon = new SqlConnection(sqlDataSource))
        {
            myCon.Open();
            using (SqlCommand myComm = new SqlCommand(query, myCon))
            {
                myreader = myComm.ExecuteReader();
                table.Load(myreader);
                myreader.Close();
                myCon.Close();
            }
        }
        return new JsonResult(table);
    }    

However i get this result with squared brackets on the sides

[{"id":4,"number":10,"name":"Peter","lastName":"Peterson"}]

Answer 1

try this

return new JsonResult(JArray.FromObject(dt).First())

result

{"id":4,"number":10,"name":"Peter","lastName":"Peterson"}

Answer 2

You can just return table.Rows[0] rather than the whole table.

There are other improvements here:

• Parameterize your query. Do not inject data into your SQL.
• Missing using on the reader.
• Consider making this function async

    [HttpGet("{id:int}")]
    public JsonResult GetUser(int id)
    {
        const string query = @"#
    select
      id,
      number,
      name,
      lastName
    from dbo.Users
    where id = @id;
    ";
        DataTable table = new DataTable();
        string sqlDataSource = _configuration.GetConnectionString("UsersDb");
        using (SqlConnection myCon = new SqlConnection(sqlDataSource))
        using (SqlCommand myComm = new SqlCommand(query, myCon))
        {
            myComm.Parameters.Add("@id", SqlDbType.Int).Value = id;
            myCon.Open();
            using(var myreader = myComm.ExecuteReader())
            {
                table.Load(myreader);
            }
        }
        return new JsonResult(table.Rows[0]);
    }