Home > Back-end >  MySQL Connector C#, MySQLDataRead.Read() only running when a value returned
MySQL Connector C#, MySQLDataRead.Read() only running when a value returned

Time:10-24

As the name suggest's, this is an issue with the Reader's while loop only running when a value is returned. See below in this scenario, in the case of if (email == rdr[0].ToString()) returns true, the while loop itself executes. However if it's not true the else does not execute. Also important to note that Debug.Log("Checking to see if the account exists"); only actually executes if the account exist's, as the check intends.

My assumption is, that if the Connector cannot find a row, it will not run the loop. How can I achieve this running?

Thanks.

 conn.Open();
            Debug.Log("SUCCESSFULL CONNECTION!");
            if (!isLogin)
            {
                Debug.Log("Is not login");
                bool doesEmailExist = false;
                string seecQuery = "SELECT * FROM accounts WHERE email = '"  email  "'";
                Debug.Log(seecQuery);
                MySqlCommand cmd = new MySqlCommand(seecQuery, conn);
                MySqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    Debug.Log("Checking to see if the account exists");
                    if (email == rdr[0].ToString())
                    {
                        Debug.Log("The account exists");
                        accountExists.SetActive(true);
                        //doesEmailExist = true;
                    }
                    else //if (!doesEmailExist)
                    {
                        Debug.Log("Email is not in use. Starting OTA");
                        StartCoroutine(sendOTA()); // Start the OTP process
                        OTARead.SetActive(true);
                    }

CodePudding user response:

Just an update for this, I managed to fix the issue with a workaround. Here it is. Rather than performing a while loop I checked to see if the Reader returned any rows or not. Worked fine.

conn.Open();
            Debug.Log("SUCCESSFULL CONNECTION!");
            if (!isLogin)
            {
                Debug.Log("Is not login");
                bool doesEmailExist = false;
                string seecQuery = "SELECT * FROM accounts WHERE email = '"  email  "'";
                Debug.Log(seecQuery);
                MySqlCommand cmd = new MySqlCommand(seecQuery, conn);
                MySqlDataReader rdr = cmd.ExecuteReader();

                if (!rdr.HasRows)
                {
                    Debug.Log("Email is not in use. Starting OTA");
                    StartCoroutine(sendOTA()); // Start the OTP process
                    OTARead.SetActive(true);
                }
                else
                {
                    Debug.Log("The account exists");
                    accountExists.SetActive(true);
                }

                rdr.Close();
                conn.Close();
            }

CodePudding user response:

Your primary issue is that the resultset has no rows, so rdr.Read() returns false on the first run and never executes the loop.

If the email column is unique, then all you want is a single result. For that you can obviate a reader and just use cmd.ExecuteScalar

  • Note correct use of parameterization, preventing dangerous SQL injection
  • Note using blocks, which will guarantee the connection gets closed in the event of an exception
bool doesEmailExist = false;
const string seecQuery = @"
SELECT 1
FROM accounts
WHERE email = @email;
";
Debug.Log(seecQuery);
using (var conn = new MySqlConnection(yourConnString))
using (var cmd = new MySqlCommand(seecQuery, conn))
{
    cmd.Parameters.AddWithValue("@email", email);
    conn.Open();
    Debug.Log("SUCCESSFULL CONNECTION!");
    Debug.Log("Checking to see if the account exists");
    if (cmd.ExecuteScalar() == (object)1)
    {
        Debug.Log("The account exists");
        accountExists.SetActive(true);
        //doesEmailExist = true;
    }
    else
    {
        Debug.Log("Email is not in use. Starting OTA");
        StartCoroutine(sendOTA()); // Start the OTP process
        OTARead.SetActive(true);
    }
}
  • Related