Oracle.ManagedDataAccess

Oracle.ManagedDataAccess and Oracle.ManagedDataAccess.Core

Because Oracle data types don't directly translate to .NET data types, a common configuration includes conversion.

For Oracle.ManagedDataAccess.Core, simply use that package. The namespaces and the types are identical so the same code will compile for both.

public static class OracleManagedDataAccessExtensions
{
    private static readonly ValueExtractor _extractor = ValueExtractor.Create(config =>
    {
        config.UseNullTester(obj => obj is DBNull);

        // Configure unwrapping of the Oracle* types that implement INullable.
        config.UseUnwrapper(
            // Test for Oracle.ManagedDataAccess.Types.INullable.
            obj => obj is INullable,
            // The Oracle* types all have a public `Value` property , but it's defined by each type
            // rather than by a common base class or interface.  Use a little reflection to get it.
            // This could be optimized, of course.
            obj => ((INullable)obj).IsNull ? null : obj.GetType().GetProperty("Value").GetValue(obj));
            );

        // Oracle databases are often designed with fixed-width character columns.
        // Use a custom string handler that returns all strings trimmed.
        config.UseHandler<string>(
            obj => obj.ToString().TrimEnd());

        // Use a converter that can use the source type's TypeConverter, if available,
        // then the destination type's TypeConverter, if available, and finally fall
        // back to System.Convert.ChangeType().
        config.UseConverter(ExtractionConverters.DualTypeConverterWithFallback);
    });

    public static T Column<T>(this OracleDataReader reader, int i)
    {
        if (reader is null)
            throw new ArgumentNullException(nameof(reader));

        return _valueExtractor.Extract<T>(reader.GetValue(i));
    }

    public static T Column<T>(this OracleDataReader reader, string name)
    {
        if (reader is null)
            throw new ArgumentNullException(nameof(reader));

        return _valueExtractor.Extract<T>(reader.GetValue(reader.GetOrdinal(name)));
    }

    public static T OracleColumn<T>(this OracleDataReader reader, int i)
    {
        if (reader is null)
            throw new ArgumentNullException(nameof(reader));

        return _valueExtractor.Extract<T>(reader.GetOracleValue(i));
    }

    public static T OracleColumn<T>(this OracleDataReader reader, string name)
    {
        if (reader is null)
            throw new ArgumentNullException(nameof(reader));

        return _valueExtractor.Extract<T>(reader.GetOracleValue(reader.GetOrdinal(name)));
    }

    public static T GetValue<T>(this OracleParameter parameter)
    {
        if (parameter is null)
            throw new ArgumentNullException(nameof(parameter));

        return _valueExtractor.Extract<T>(parameter.Value);
    }
}

Whereas OracleDataReader has GetOracleValue to get one of the Oracle* values instead of a BCL type, OracleParameter's Value property returns BCL types if OracleDbType is set or Oracle* types if OracleDbTypeEx is set.

Usage

Integer columns, particularly IDs that are auto-incrementing, usually are of the NUMBER data type. C# code usually deals with them as long values.

long id = reader.Column<long>("ID");
string name = reader.Column<string>("NAME");
int? x = reader.Column<int?>("SOME_INT");

Whatever you specify as the type, the above extension methods will make an attempt to convert the value to that type.

The Sql* data types, including nullable versions of them, can be used the same way with OracleColumn.

OracleDate? endDate = reader.OracleColumn<OracleDate?>("END_DATE");