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?

Maybe if you have the control over the query, you can do a `TOP 1` in it... If you want to check for the integrity of your database, maybe you should put a `UNIQUE INDEX` against the columns of your `WHERE` of you query instead of doing this. — Mike Gleason jr Couturier, Oct 20 '11 at 13:21
I don't want a TOP 1, I want an exception. They are not the same thing at all. — fearofawhackplanet, Oct 20 '11 at 13:30

score 24 · Accepted Answer · answered Oct 20 '11 at 13:25

24

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.

answered Oct 20 '11 at 13:25

flq

22,247
8
55
77

We could avoid even more duplicated code by making an extension method for `SqlCommand`. `User u = cmd.Single(...);` – Chris Shouts Oct 20 '11 at 13:33
Sure, on that low-level API, with a couple of extension methods you can shape it to be quite usable stuff :) – flq Oct 20 '11 at 13:36
I don't like the extension on `SqlCommand` so much because it does not implement any useful interface. – fearofawhackplanet Oct 21 '11 at 09:54

score 17 · Answer 2 · answered Jan 28 '14 at 23:38

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

score 1 · Answer 3 · answered Oct 20 '11 at 13:30

If you are using a sql to fetch your'e data this might help by letting you remove that kind of coding in every instance that you need to use a data reader.

SELECT TOP ([Number of rows you want to be selected]) 
FROM [Table Name] 
WHERE [Condition]

EX:

SELECT TOP (1)
FROM tblUsers
WHERE Username = 'Allan Chua'

Another tip use stored procedures, Using them could minimize the repetition of SQL query and unnecessary coding.

Enforce only single row returned from DataReader

3 Answers3

Linked