We help IT Professionals succeed at work.

Oracle Datareader null check extention method

Natavia Finnie
Natavia Finnie asked
on
Can we create a extension method ToVauleOrDefault on the DataReader, so when we do reader.GetValue(ordinalId).ToValueOrDefault<string>, within that method it will check null and then convert using oracle datereader

public static T ValueOrDefault<T>(this XElement element, T defaultValue = default(T))
        {
            if (element == null)
            {
                return defaultValue;
            }

            return element.Value.To(defaultValue);
        }

Open in new window

Comment
Watch Question

View Solutions Only

kaufmed
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2015
Commented:
Certainly. We do something similar in my organization. Here's an example:

public static T ValueOrDefault<T>(this OracleDataReader reader, int columnPosition, T defaultValue = default(T))
{
    if (!reader.IsDBNull(columnPosition))
    {
        return (T)reader.GetValue(columnPosition);
    }   

    return defaultValue;
}

Open in new window


I would suggest putting the GetValue call inside the extension method:  it will simplify your calls. Usage would be something like:

int x = reader.ValueOrDefault(2, 42);

Open in new window


Most of the time, however, we prefer to use column names because it's easier to manage if we move columns around in the SELECT. If you wanted to do that, then you could tweak the above to something like:

public static T ValueOrDefault<T>(this OracleDataReader reader, string columnName, T defaultValue = default(T))
{
    int columnPosition = reader.GetOrdinal(columnName);

    if (!reader.IsDBNull(columnPosition))
    {
        return (T)reader.GetValue(columnPosition);
    }   

    return defaultValue;
}

Open in new window


Then usage would pass the column name instead of its position:

string x = reader.ValueOrDefault("FIRST_NAME", "N/A");

Open in new window


That's also a bit clearer in its intent:  "take the FIRST_NAME column's value" versus "take the second value from the SELECT".
Natavia FinnieSenior Analyst Technical Focus Applications

Author

Commented:
@kaufmed

string x = reader.ValueOrDefault("FIRST_NAME", "N/A");

Gives an error under 'reader' : cannot convert instance argument type 'Oracle.DataAccess.Client.OracleDataReader' to 'System.Data.OracleClient.OracleDataReader'
kaufmed
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
It sounds like you're mixing two version of the Oracle stuff. Back in the day, when Microsoft released .NET they included their own classes to connect with Oracle. Oracle a few years later released their own .NET libraries, and those libraries became favored. (And many years later Oracle released a fully-manged .NET client that you could pull in solely from NuGet.) Microsoft subsequently deprecated the Oracle classes. The classes that are in System.Data.OracleClient are the Microsoft classes. Don't use those. Just use the classes from Oracle.DataAccess.Client...those are the Oracle-written classes.
kaufmed
SILVER EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
Do you have the Oracle client installed on your machine? If no, then I would suggest importing the managed driver from NuGet--it's much easier to work with from an installation perspective.

Screenshot
Senior Analyst Technical Focus Applications
Commented:
public static T ToValueOrDefault<T>(this object obj,  T defaultValue = default(T))
        {
            if (obj == null)
            {
                return defaultValue;
            }

            return obj.ToString().To(defaultValue);
        }

var x = reader.GetValue(reader.GetOrdinal("column_name")).ToValueOrDefault<string>();