What are the best practices working with Oracle.DataAccess.Client?

Question

I'm going over a lengthy data access code of a somewhat older app. Every function is calling a stored procedure to select something from Oracle DB. Every function more or less looks like the code below:

public List<SomeObject> GetMeSomethingFromDB(string myParam, int anotherParam)
{
    OracleConnection conn = null;
    OracleDataReader dataReader = null;
    try
    {
        conn = new OracleConnection(Settings.ConnectionString);
        conn.Open();

        var cmd = new OracleCommand("STORED_PROC_NAME", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new OracleParameter("IN_MY_PARAM", OracleDbType.Varchar2)).Value = myParam;
        cmd.Parameters.Add(new OracleParameter("IN_ANOTHER_PARAM", OracleDbType.Int32)).Value = anotherParam;
        cmd.Parameters.Add(new OracleParameter("OUT_REF_CURSOR", OracleDbType.RefCursor)).Direction = ParameterDirection.Output;
        dataReader = cmd.ExecuteReader();

        List<SomeObject> result = new List<SomeObject>();
        if (dataReader == null || !dataReader.HasRows) return result;

        while (dataReader.Read())
        {
            SomeObject someObject = new SomeObject
            {
                SomeId = (int)dataReader["SOME_ID"],
                SomeStringValue = dataReader["SOME_STRING_VALUE"].ToString()
            };

            result.Add(someObject);
        }

        return result;
    }
    catch (Exception e)
    {
        throw e;
    }
    finally
    {
        if (dataReader != null)
        {
            dataReader.Close();
            dataReader.Dispose();
        }
        if (conn != null)
        {
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
        }
    }
}

My questions are:

1. Some functions use class level OracleConnection variable instead. What is preferred - function level or class level variable?
2. Is the check dataReader == null necessary? Would it ever be NULL after cmd.ExecuteReader() call?
3. Functions differ when it comes to connection Close/Dispose and reader Close/Dispose. What is the correct way/order in which to close/dispose? Wouldn't the reader automatically Close/Dispose if the connection is disposed?
4. I'm looking to hook up Oracle.ManagedDataAccess.Client to this project in the near future. Will anything in this code change to work with managed data access client?

Answer 1

The using statement will simplify a lot your code.

public List<SomeObject> GetMeSomethingFromDB(string myParam, int anotherParam)
{
    using (OracleConnection conn = new OracleConnection(Settings.ConnectionString))
    using (OracleCommand cmd = new OracleCommand("STORED_PROC_NAME", conn))
    {   
        conn.Open();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new OracleParameter("IN_MY_PARAM", OracleDbType.Varchar2)).Value = myParam;
        cmd.Parameters.Add(new OracleParameter("IN_ANOTHER_PARAM", OracleDbType.Int32)).Value = anotherParam;
        cmd.Parameters.Add(new OracleParameter("OUT_REF_CURSOR", OracleDbType.RefCursor)).Direction = ParameterDirection.Output;
        using (OracleDataReader dataReader = cmd.ExecuteReader())
        {
            while (dataReader.Read())
            {
                 SomeObject someObject = new SomeObject
                 {
                     SomeId = (int)dataReader["SOME_ID"],
                     SomeStringValue = dataReader["SOME_STRING_VALUE"].ToString()
                 };
                 result.Add(someObject);
            }
        }
    }
    return result;
}

1. Use always a local connection object and include it in a using statement to have a correct closing and disposal of the object (the same holds true also for the OracleDataReader and OracleCommand). This will free your server from the memory and threads required to keep the connection with your code and performances are guaranteed by the connection pooling enabled by ADO.NET providers
2. No, the call is not necessary and neither the call to HasRows if you plan to loop over the result. The reader returns false if there are no rows or if you reach the end of the data set
3. See the point about the using statement. Proper using statements will remove this problem from your burdens.
4. You should't have any problem with this code if you use the ODP provider from Oracle
5. There is no need to have a try catch if you only want to rethrow the exception. Just let it bubble up to the upper level without disrupting the stack trace with a throw e and all the code required in the finally statement is implicitly added by the compiler in the using closing curly brace.