Why might IDbCommand.ExecuteReader() fail when IDbCommand.ExecuteScalar() succeeds?

Question

I have some C# code that dynamically generates an SQL query and executes it via IDbCommand.ExecuteScalar(). This works fine; there's exactly one result that matches my query in the DB, and that result is always returned.

But just recently, as the first step in a refactoring to support multiple matches in the DB, I replaced the call to ExecuteScalar() with one to ExecuteReader(). Everything else in the setup and DB access is the same. But the returned IDataReader contains no data, and throws InvalidOperationExceptions whenever I try to get data out of it.

I know the data's still there; everything works fine when I switch back to ExecuteScalar(). How is this possible?

Answer 1

Is this to do with having multiple IDataReaders open on the same connection?

Because you wont get that issue with ExecuteScalar(), but once you start using ExecuteReader() you need to make sure all previous DataReaders on the same connection are closed (e.g. by using a 'using' block)

What is the error message that you get with the InvalidOperationException?

Answer 2

Make sure that you are calling the Read() method on the IDataReader that is returned by ExecuteReader() before trying to access it. Calling Read() will advance the reader onto the first (and in your case only) row of the resultset. If you do not call Read() before accessing the IDataReader, you will get an InvalidOperationException when you try to access its data - as you are experiencing.