Query Mysql in Visual Studio does not return all data

Question

I have a problem in Visual Studio, where in my database access class, I have a query that makes a query in my Mysql database, and returns only the last result. Therefore, when I test this same query directly in the Mysql database, using tools like Phpmyadmin, Mysql Workbench, etc. The database returns me, all the results, which should return, which in case are 29 items.

Follow the C# code of my database access method,

    public DataTable ObterListadePrecos(int codigoMedico)
    {
        Mysql mysql = new Mysql();
        try
        {
            mysql.Comando.CommandText = @"select e.codigo_anatomico, em.medico as cod, e.nome, concat(g.nome, ' > ',  s.nome) as grupo, e.valor as valorCheio, t.codigo as codTabela, t.desconto,
                                    (valor - (valor * t.desconto / 100) ) as valorComDesconto,              
                                    (valor * (select case 
                                                    when desconto = 0 
                                                    then 100 
                                                    else desconto 
                                                    end) * t.usuario_convencional /100 / 100 ) as uconvencional,              
                                    (valor * (select case 
                                                    when desconto = 0 
                                                    then 100 
                                                    else desconto 
                                                    end) * t.convenio_convencional /100 / 100 ) as sconvencional,              
                                    (valor * (select case 
                                                    when desconto = 0 
                                                    then 100 
                                                    else desconto 
                                                    end) * t.usuario_master /100 / 100 ) as umaster,              
                                    (valor * (select case 
                                                    when desconto = 0 
                                                    then 100 
                                                    else desconto 
                                                    end) * t.convenio_master /100 / 100 ) as smaster
                                    from exame_medico em, exames e, subgrupo_exame s, grupo_exame g, tabela_preco t, medico m
                                    where e.codigo_subgrupo = s.codigo
                                    and em.subgrupo = s.codigo
                                    and s.grupo_codigo = g.codigo
                                    and m.med_tabela = t.codigo
                                    and em.medico = m.med_cod
                                    and m.med_cod = @CODIGO
                                    order by e.nome desc";

            mysql.Comando.Parameters.AddWithValue("@CODIGO", codigoMedico);

            return mysql.ExecutarComando();
        }
        catch(MysqlException e)
        {
            //tratamento de minha exceptions
        }
    }

Parte do código onde monto a Grid para exibição dos dados:

    gridView.DataSource = minhaClasse.ObterListadePrecos(valor);
    gridView.DataBind()

**[UPDATE]**Código do método executar comando

public DataTable ExecutarComando()
        {
            if (_comando.CommandText == string.Empty)
                throw new Exception("Comando sem instrução SQL.");

            DataTable dt = new DataTable();

            try
            {
                if (_conexao.State == ConnectionState.Closed)
                    _conexao.Open();

                MySqlDataReader dr = _comando.ExecuteReader();

                dt.Load(dr);
            }
            catch (Exception ex)
            {

                if (_transacao != null)
                {
                    _transacao.Rollback();
                    _transacao = null;
                }

                if (_conexao.State == ConnectionState.Open)
                    _conexao.Close();

                throw new Exception("Mysql.ExecutarComando.", ex);
            }
            finally
            {
                if (_gerenciarConexaoAutomatica)
                    _conexao.Close();
            }

            return (dt);
        }

Answer 1

Try the function: (I don’t think it will work with the parameters. I don’t know how to do with parameters, because I always provide SQL ready, or use Fluent Nhibernate. While providing the ready SQL)

public DataTable ExecutarComando()
        {
            if (_comando.CommandText == string.Empty)
                throw new Exception("Comando sem instrução SQL.");

            DataSet DS = new DataSet();
            DataTable dt = new DataTable();

            try
            {
                if (_conexao.State == ConnectionState.Closed)
                    _conexao.Open();    

                using (var MyDataAdapter = new System.Data.OleDb.OleDbDataAdapter(_comando.CommandText, _conexao))
                {
                    MyDataAdapter.Fill(dt, "TABELA");
                }

                //MySqlDataReader dr = _comando.ExecuteReader();
                //dt.Load(dr);    
            }
            catch (Exception ex)
            {    
                if (_transacao != null)
                {
                    _transacao.Rollback();
                    _transacao = null;
                }

                if (_conexao.State == ConnectionState.Open)
                    _conexao.Close();

                throw new Exception("Mysql.ExecutarComando.", ex);
            }
            finally
            {
                if (_gerenciarConexaoAutomatica)
                    _conexao.Close();
            }

            //return (dt);
            return (DS.Tables[0]);
        }