Get one record with SQL Server correctly

Question

I use Ado to retrieve a single record by id. Note:

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 use While to repeat entries. Since while it means that there can be more than one record for repetition, I believe that this may be the wrong way to get one record. Given that ADO has an ExecuteScalar for a single row, a single field may have a specific path for multiple lines of multiple fields. Is there a way to get one record in ADO?

Answer 1

I would go with your current approach, except that I would exclude the while loop. If you want only one entry to be returned, do an additional Read to make sure it returns false. This is similar to the semantics of the LINQ Single statement.

 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 the primary key (unique), there is no need to specify the TOP clause in the SQL query; SQL Server Query Optimizer reports that only one record is returned due to a WHERE . However, if you do not have a primary key or a unique index / constraint in the id column, you should issue a TOP (2) clause to limit the number of rows returned. You should avoid using TOP (1) because you will not be able to detect (and raise the error) additional matches.

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

Answer 2

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

PS: I also changed the select statement to select only required fields and a wrapped reader in using the operator.

Answer 3

You can use Top(1) in this case in your query to get only one record from the database:

 SELECT Top(1) * FROM Images where id = @id order by id desc -- will get the latest record