CODEWITHSUNDEEP
c#.netsqldatareader
cc0
cc0

Reputation: 1950

Why is no data found after calling SqlDataReader.NextResult?

My problem is that this does not work;

while (reader.Read())
{
   if (reader.NextResult() == true)
   {
      json.AppendFormat("{{\"AvgDate\": \"{0}\"}},{{\"MarkerID\": \"{1}\"}},", reader["AvgDate"], reader["MarkerID"]);
   }

But this works;

while (reader.Read())
{
    json.AppendFormat("{{\"AvgDate\": \"{0}\"}},{{\"MarkerID\": \"{1}\"}},", reader["AvgDate"], reader["MarkerID"]);
}

The problem with the first one is that the reader doesn't find any data to read. I get;

"Invalid attempt to read when no data is present."

Can anyone see why?

Upvotes: 3

Views: 7153

Answers (3)

Gerard ONeill
Gerard ONeill

Reputation: 4102

the code from tvanfosson's answer above:

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

should instead be:

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

There may be result sets without rows that would cause the previous code example to throw an exception. Worse, the first result set may have no rows, and the later result sets would then not be read.

But thanks for letting me know why and how HasRows is used.

Upvotes: 4

tvanfosson
tvanfosson

Reputation: 532505

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

Upvotes: 17

Jacob G
Jacob G

Reputation: 3665

NextResult takes you to the next Result Set from the Reader. You probably only have one Result Set.

Upvotes: 4

Related Questions