Dimskiy Dimskiy - 1 year ago 131
C# Question

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

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?

  5. Anything else, any best practices/suggestions are welcomed.



Thank you.

Answer Source

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 you 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 is 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 you burdens.
  4. No you should not 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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download