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?

Answer 1

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.

Answer 2

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

Answer 3

I usually do this:

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

          // Do Stuff
       }
    }

Hope it helps