stored procedure returns multiple resultsets but number of resultsets are not fixed

I have a stored procedure which returns variable number of mul开发者_StackOverflow中文版tiple resultsets. DataReader.NextResult() gives error if no next resultset exists . How to find whether next resultsets exists or not.

The NextResult() method returns true if there are more result sets - check that before making your next read

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.nextresult.aspx

(I know this is an old post, but hopefully this is helpful to someone!)

You can do something like the following if you need to process an unknown number of result sets:

// Need to wrap the while loop in a do-while loop due to the way Read() works versus NextResult().
// Read() moves to the next record, if any, starting with the first record.
// NextResult() moves to the next result set, if any, starting with the second result set (i.e., first result set is used automatically).
do
{
    while (mySqlDataReader.Read())
    {
        // Do some processing here...for example:

        var rowValues = new object[mySqlDataReader.FieldCount];
        mySqlDataReader.GetValues(rowValues);

        foreach (var element in rowValues)
        {
            myStringBuilder.Append(element).Append(" | ");
        }

        myStringBuilder.AppendLine();
    }
}
while (mySqlDataReader.NextResult());