Enforce only single row returned from DataReader

Question

I seem to write this quite a lot in my code:

using (var reader = cmd.ExecuteReader())
{
    if (reader.Read())
    {
        result = new User((int)reader["UserId"], reader["UserName"].ToString());
    }

    if (reader.Read())
    {
        throw new DataException("multiple rows returned from query");
    }
}

Is there some built in way to do this that I don't know about?

Answer 1

I don't know, but this code can be delegated into an extension method:

public static R Single<R>(this DataReader reader, Func<DataReader,R> selector) {
    R result = default(R);
    if (reader.Read())
        result = selector(reader);
    if (reader.Read())
        throw new DataException("multiple rows returned from query");
    return result;
}

to be used like that:

using (var reader = cmd.ExecuteReader())
{
    User u = reader.Single(r => new User((int)r["UserId"], r["UserName"].ToString()))
}

Saving you from code duplication.

Answer 2

This may or may not help depending on what your goal is. If you need to detect that multiple rows were returned in order to throw an appropriate exception, then this won't help.

If you just want to make sure that only one result is returned, you can potentially get a performance bump by using this method. From what I understand, data providers can use this to optimize the query in anticipation of a single row result.

In any case, what you'll want to do is use SqlCommand.ExecuteReader to create your data reader, but pass in an argument from the CommandBehavior enumeration (specifically CommandBehavior.SingleRow). ExecuteReader is overloaded to accept this.

CommandBehavior enum

SqlCommand.ExecuteReader overload

So your code might look like this:

using (var reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
    if (reader.Read())
    {
        result = new User((int)reader["UserId"], reader["UserName"].ToString());
    }
}