How best to loop over a batch of Results with a C# DbDataReader

Question

I'm executing a number of SQL query's as a batch and then getting all of the result sets back in a batch. The way that my code is currently put together, the first result set gets skipped. Now that I know this, I could simply throw in another statement outside of my loop that grabs the first result, however I'm wondering if there is a more elegant solution to this problem.

Here is some sudo code of whats going on:

DbDataReader reader= /*some stuff that returns a batch of results...*/;

while (reader.NextResult())
{
   while (reader.Read())
   {
       if (!reader.IsDBNull(0))
       {
           //do things with the data....
       }
   }
}

Now i would have expected that NextResult() advances you to the first result the first time you call it, which is what Read() seems to do. However what it actually seems to do is bring you to the second result on the first call. Am I misunderstanding how you're expected to use this method, or are you really expected to do some like the following:

DbDataReader reader= /*some stuff that returns a batch of results...*/;

//this deals with the row in the the very first result
while (reader.Read())
{
    if (!reader.IsDBNull(0))
    {
        //do things with the data....
    }
}

//this deals with the rest of the rows...
while (reader.NextResult())
{
   while (reader.Read())
   {
       if (!reader.IsDBNull(0))
       {
           //do exact same things with the data....
           //is this not pretty klugey?
       }
   }
}

This strikes me as rotten programming style, but I don't see a way around it. Does anyone know of a more elegant solution to this?

score 9 · Accepted Answer · answered Apr 02 '09 at 16:36

9

Simply put the NextResult at the end of the loop instead of the beginning:

do {
   while (reader.Read()) {
      if (!reader.IsDBNull(0)) {
         //do things with the data....
      }
   }
} while (reader.NextResult());

answered Apr 02 '09 at 16:36

Guffa

687,336
108
737
1,005

I'm still wondering whether sweeney shouldn't go deeper in thinking about this one though. I can't think of too many situations where I'd pull multiple data sets and do the exact same thing with them. – Mark Brittingham Apr 02 '09 at 16:39
anyhow, i implemented your do while loop and it runs like w/o error so thanks! – Brian Sweeney Apr 02 '09 at 16:47
@MarkBrittingham One is enough. I do it often. – paparazzo Jan 11 '15 at 13:42

score 3 · Answer 2 · answered Apr 02 '09 at 16:35

3

In the great majority of cases, you will only be returning a single result set with any given call so it would not make sense for the designers to have required a "NextResultSet" every time you use a reader.

Thus, your second example does indeed hold if you are pulling multiple result sets. The other thing that your post makes me wonder, though, is why you'd be doing the "exact same things with the data" if you are retrieving multiple result sets - wouldn't the structure of the data be different enough that you wouldn't be doing the exact same things?

That is, your example makes me wonder if you don't have some kind of bug in your thinking about how the data management functions work.

answered Apr 02 '09 at 16:35

Mark Brittingham

28,545
12
80
110

Not necessarily: I have a generic procedure that connects to our db, runs some queries from a file, and knows how to output the results to a different file, in a very generic way regardless of the format for the results. – Joel Coehoorn Apr 02 '09 at 16:38
point taken, mark but in my case there is enough metadata in the results that i pull that i can decide what im doing with them on the fly. thats more or the less the contents of the inner most loop which i omitted. – Brian Sweeney Apr 02 '09 at 16:39
also, i have to disagree with that design decision, in JDBC drivers the nextResult method behaves in the same fashion as the read() method, which to me is more intuitive. – Brian Sweeney Apr 02 '09 at 16:40
Ok - sounds fair. I don't know how experienced you are and sometimes you have to help people by seeing past their immediate problem and into the *why* they are taking the approach that they are. Doesn't sound like you need that though. – Mark Brittingham Apr 02 '09 at 16:41
Yea, i'm kinda green to be fair, and I do understand your concern. However the code runs acceptably fast despite the inefficiency of that batch of query's and it allows for a certain flexibility in design where I can readily add more querys and deal with them all automatically with no extra coding. – Brian Sweeney Apr 02 '09 at 16:46

score 0 · Answer 3 · answered Apr 02 '09 at 16:37

0

I usually do this:

if(reader.HasRows)
    {
      while(reader.Read())
       {

          // Do Stuff
       }
    }

Hope it helps

answered Apr 02 '09 at 16:37

MRFerocius

5,509
7
39
47

You're misunderstanding the problem, I think. The question was not whether or not the reader had rows, but whether or not it had numerous result sets which you can test with NextResult(). Then you test each result set for numerous rows with Read(). – Brian Sweeney Apr 02 '09 at 17:02

How best to loop over a batch of Results with a C# DbDataReader

3 Answers3