SqlDataReader returns only one row

Question

I am using a SqlDataReader to fetch data from a stored procedure. Even though the records are being fetched, the while (reader.Read()) gets executed only once, and so in my list only one row is added.

List<Student> tablelist = new List<Student>();

using (SqlConnection con = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand("SP_ReadPromotedStudents"))
    {
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = Data[0];
        cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = Data[1];
        cmd.Parameters.Add("@Class", SqlDbType.VarChar).Value = Data[2];

        con.Open();

        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.HasRows)
            {
                 while (reader.Read())
                 {
                     tablelist.Add(new Student
                                    {
                                        Name = (string)(reader[0]),
                                        Email = (string)(reader[1]),
                                        Class = (string)(reader[2]),
                                    });
                     reader.NextResult();
                 }
             }
         }
     }
}

return tablelist;

My Student class:

public class Student
{
    public string Name { get; set; }
    public string Email { get; set; }
    public string Class { get; set; }
}

I have about 46 records being fetched. But in the list only one record gets added. What is the mistake here?

NextResult() moves to the next result set, not the next row. Take that out. — Crowcoder
– Crowcoder, Commented Jun 8, 2020 at 17:27
Without NextResult(), it is going into infinte while loop. It is stuck at while (reader.Read()) — Cherylaksh
– Cherylaksh, Commented Jun 8, 2020 at 17:33
You should not loop while reader HasRows, you should use if not while. — Crowcoder
– Crowcoder, Commented Jun 8, 2020 at 18:46

marc_s · Accepted Answer · 2020-06-08 19:45:33Z

2

You need to move your call to NextResult outside the reader.Read() loop. Otherwise after the first read the code encounters the NextResult call and tries to load a second sets of data returned by the stored procedure.

Also the loop over HasRows is an infinite loop. If the property reader.HasRows is true it will be true also when you finish to read the rows.

using (SqlDataReader reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        tablelist.Add(new Student
        {
            Name = (string)(reader[0]),
            Email = (string)(reader[1]),
            Class = (string)(reader[2]),
        });
    }

    // This should be called only if your stored procedure returns 
    // two or more sets of data otherwise you can remove everything
    reader.NextResult();

    // If there is another set of data then you can read it with a
    // second while loop with
    while(reader.Read())
    {
        .....
    }
}

edited Jun 8, 2020 at 19:45

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jun 8, 2020 at 17:29

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shai Cohen Over a year ago

if(!reader.HasRows()) return; should be added as a cutout after the using block. You will release resources significantly sooner and prevent inevitable NullReferenceException errors downstream

Felipe Oriani · Accepted Answer · 2020-06-08 17:29:14Z

The ideal scenario would to have a new sql statement to get just what you want instead the get a list and need just the first access. Imagine if you have a table with millions of records, would you need to execute a query to get all and read just the first one? No, you execute a query to get the you need.

The NextResult method from DataReader moves the pointer to the next result if you have it on the result. Remove it.

After you chanfge the sql statement to get what you need, you are looping the result set. You could read just the first line (changing the while to if):

if (reader.Read())
{
   tablelist.Add(new Student
   {
     Name = (string)(reader[0]),
     Email = (string)(reader[1]),
     Class = (string)(reader[2]),
   });
}

Collectives™ on Stack Overflow

SqlDataReader returns only one row

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related