How to get more than one record using SqlDataReader?

Question

Here is my web method I want to add all the meetings in my allmeeting list then I want to return it and receive through kendo grid

List<DefMeetingDTO> AllDefCompany = new List<DefMeetingDTO>();
List<DefMeetingDTO> f = new List<DefMeetingDTO>();

SqlDataReader reader = cmd.ExecuteReader();

if (reader.HasRows)
{
    DefMeetingDTO d = new DefMeetingDTO();
    int ji = reader.FieldCount;

    do
    {
         while (reader.Read())
         {
              //for(int i = 0 ; i < ji ; i++) {
              if (!reader.IsDBNull(reader.GetOrdinal("md_id")))
              {
                   d.md_id = Convert.ToInt32(reader["md_id"]) as int? ?? default(int);
              }
              else
              {
                   d.md_id = 0;
              }

              if (!reader.IsDBNull(reader.GetOrdinal("md_visitor_name")))
              {
                   d.md_visitor_name = (string)(reader["md_visitor_name"]);// as DateTime? ?? default(DateTime);
              }
              else
              {
                   // d.m_datetime = null;
              }

              if (!reader.IsDBNull(reader.GetOrdinal("md_visitor_cell")))
              {
                   d.md_visitor_cell = (reader["md_visitor_cell"]).ToString();// as string? ?? default(string);
              }

              if (!reader.IsDBNull(reader.GetOrdinal("md_visitor_company")))
              {
                   d.md_visitor_company = (string)reader["md_visitor_company"];
              }

              if (!reader.IsDBNull(reader.GetOrdinal("purpose_name")))
              {
                   d.purpose_name = (string)reader["purpose_name"];
              }

              if (!reader.IsDBNull(reader.GetOrdinal("m_datetime")))
              {
                   d.m_datetime = Convert.ToDateTime(reader["m_datetime"]) as DateTime? ?? default(DateTime);
              }

              AllDefCompany.Add(d);
          }
     }

     while(reader.NextResult());                        
}

My SQL query return result as checked 4 rows

   10878    |   Wasim Riaz  | 0300449436     | Jade | N/A   | NULL
   71123    |   bb          | +9232531256    | F    | mee   | 2015-09-03
   71124    |   CC          | +923218531256  | Fb   | N/A   | 2015-09-03
   71125    |   DD          | +923218531256  | Gb   | N/A   | 2015-09-03

But the SqlDataReader iterates every time to 1st row only and does not go to the second to add in allmeetinglist() and connection times out

Answer 1

I am not sure why you are using Do-While loop. You can simply use a While loop like this:-

List<DefMeetingDTO>   AllDefCompany = new List<DefMeetingDTO>();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
    DefMeetingDTO d = new DefMeetingDTO();
    if (!reader.IsDBNull(reader.GetOrdinal("md_id")))
    {
          d.md_id = Convert.ToInt32(reader["md_id"]) as int? ?? default(int);
    }
    else
    {
          d.md_id = 0;
    }
    AllDefCompany.Add(d);
}

Please note, Read() will anyways advance the datareader to next record, so no point doing it again with NextResult. Also, note it is not necessary to check reader.HasRows since Read will return false if there are no more rows to fetch.

Answer 2

I think you are executing ** batch Transact-SQL statements** on datareader.try the below code

  do
    {
       if (reader.HasRows)
         {
             while (reader.Read())
            {
               DefMeetingDTO d = new DefMeetingDTO();
              if (!reader.IsDBNull(reader.GetOrdinal("md_id")))
              {
                   d.md_id = Convert.ToInt32(reader["md_id"]) as int? ?? default(int);
              }
              else
              {
                   d.md_id = 0;
              }
              AllDefCompany.Add(d);
           }
        }
  }
 while(reader.NextResult());