SqlDataReader problem

Question

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?

Answer 1

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

Answer 2

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