Error trying to log in with mysql

Question

I am trying to log a user to my system using a Mysql database, but it is returning me a Nullreference error. I tried to check if it was null before converting to string, but the error persists. Why does the error happen? How to fix?

Method where I execute the query:

private static string executarQueryScalar(MySqlCommand command)
{
    MySqlConnection connect = getConexão();
    try
    {
        connect.Open();
        return command.ExecuteScalar().ToString(); //Linha do erro
    }
    catch
    {
        return null;
    }
    finally
    {
        connect.Close();
    }
}

Method where I call you:

private static bool testarLogin(string usuario, string senha)
{
    string query = "SELECT * FROM tbl_usuario WHERE (usuario=@Usuario OR email=@Usuario) AND senha=@Senha";
    MySqlCommand command = new MySqlCommand(query, getConexão());
    command.Parameters.AddWithValue("@Usuario", usuario);
    command.Parameters.AddWithValue("@Senha", senha);

    return Convert.ToInt32(executarQueryScalar(command)) > 0;
}

Method of getConnection():

private static MySqlConnection getConexão()
{
    string servidor = "server", banco = "database", banco_usuario = "user", banco_senha = "password";
    return new MySqlConnection($"Server={servidor};Database={banco};Uid={banco_usuario};Pwd={banco_senha}");
}

Error presented:

System.Nullreferenceexception: Object reference not set to an instance of an object. in Mysql.Data.Mysqlclient.MySqlCommand.Executescalar() in PureCheats.Validações.ChecarLogin.executarQueryScalar(MySqlCommand command)

Answer 1

you are calling the getConexão() outside the method that executes the command, and then calls again inside the method. Thus, you pass the command with a connection, and open another.

One of the right ways would be something like this:

private static bool testarLogin(string usuario, string senha)
{
    using (MySqlConnection conexao = getConexão())
    {
        string query = "SELECT * FROM tbl_usuario WHERE (usuario=@Usuario OR email=@Usuario) AND senha=@Senha";
        conexao.Open();
        MySqlCommand command = new MySqlCommand(query, conexao );
        command.Parameters.AddWithValue("@Usuario", usuario);
        command.Parameters.AddWithValue("@Senha", senha);
        int resultado = command.ExecuteScalar();
        conexao.Close();

        return resultado > 0;
     }
}

ps. I know it works with ã, but I confess q gave me an agony see the signature of the method written like this. Look over the nomenclature patterns =]