c# - Get a single record from SQL Server the correct way -

i'm using ado retrieve single record id. observe:

public async task<image> getimage(int id) {     var image = new image();      using (sqlconnection conn = new sqlconnection(connectionstring))     {         conn.open();          string sql = @" select * images 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 can see using while iterate through records. since while signifying there may more 1 record iterate believe may wrong way get single record. considering ado has executescalar 1 row 1 field maybe have specified way 1 row multiple fields. there specified way single record in ado?

i go current approach, except i'd eliminate while loop. if want ensure 1 record returned, perform read ensure returns false. similar semantics of linq single operator.

if (!reader.read())             throw new invalidoperationexception("no records returned.");  image.id = reader.getint32(ordid); image.name = reader.getstring(ordname); image.path = reader.getstring(ordpath);  if (reader.read())     throw new invalidoperationexception("multiple records returned."); 

assuming id column in database primary key (unique), there no need specify top clause in sql query; sql server query optimizer deduce @ 1 record returned due where clause. however, if don't have primary key or unique index/constraint on id column, should issue top (2) clause restrict number of returned rows. should avoid using top (1) because unable detect (and raise error for) matches.

string sql = @"select top (2) * images id = @id"