How to do a query using Oracle.Managed.DataAccess in C #

0

I have the following method in C #

public static ProdutividadeBean GetProdutividade()
        {
            Console.WriteLine(CairuUtils.UserContext.getCodUsuario());
            String strConnection = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srvoda-scan.cairunet.ad.br)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=SRV_JAPAO))); User Id=user;Password=pass;";
            Console.WriteLine(strConnection);
            Oracle.ManagedDataAccess.Client.OracleConnection con = new Oracle.ManagedDataAccess.Client.OracleConnection(strConnection);

            try
            {
                con.Open();
                StringBuilder sb = new StringBuilder();
                sb.AppendLine(" SELECT ");
                sb.AppendLine(" TAB.RANK, ");
                sb.AppendLine(" TAB.CODFUNC, ");
                sb.AppendLine(" TAB.FUNCSEPARADOR AS NOME, ");
                sb.AppendLine(" TAB.QTDEOS, ");
                sb.AppendLine(" TAB.QTITENS, ");
                sb.AppendLine(" TAB.PESO, ");
                sb.AppendLine(" TAB.VOLUME, ");
                sb.AppendLine(" TAB.VLVENDA, ");
                sb.AppendLine(" TAB.MESA ");

                sb.AppendLine(" FROM TABLE(PKG_EXPEDICAO_CAIRU.F_GET_ACOMP_PRODUCAO_OS( ");
                sb.AppendLine("                                    to_date('14/02/2018', 'dd/mm/yyyy') ");
                sb.AppendLine("                                               ,to_date('14/02/2018', 'dd/mm/yyyy') ");
                sb.AppendLine("                                                       ,'00:00' ");
                sb.AppendLine(" ,'23:59' ");
                sb.AppendLine(" ,'CONFERENTE' ");
                sb.AppendLine(" ,'1,2,3,11' ");
                sb.AppendLine(" ,  514");//CairuUtils.UserContext.getCodUsuario()
                sb.AppendLine(" )        ");
                sb.AppendLine(" )TAB  ");
               // OracleCommand cmd = new OracleCommand(sb.ToString(), con);
                Oracle.ManagedDataAccess.Client.OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand(sb.ToString(), con);
                 Oracle.ManagedDataAccess.Client.OracleDataReader dr = cmd.ExecuteReader();

                ProdutividadeBean p = new ProdutividadeBean();
                Console.WriteLine("vai executar a porra agora....");
                if (dr.HasRows)
                {
                    dr.Read();
                    Console.WriteLine("entra aqui seu filha da puta");
                    p.Mesa = dr["MESA"].ToString();
                    p.Peso = dr["PESO"].ToString();
                    p.Qtitens = dr["QTITENS"].ToString();
                    p.Qtdeos = dr["QTDEOS"].ToString();
                    p.Valor = Convert.ToDouble(dr["VLVENDA"]).ToString();
                    p.Volume = dr["VOLUME"].ToString();
                }

                return p;
            }
            catch (Oracle.ManagedDataAccess.Client.OracleException e)
            {
                throw new Exception(e.Message);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

This method fetches data from a function in the database. I am trying to get the data to be returned from the data in SQLNavigator, but I would like to know how to do this.

Here is my query that returns the data normally in SQL Navigator, including a Ctr + C of which is in the

SELECT 
TAB.RANK,
TAB.CODFUNC,
TAB.FUNCSEPARADOR AS NOME,
TAB.QTDEOS,
TAB.QTITENS,
TAB.PESO,
TAB.VOLUME,
TAB.VLVENDA,
TAB.MESA

FROM TABLE(PKG_EXPEDICAO_CAIRU.F_GET_ACOMP_PRODUCAO_OS(
                                                      to_date('14/02/2018', 'dd/mm/yyyy')
                                                      ,to_date('14/02/2018', 'dd/mm/yyyy')
                                                      ,'00:00'
                                                      ,'23:59'
                                                      ,'CONFERENTE'
                                                      ,'1,2,3,11'
                                                      , 514
                                   )       
)TAB 

This returns me a line with 9 columns, as expected (In the database application)

I figured it could be a limitation of Oracle DataAcces in performing query in function with type return, so I updated it to the latest version and it still does not work. Does anyone know why this does not work?

    
asked by anonymous 14.02.2018 / 21:35

0 answers