Query Mysql in Visual Studio does not return all data

0

I have a problem in Visual Studio, where in my database access class, I have a query that does a query in my Mysql database, and returns only the last result. So, how much do I test this same query directly in the Mysql database, using tools like PhpMyAdmin, Mysql Workbench, etc. The bank returns me, all the results, which should return, which in the case are 29 items.

Follow the C # code from 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);
        }
    
asked by anonymous 30.08.2014 / 15:44

1 answer

1

Try the function: (I think it will not work with the parameters.) I do not know how to do with parameters, as I always provide SQL ready, or I use Fluent NHibernate.

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]);
        }
    
06.09.2014 / 03:43