c# - Difference between SqlDataReader.Read and SqlDataReader.NextResult

Question

What is the main difference between these two methods? On the msdn website it is explained like below but I don't understand it.

Read Advances the SqlDataReader to the next record. (Overrides DbDataReader.Read().)

NextResult Advances the data reader to the next result, when reading the results of batch Transact-SQL statements. (Overrides dbDataReader.NextResult().)

Answer 1

If your statement/proc is returning multiple result sets, For example, if you have two select statements in single Command object, then you will get back two result sets.

• NextResult is used to move between result sets.
• Read is used to move forward in records of a single result set.

Consider the following example:

If you have a proc whose main body is like:

.... Proc start

SELECT Name,Address FROM Table1

SELECT ID,Department FROM Table2

-- Proc End

Executing the above proc would produce two result sets. One for Table1 or first select statement and other for the next select statement.

By default first result set would be available for Read. If you want to move to second result set, you will need NextResult.

See: Retrieving Data Using a DataReader

Example Code from the same link: Retrieving Multiple Result Sets using NextResult

static void RetrieveMultipleResults(SqlConnection connection)
{
    using (connection)
    {
        SqlCommand command = new SqlCommand(
          "SELECT CategoryID, CategoryName FROM dbo.Categories;" +
          "SELECT EmployeeID, LastName FROM dbo.Employees",
          connection);
        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        while (reader.HasRows)
        {
            Console.WriteLine("{0}{1}", reader.GetName(0),
                reader.GetName(1));

            while (reader.Read())
            {
                Console.WriteLine("{0}{1}", reader.GetInt32(0),
                    reader.GetString(1));
            }
            reader.NextResult();
        }
    }
}