Home:ALL Converter>C# OracleDataReader.Get???(oracle AVG()) InvalidCastException

C# OracleDataReader.Get???(oracle AVG()) InvalidCastException

Ask Time:2014-07-07T13:37:05         Author:Ewert

Json Formatter

I'm working in C# with an oracle database, and i have some issues with InvalidCastException when i try to data out of OracleDataReader.

The problem is the type of the function AVG(). This is the query:

SELECT AVG(MBAR) mbavg, AVG(ML_MIN) mlavg, RSZ FROM (SELECT * FROM ( SELECT ID, RSZ rsz2 FROM SZIVARGASMERO ORDER BY ID DESC ) WHERE ROWNUM=1 ) a, SZIVARGASMERO WHERE RSZ=a.RSZ2 GROUP BY RSZ;

The table looks like this:

CREATE TABLE SZIVARGASMERO
(
  ID          NUMBER                            NOT NULL,
  RSZ         VARCHAR2(1000 BYTE),
  CIKKSZ      VARCHAR2(500 BYTE),
  DATE_G      DATE,
  DEV_ID      INTEGER,
  PROG        INTEGER,
  RES         VARCHAR2(50 BYTE),
  REG1        INTEGER,
  MBAR        FLOAT(126),
  FE          VARCHAR2(50 BYTE),
  REG2        INTEGER,
  ML_MIN      FLOAT(126),
  TIMESTAMPA  DATE,
  ERROR       INTEGER
)

I tried

double mbavg=reader.GetDouble(reader.GetOrdinal("mbavg"));
float mbavg=reader.GetFloat(reader.GetOrdinal("mbavg"));

And also

string mbavg=reader.GetString(reader.GetOrdinal("mbavg"));

And i tried parse double with CultureInvariant and System.Conver.ToXXX too, but none of them works.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO.Ports;
using System.IO;
using System.Threading;
//using MySql.Data.MySqlClient;
using System.Globalization;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
....

public const string oradb ="....";
public OracleConnection conn = new OracleConnection(oradb);
...
OracleCommand cmd = new OracleCommand();
            conn.Open();
            try
            {
                cmd.Connection = conn;
                cmd.CommandText = "SELECT AVG(MBAR) mbavg, AVG(ML_MIN) mlavg, RSZ FROM (SELECT * FROM ( SELECT ID, RSZ rsz2 FROM SZIVARGASMERO ORDER BY ID DESC ) WHERE ROWNUM=1 ) a, SZIVARGASMERO WHERE RSZ=a.RSZ2 GROUP BY RSZ";
                cmd.CommandType = CommandType.Text;
                OracleDataReader dr = cmd.ExecuteReader();
                //cmd.ExecuteNonQuery();
                while (dr.Read())
                {
                    if (dr.HasRows)
                    {

                       // double mbavg = dr.GetDouble(0);
                        double mlavg = dr.GetDouble(dr.GetOrdinal("mlavg"));
                        string last_rsz = dr.GetString(dr.GetOrdinal("RSZ"));
                       // mbaravg.Text = System.Convert.ToString(mbavg) + " mbar";
                        mlavg_lb.Text = System.Convert.ToString(mlavg) + " ml/min";
                        last_rsz_lb.Text = last_rsz;
                    }
                }
                dr.Dispose();
                dr.Close();

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    oracle_lb.Text = "OK";
                    ora_lbl_2.Text = "Oracle: OK!";
                    ora_lbl_2.ForeColor = Color.DarkGreen;
                }
                else
                {
                    MessageBox.Show("Nincs adatbáziskapcsolat!");
                }

Who could i solve it? Please help me. Thank you very much :)

Edit:

cmd.CommandText = "SELECT TRUNC(AVG(MBAR),6) mbavg, TRUNC(AVG(ML_MIN),6) mlavg, RSZ FROM (SELECT * FROM ( SELECT ID, RSZ rsz2 FROM SZIVARGASMERO ORDER BY ID DESC ) WHERE ROWNUM=1 ) a, SZIVARGASMERO WHERE RSZ=a.RSZ2 GROUP BY RSZ";

decimal mbavg_d=dr.GetDecimal(GetOrdinal("mbavg"));

double mbavg=(double)mbavg_d

Author:Ewert,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/24603641/c-sharp-oracledatareader-getoracle-avg-invalidcastexception
yy