How to query a SQL Server table in C# which results only in one single row?

Question

I want to query a SQL Server table like:

SELECT * 
FROM TABLE1 
WHERE REC_ID = 1

I know that this query can only return one single record by design. Normally I would do:

SqlCommand cmd = new SqlCommand(sqlquery, con);
SqlDataReader reader = cmd.ExecuteReader();

if (reader.HasRows)
{
  while (reader.Read())
  {
    ct = new ctyp();
    ct.ID = Convert.ToInt32(reader["MyID"]);
    ct.Name = Convert.ToString(reader["Name"]);
  }
}

But in this case I have always need to use the reader.Read() method although I know that the returned count of records is only one.

Is there any other to handle queries or do a execute... which results in one object?

Answer 1

I agree with Jesús López in that using a micro ORM is the easiest approach. You can check out Dapper. Here's an example in your case...

using (var connection = new SqlConnection(ConnectionString))
{
    connection.Open();
    var record = connection.Query<ctyp>("SELECT MyID AS ID, Name FROM TABLE1 WHERE REC_ID = 1").FirstOrDefault();
}

record will be of type ctyp, and it will be null if there was no record returned from DB.