Get a single record from SQL Server the correct way

Question

I'm using Ado to retrieve a single record by id. Observe:

public async Task<Image> GetImage(int id)
{
    var image = new Image();

    using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        conn.Open();

        string sql = @" SELECT * FROM Images where id = @id";

        using (SqlCommand comm = new SqlCommand(sql, conn))
        {
            comm.Parameters.AddWithValue("@id", id);

            var reader = await comm.ExecuteReaderAsync();

            int ordId = reader.GetOrdinal("id");
            int ordName = reader.GetOrdinal("name");
            int ordPath = reader.GetOrdinal("path");

            while (reader.Read())
            {
                image.Id = reader.GetInt32(ordId);
                image.Name = reader.GetString(ordName);
                image.Path = reader.GetString(ordPath);
            }

            return image;
        }
    }
}

As you can see I am using While to iterate through the records. Since while is signifying there may be more than one record to iterate I believe this may be the wrong way to get get a single record. Considering ADO has ExecuteScalar for one row one field maybe they have a specified way for one row multiple fields. Is there a specified way to get a single record in ADO?

Answer 1

What if you just read once:

using (SqlConnection conn = new SqlConnection(ConnectionString))
{
    conn.Open();

    string sql = @" SELECT id, name, path FROM Images where id = @id";

    using (SqlCommand comm = new SqlCommand(sql, conn))
    {
        comm.Parameters.AddWithValue("@id", id);           

        using (var reader = await comm.ExecuteReaderAsync())
        {
            if (!reader.Read())
                 throw new Exception("Something is very wrong");

            int ordId = reader.GetOrdinal("id");
            int ordName = reader.GetOrdinal("name");
            int ordPath = reader.GetOrdinal("path");

            image.Id = reader.GetInt32(ordId);
            image.Name = reader.GetString(ordName);
            image.Path = reader.GetString(ordPath);

            return image;
        }
    }
}

P.S.: I have also changed select statement to select only required fields and wrapped reader in using statement.