MySQL connection seems to be only reading first query

Question

I'm working on a C# project and until now I haven't had any difficulties whatsoever, the problem is while coding a simple login form.

Here's the code for it:

    MySqlConnection con = new MySqlConnection();
    MySqlCommand com = new MySqlCommand();

    public LoginForm()
    {
        InitializeComponent();

        con.ConnectionString = @"Data Source=localhost;port=3306;Initial Catalog=databaseName;User id=root;password=''";
    }

    private void button1_Click(object sender, EventArgs e)
    {
        con.Open();

        com.Connection = con;
        com.CommandText = "select * from users";
        MySqlDataReader dr = com.ExecuteReader();
        if (dr.Read())
        {
            if (txtBoxUsername.Text.Equals(dr["username"].ToString()) && txtBoxPassword.Text.Equals(dr["password"].ToString()))
            {
                lblIncorrect.Text = "";
                this.Hide();
                InjectForm f2 = new InjectForm();
                f2.Show();
            }
            else
            {
                lblIncorrect.Text = "Incorrect userename or password.";
                txtBoxPassword.Text = "";
            }
        }
        con.Close();
    }

As you can see, on button click it will open my MySql connection and select everything from the table users. Then check if the username and password entered on the text boxes match with the ones stored on phpMyAdmin.

The problem comes now, if I use the first user, it will sign in correctly and do what it's supposed to do, in the other hand though, if I sign in with my second user, it will throw the "Incorrect username or password." text.

This should all work but it just doesn't.

Any ideas?

Answer 1

For optimal memory consumption and speed up, just fetch the desired record. You will notice a difference in execution speed when the data becomes very large.

change button1_Click to :

private void button1_Click(object sender, EventArgs e)
{
     con.Open();

     com.Connection = con;
     com.CommandText = "select * from users where username = @username and password = @password";
     com.Parameters.Add("@username", SqlDbType.VarChar, 50).Value = txtBoxUsername.Text;
     com.Parameters.Add("@password", SqlDbType.VarChar, 50).Value = txtBoxPassword.Text;
     MySqlDataReader dr = com.ExecuteReader();
     if (dr.HasRows)
     {
         while (dr.Read())
         {
             lblIncorrect.Text = "";
             this.Hide();
             InjectForm f2 = new InjectForm();
             f2.Show();
             break;
         }
     }
     else
     {
         lblIncorrect.Text = "Incorrect userename or password.";
         txtBoxPassword.Text = "";
     }
     con.Close();
}

Note: use while(dr.Read()) to read all rows to scroll through all rows.