difference between NextResult and MARS

Question

What's the difference between using .NextResult and Multiple Active Result Sets?

sqlReader.NextResult();

while (sqlReader.Read())
{
    MessageBox.Show("From third SQL - " + 
                    sqlReader.GetValue(0) + " - " + 
                    sqlReader.GetValue(1));
}

MARS

private static string GetConnectionString()
{
    // To avoid storing the connection string in your code,
    // you can retrive it from a configuration file.
    return "Data Source=(local);Integrated Security=SSPI;" + 
           "Initial Catalog=AdventureWorks;MultipleActiveResultSets=True";
}

To what Damien wrote, I'll add that MARS is probably rarely used because it's the road to the `SELECT N+1` antipattern. In the end if the first result-set is small, you can read it in a collection and then do the second query while if it's too much big doing a `SELECT N+1` will kill you db. So the use cases for MARS are rare. — xanatos, Aug 15 '13 at 06:11

score 5 · Accepted Answer · answered Aug 15 '13 at 05:57

They're almost opposites.

With .NextResult, a single piece of code is consuming multiple result sets sequentially. It works through (as much of) each result set in turn by calling .Read, and then when it no longer wants to work on that result set, it calls .NextResult to move onto the next one. Once you've called .NextResult, there's no way to continue consuming the previous result set.

All of these result sets must have been requested as part of the original query that was submitted to generate the DataReader. (Either as separate SELECT statements, or calling a stored procedure that contains such calls, or a combination of the two)

With MARS, whilst you're consuming a result set through a DataReader, you can submit a separate query on the same connection that generates a new, separate DataReader. Both readers may be accessed in parallel.

+1 But they aren't opposite... They are probably orthogonal, even because with MARS you can even use multiple result sets with each `DataReader` — xanatos, Aug 15 '13 at 06:03
@xanatos - I was trying to decide what the best word was to describe them. You may be right that orthogonal is a better fit. — Damien_The_Unbeliever, Aug 15 '13 at 06:04

difference between NextResult and MARS

1 Answers1