Error handling exceptions - "Undefined object reference for an instance of an object"

Question

public String IdentificarAcessoDAL(FuncionarioDTO acesso)
    {
        try
        {
            conexao.cmd.Connection = conexao.conexao;
            string recuperar = "SELECT CARGO.NOME_CARGO FROM LOGIN INNER JOIN FUNCIONARIO ON FUNCIONARIO.CPF_FUNCIONARIO = LOGIN.CPF_FUNCIONARIO INNER JOIN CARGO ON CARGO.ID_CARGO = FUNCIONARIO.ID_CARGO WHERE LOGIN.USUARIO_LOGIN = '" + acesso.User + "' AND LOGIN.SENHA_LOGIN = '" + acesso.Password + "'";
            conexao.cmd.CommandText = recuperar;

            conexao.conexao.Open();
            return funcionarioDTO.Funcao = conexao.cmd.ExecuteScalar().ToString();
        }
        catch (Exception erro)
        {
            throw erro;
        }
        finally
        {
            conexao.conexao.Close();
        }
    }

Whenever I enter the correct information it executes the application perfectly, but when it does not find in the database, it simply stops the execution of the program and displays the following error:

The program seeks the position of the employee, and for this it uses the login and the password, and this error only appears when the login and/or password are incorrect, otherwise it works perfectly.

Answer 1

public String IdentificarAcessoDAL(FuncionarioDTO acesso) {
    try {
        conexao.cmd.Connection = conexao.conexao;
        conexao.cmd.CommandText = "SELECT CARGO.NOME_CARGO FROM LOGIN INNER JOIN FUNCIONARIO ON FUNCIONARIO.CPF_FUNCIONARIO = LOGIN.CPF_FUNCIONARIO INNER JOIN CARGO ON CARGO.ID_CARGO = FUNCIONARIO.ID_CARGO WHERE LOGIN.USUARIO_LOGIN = '" + acesso.User + "' AND LOGIN.SENHA_LOGIN = '" + acesso.Password + "'";
        conexao.conexao.Open();
        return funcionarioDTO.Funcao = conexao.cmd.ExecuteScalar().ToString();
    } finally {
        conexao.conexao.Close();
    }
}

Ready, simple.

The code is still not good. Surely this connection control is something badly done and this would need to be fixed, but it is not the focus of the question. The architecture of this application is certainly a source of problems that you are having and will have in the future, I would simplify this.

The solution was simple because it was doing something unnecessary there. Why capture an exception to do anything? Exceptions exist to propagate the error, only capture when you know what to do with it and solve it properly, it makes no sense to capture to do anything.

I’d rather do it so it’s simpler to manage:

public String IdentificarAcessoDAL(FuncionarioDTO acesso) {
    using (var connection = new SqlConnection(connectionString)) {
        var command = new SqlCommand("SELECT CARGO.NOME_CARGO FROM LOGIN INNER JOIN FUNCIONARIO ON FUNCIONARIO.CPF_FUNCIONARIO = LOGIN.CPF_FUNCIONARIO INNER JOIN CARGO ON CARGO.ID_CARGO = FUNCIONARIO.ID_CARGO WHERE LOGIN.USUARIO_LOGIN = '" + acesso.User + "' AND LOGIN.SENHA_LOGIN = '" + acesso.Password + "'", connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}

I put in the Github for future reference.

Lie, I would make it even simpler, but it depends on a specific architecture, is not the focus of the question and is too broad to put in an answer.

WARNING! I did not fix the serious security problem you have in the application, I focused only on the specific problem. See the correct. Another example.