MySQLDataReader not reading C#

Question

The Stored Procedure:

BEGIN
SELECT * FROM `user` WHERE `EmailAddress`= @p0 AND `Password` = @p1;
END

Where @p0 and @p1 are Varchar(100).

And the code:

using (MySqlConnection con = new MySqlConnection(Database.MySQLConstring))
{
    using (MySqlCommand cmd = new MySqlCommand("LoginCheck", con))
    {
        cmd.CommandType = CommandType.StoredProcedure; //"LoginCheck"
        cmd.Parameters.AddWithValue("email", Email);
        cmd.Parameters.AddWithValue("password", Pword);

        con.Open();

        MySqlDataReader reader = cmd.ExecuteReader();
        UserModel UM = new UserModel();
        While (reader.Read())
        {
            UM.UserId = (int)reader["UserID"];
            UM.DisplayName = (string)reader["DisplayName"];
            UM.Moderator = (int)reader["Moderator"];
        }

        con.Close();
        While(!string.IsNullOrEmpty(UM.DisplayName) && UM.UserId != 0)
        {
            Result = 1;
            return UM;
        }

        Result = -1;
        return UM;
    }
}

The code runs succesfull until it comes to the while(reader.Read()) part, then it skips it and goes to the con.close(). No errors or exceptions are thrown. It worked when I was using it when everything was SQL and not MySQL ,but I need to get it working in MySQL.

When I run the stored Procedure itself in de databse then I get my result that I need. but when I use the code it will skip the While part of the code.

Answer 1

If I were going to execute a procedure that was declared as:

CREATE PROCEDURE x(em VARCHAR, pw VARCHAR) --declaration of parameters
BEGIN
  SELECT * FROM `user` WHERE `EmailAddress`= em AND `Password` = pw;
END

I would ensure the parameters in my C# code were named the same as in the declaration of the stored procedure:

cmd.Parameters.Add(new MySqlParameter("em", Email));
cmd.Parameters.Add(new MySqlParameter("pw", Password));

I suspect that your query is not getting any rows, because the values you set never make it into the parameters, and are hence never used to query. reader.Read() returns false, because there are no rows. Also make sure that the values for EMail and Password that you're querying really do exist in the table

Try as a debugging thing, to make your query like:

SELECT UserID, DisplayName, Moderator FROM `user` WHERE `EmailAddress`= @p0 AND `Password` = @p1 
UNION ALL 
SELECT 0, CONCAT_WS(' ', 'There is no user with email/password of', @p0, '/', @p1), '' FROM DUAL;

Or however your parameters are named now..

Your reader.Read()should now return true because this query should always return a row, so inspect the value of DisplayName, it should tell what search terms were applied