How to retrieve a particular value of a column based on another column of a same row?

Question

I an reading data from a database table. Now I want to store the data in a variable. I need to put conditions depending on the values contained in the columns.

    OracleCommand cmd = new OracleCommand("select user_name,parameter_name,parameter_value from table1 where user_name ='USER'", ocConnection);
            cmd.CommandType = CommandType.Text;
            OracleDataReader dr = cmd.ExecuteReader();


            while (dr.Read())
            {
                if (dr["PARAMETER_NAME"].ToString() =="NAME1")
                {
                    class1.Value1 = Convert.ToInt32(dr["PARAMETER_VALUE"].ToString());

                }

                if ((dr["PARAMETER_NAME"].ToString()) == "NAME2")
                {
                    class1.Value2 = Convert.ToInt32(dr["PARAMETER_VALUE"].ToString());
                }
 dr.close();
 dr.Dispose();
}

But this gives an error as the control doesn't go inside any of the if blocks. Also gives an error such as :

System.IndexOutofRangeException unable to find specified column in resultset.

Though the values are there in the table.

Answer 1

For debugging purposes you can add the following line after you create the reader to get the names of the fields, which will be returned (statement taken from this answer):

var columns = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();

So you can check if PARAMETER_NAME and PARAMETER_VALUE will be returned as expected. Then you can further investigate your problem.

Note:

I recommend using the using-Statement for the OracleCommand and the OracleDataReader, so you don't have to call Dispose explicitly. Also, calling dr.Close() and dr.Dispose() does the same thing is not needed when using the using-Block. You also forgot to dispose the OracleCommand. With the second using-Block this is also fixed.

using(OracleCommand cmd = new OracleCommand("select user_name,parameter_name,parameter_value from table1 where user_name ='USER'", ocConnection))
{
    cmd.CommandType = CommandType.Text;
    using(OracleDataReader dr = cmd.ExecuteReader())
    {
        // check column names for debugging purposes
        var columns = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();

        while (dr.Read())
        {
            if (dr["PARAMETER_NAME"].ToString() =="NAME1")
            {
                class1.Value1 = Convert.ToInt32(dr["PARAMETER_VALUE"].ToString());
            }

            if ((dr["PARAMETER_NAME"].ToString()) == "NAME2")
            {
                class1.Value2 = Convert.ToInt32(dr["PARAMETER_VALUE"].ToString());
            }
        }
    }
}