Difference between SqlDataReader.Read and SqlDataReader.NextResult

ado.net, c#, sql

Solution

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("\t{0}\t{1}", reader.GetName(0),
                reader.GetName(1));

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

Problem

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

Original source