Difference Between SQLdatareader.Read and SQLdatareader.Nextresult

Difference between SqlDataReader.Read and SqlDataReader.NextResult

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

C# SqlDataReader interface with HasRows, Read() and NextResult()

HasRows is not needed for anything (usually it's being used redundantly because people don't know any better). The standard pattern is:

while (reader.Read()) ...

So this obviates the need for the interface you are looking for.

Error while using NextResult fuction with datareader

In this context I would simply use an SqlDataAdapter to make one single call and fill all your tables

using (SqlConnection myCon = DBCon)
{
    try
    {
       string Qry =  @"SELECT [OPSProcedure],[OPSInsertedOn],[OPSInsertedBy]
                       FROM [Operation] where OPSID =  @id;
                       SELECT  LKCPID  FROM dbo.ConcurrentProcedure  
                       where CPOperationID = @id;
                       SELECT IOperaitonID FROM dbo.LkupIntraOperativeAdverseEvents   
                       where IOperaitonID = @id";
        myCon.Open();
        SqlDataAdapter da = new SqlDataAdapter(Qry, myCon);
        da.SelectCommand.Parameter.Add("@id", SqlDbType.NVarChar).Value = opID;
        DataSet ds = new DataSet();
        da.Fill(ds);

        // Test...
        Console.WriteLine(ds.Tables[0].Rows.Count);
        Console.WriteLine(ds.Tables[1].Rows.Count);
        Console.WriteLine(ds.Tables[2].Rows.Count);

Notice also that you should never concatenate strings to build sql commands. Always use parameters.

How to read multiple resultset from SqlDataReader?

Here you have a sample about how to handle multiple result sets with a data reader

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

        do
        {
            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));
            }               
        }
        while (reader.NextResult());
    }
}

The key for retrieving data from multiple data sets is using reader.NextResult

Multiples Table in DataReader

Try this because this will close connection ,data reader and command once task get over , so that this will not give datareader close exception

Also do check like this if(reader.NextResult()) to check there is next result,

using (SqlConnection connection = new SqlConnection("connection string here"))
{
    using (SqlCommand command = new SqlCommand
           ("SELECT Column1 FROM Table1; SELECT Column2 FROM Table2", connection))
    {
        connection.Open(); 
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                MessageBox.Show(reader.GetString(0), "Table1.Column1");
            }

            if(reader.NextResult())
            {
               while (reader.Read())
              {
                MessageBox.Show(reader.GetString(0), "Table2.Column2");
              }
            }
        }
    }
}

Why is no data found after calling SqlDataReader.NextResult?

NextResult() makes the reader advance to the next result set coming back from the query. The way you have it written, it would skip the first result set (likely the only one).

The pattern I think you want is:

if (reader.HasRows)
{
     do
     {
        while (reader.Read())
        {
             ...
        }
     }
     while (reader.NextResult());
}

This will check if there are any results, and if so, read the results in each result set until there are no more left to read.

EDIT: Based on comment:

For JSON, consider using a list of temporary objects, then a DataContractJsonSerializer:

 public class DateClass
 {
      public string AvgDate { get; set; }
      public int MarkerID { get; set; }
 }

 ...

 var dates = new List<DateClass>();
 if (reader.HasRows)
 {
       while (reader.Read())
       {
           var date = new DateClass { AvgDate = reader["AvgDate"].ToString(), MarkerID = (int)reader["MarkerID"] };
            dates.Add( date );
       }
 }

 var stream = new MemoryStream();
 var serializer = new DataContractJsonSerializer( typeof(DateClass) );
 serializer.WriteObject( stream, dates );
 stream.Seek( 0, SeekOrigin.Begin );
 return stream.ToString();

Read SqlDataReader after Exception -- LIKE SSMS Result Pane

To avoid throwing a SqlException and continue executing the batch after errors, you can set the connection FireInfoMessageEventOnUserErrors property to true and handle the connection InfoMessage event. The Infomessage handler will be then be invoked instead of throwing a SqlException and you can continue processing SqlDataReader results using Read and NextResult.

---

Related Topics