How to retrieve all fields for a given record using OracleDataReader?

Question

My question, which is similar to this one, is how can I use OracleDataReader to retrieve all the fields for a given record? Currently, I've been using this method, which returns only one column value at a time:

public string Select_File(string filename, string subdirectory, string envID)
{
    Data_Access da = new Data_Access();
    OracleConnection conn = da.openDB();

    OracleCommand cmd = new OracleCommand();
    cmd.Connection = conn;
    cmd.CommandText = "SELECT * FROM EIP_Deployment_Files" 
        + " WHERE Filename ='" + filename + "'"
        + " AND Subdirectory = '" + subdirectory + "'"
        + " AND Environment_ID = '" + envID + "'";
    cmd.CommandType = CommandType.Text;

    string x;
    OracleDataReader dr = cmd.ExecuteReader();
    if (dr.HasRows) // file exists in DB
    {
        dr.Read();
        x = dr.GetString(2).ToString(); // return baseline filename (index 2)
    }
    else
    {
        x = "New File";
    }

    cmd.Dispose();
    da.CloseDB(conn);
    return x;
}

I'm sure that this method is far from perfect and ppl will be quick to point that out (I was basically given it by my supervisor since I didn't have any prior experience in ASP.NET) but all I really care about is that it works. My question is: how can it be modified to return all the fields for a given record?

The fields will be of either VARCHAR2, CHAR, or DATE datatypes, (if that makes a difference) and some of these values may be null. I'm thinking I could convert them to strings and return them as a list?

Answer 1

Sorry for posting an answer to a very old question. As none of the answers are correct (either they have security issues or not checking for DBNull), I have decided to post my own.

public async Task<StringBuilder> FetchFileDetailsAsync(string filename, string subdirectory, string envId)
    {
        var sb = new StringBuilder();
        //TODO: Check the parameters

        const string connectionString = "user id=userid;password=secret;data source=" +
                                        "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.8)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)))";

        const string selectQuery = "SELECT * FROM EIP_Deployment_Files"
                                + " WHERE Filename = :filename"
                                + " AND Subdirectory = :subdirectory"
                                + " AND Environment_ID = :envID"
                                + " AND rownum<=1";

        using (var connection = new OracleConnection(connectionString))
        using (var cmd = new OracleCommand(selectQuery, connection) {BindByName = true, FetchSize = 1 /*As we are expecting only one record*/})
        {

            cmd.Parameters.Add(":filename", OracleDbType.Varchar2).Value = filename;
            cmd.Parameters.Add(":subdirectory", OracleDbType.Varchar2).Value = subdirectory;
            cmd.Parameters.Add(":envID", OracleDbType.Varchar2).Value = envId;

            //TODO: Add Exception Handling
            await connection.OpenAsync();
            var dataReader = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);

            var rowValues = new object[dataReader.FieldCount];
            if (dataReader.Read())
            {
                dataReader.GetValues(rowValues);
                for (var keyValueCounter = 0; keyValueCounter < rowValues.Length; keyValueCounter++)
                {
                    sb.AppendFormat("{0}:{1}", dataReader.GetName(keyValueCounter), 
                        rowValues[keyValueCounter] is DBNull ? string.Empty : rowValues[keyValueCounter])
                      .AppendLine();
                }

            }
            else
            {
                //No records found, do something here
            }
            dataReader.Close();
            dataReader.Dispose();
        }
        return sb;
    }

Answer 2

if u want something like this:

List<User> lstUser = new List<User>();
            string sqlQuery = "Select * from User_T where User_Name='" + oUser.UserName + "' And Password='" +oUser.Password + "' AND IsActive='"+1+"' AND IsDelete='"+0+"'";
            string connectionString = "Data Source=ORCL;User Id=ACCOUNTS;Password=ACCOUNTS";
            using (DBManager dbManager = new DBManager(connectionString))
            {
                try
                {

                    dbManager.Open();
                    OracleDataReader dataReader = dbManager.ExecuteDataReader(sqlQuery);
                    while (dataReader.Read())
                    {
                        oUser = new User();
                        oUser.Id = Convert.ToInt32(dataReader["ID"]);
                        oUser.CompanyId = Convert.ToInt32(dataReader["Company_ID"]);
                        oUser.BranchId = Convert.ToInt32(dataReader["Branch_ID"]);
                        oUser.UserName = Convert.ToString(dataReader["User_Name"]);
                        lstUser.Add(oUser);
                    }
                    dataReader.Close();
                    dataReader.Dispose();

                }
                catch
                (Exception)
                {


                }
                finally
                {
                    dbManager.Close();
                    dbManager.Dispose();
                }

Answer 3

To read all the data from the columns of the current row in a DataReader, you can simply use GetValues(), and extract the values from the array - they will be Objects, of database types.

Object[] values;
int numColumns = dr.GetValues(values); //after "reading" a row
for (int i = 0; i < numColumns; i++) {
    //read values[i]
}

MSDN - "For most applications, the GetValues method provides an efficient means for retrieving all columns, rather than retrieving each column individually."