SqlDataReader.Read() error, reading values from a stored procedure

Question

I am new to ASP.NET, and I am having issues with my API-controller function. I am calling a stored procedure and mapping the results to a model, then aggregating it into a list of Strings, but at the end of the code it returns an empty string. I noticed that execution skips the while block, and I'm not sure why, because when I set break points there it doesn't hit.

this is the output of the stored procedure when i run in in sqlserver

This is my code:

The UserModel

   using System;
   using System.ComponentModel.DataAnnotations;

   namespace Fnk.Models{
      public class UserModel{
        [Required]
        public string id {get; set;}
        [Required]
        public string Username{get; set;} 
        [Required]
        public string Email {get; set;} 
        [Required]
        public string UserRole {get; set;}
      }
   }

The Api-controller - receives an email

        public IActionResult getusers([FromRoute] string email){

            string conn = Configuration.GetConnectionString("FintrakDBConnection");         
            var columns = new List<string>();  // List<string> pTypes;

            using (var con = new SqlConnection(conn)) {
                var cmd = new SqlCommand("scb_getUserRoles", con);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;
                cmd.Parameters.Add(new SqlParameter {
                    ParameterName = "email",
                    Value = email,
                });

               con.Open();

               using(SqlDataReader reader = cmd.ExecuteReader()){   ///code works up to this point

                   while(reader.Read()){   // breakpoint here shows the 4 columns... 

                        var obj = new UserModel(); 

                        if (reader["id"] != DBNull.Value) 
                            obj.id = reader["id"].ToString(); 

                        if (reader["UserName"] != DBNull.Value)
                            obj.Username = reader["UserName"].ToString(); 

                        if (reader["Email"] != DBNull.Value)
                            obj.Email = reader["Email"].ToString(); 

                        if (reader["RoleId"] != DBNull.Value) 
                            obj.UserRole = reader["RoleId"].ToString();

                        columns.Add(obj.id);  
                        columns.Add(obj.Username);      
                        columns.Add(obj.UserRole);  
                        columns.Add(obj.Email);     
                    }
                    reader.Close();
               }                  
                con.Close();
            }
            return Ok(columns.ToArray());  //returns an empty array.. dont know why
          }

At this point while(reader.Read()) the system shows that it has read the database and updated the reader, but execution skips the while loop and returns an empty array.

Please, what am I doing wrong?

How does the sproc scb_getUserRoles looks like? Does it has output parameters or outputs a result set? — Julian
– Julian, Commented Feb 21, 2020 at 16:33
I don't know what the output of your SP looks like. Check this out maybe: stackoverflow.com/questions/26178032/… — Axelle
– Axelle, Commented Feb 21, 2020 at 16:44
i just updated the question, to show what the stored procedure looks like. — Ande Caleb
– Ande Caleb, Commented Feb 21, 2020 at 16:51
@OrlandoAguilera it works without the @ symbol, the query executes and gets to the while block, but if you insist, let me try so. — Ande Caleb
– Ande Caleb, Commented Feb 21, 2020 at 17:31

Md Farid Uddin Kiron · Accepted Answer · 2020-02-25 12:17:02Z

0

I have tested as per my table data which works fine.

    string connectionString = @"data source=MS-KIRON-01;initial catalog=MSTestDB;integrated security=True;MultipleActiveResultSets=True";
    var columns = new List<string>();  // List<string> pTypes;

    using (var con = new SqlConnection(connectionString))
    {
        var cmd = new SqlCommand("SELECT Username, Email FROM TestTable", con);

        con.Open();

        using (SqlDataReader reader = cmd.ExecuteReader())
        {   ///code works up to this point
            if (reader.HasRows)
            {
                while (reader.Read())
                {   // breakpoint here shows the 4 columns... 

                    var obj = new UserModel();

                    if (reader["Username"] != DBNull.Value)
                        obj.Username = reader["Username"].ToString();

                    if (reader["Email"] != DBNull.Value)
                        obj.Email = reader["Email"].ToString();
                    columns.Add(obj.Username);
                    columns.Add(obj.Email);
                }

            }

            reader.Close();
        }
        con.Close();
    }
    return columns.ToArray();

Everything seems alright I don't know why you are getting null array. One Modification I would suggest if (reader.HasRows) {} when your reader doesn't have any record it would through exception to handle this you could use it as I have already attached on my example.

edited Feb 25, 2020 at 12:17

answered Feb 21, 2020 at 17:20

Md Farid Uddin Kiron

22.9k3 gold badges29 silver badges54 bronze badges

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

8 Comments

Ande Caleb Over a year ago

sincerely... i've checked everything, let me try your approach and see..

Md Farid Uddin Kiron Over a year ago

Sure you can, let me know if anymore problem encountered, No worry. Good luck.

Ande Caleb Over a year ago

please i need help, on this same issue.. do you have team viewer maybe you could help me resolve this issue remotely, because i'm back to it.

Ande Caleb Over a year ago

no it didnt work, i moved on to solve other issues that was why.. but i'm back here, since its the same application, i tried to work on other modules, but now i want to handle the user module.. and i'm back to this.

Ande Caleb Over a year ago

you told me to use reader.HasRows - that returns false on my code, and the block of code refuses to execute, not sure why.

|

NeilNewman · Accepted Answer · 2020-02-21 16:59:09Z

0

Are you sure you are not accidentally executing the reader.Read() in a watch window or Immediate window, as if there is only one record, the next Read() will be false...?

Note you have properly used using so you do not need to close your reader or con

answered Feb 21, 2020 at 16:59

NeilNewman

1308 bronze badges

Collectives™ on Stack Overflow

SqlDataReader.Read() error, reading values from a stored procedure

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related