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

I would go with your current approach, except that I'd eliminate the while loop. If you want to ensure that only one record is returned, perform an extra Read to ensure it returns false. This is similar to the semantics of the LINQ Single operator.

if (!reader.Read())        
    throw new InvalidOperationException("No records were returned.");

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

if (reader.Read())
    throw new InvalidOperationException("Multiple records were returned.");

Assuming that the id column in your database is a primary key (unique), there is no need to specify a TOP clause in the SQL query; the SQL Server query optimizer would deduce that only at most one record is returned due to the WHERE clause. However, if you don't have a primary key or unique index/constraint on the id column, then you should issue a TOP (2) clause to restrict the number of returned rows. You should avoid using TOP (1) because you would be unable to detect (and raise an error for) extra matches.

string sql = @"SELECT TOP (2) * FROM Images WHERE id = @id"