CODEWITHSUNDEEP
c#sql-serversqldatareaderdatareader
Cherylaksh
Cherylaksh

Reputation: 300

SqlDataReader returns only one row

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?

Upvotes: 0

Views: 3790

Answers (2)

Steve
Steve

Reputation: 216293

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

Upvotes: 2

Felipe Oriani
Felipe Oriani

Reputation: 38608

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]),
   });
}

Upvotes: 0

Related Questions