Proper way to unbox database values

Question

I'm working with an older Oracle database, and I feel there's likely a better way to go about unboxing the values I retrieve from the database.

Currently, I have a static class full of different type-specific methods:

public static int? Int(object o)
{
    try
    {
     return (int?)Convert.ToInt32(o);
    }
    catch (Exception)
    {
     return null;
    }
}

..and so on for different types, but I feel there should be a better way? If I want to unbox a value, I do something along the lines of...

int i;
i = nvl.Int(dataRow["column"]); //In this instance, "column" is of a numeric database type

I thought about using a generic class to handle all the different types, but I couldn't really figure out the best way to go about that.

Any ideas?

Answer 1

I find helper methods such as the following useful in your scenario - testing for DBNull is more efficient than catching an Exception as in your example:

public static MyHelper
{
    public static Nullable<T> ToNullable<T>(object value) where T : struct
    {
        if (value == null) return null;
        if (Convert.IsDBNull(value)) return null;
        return (T) value;
    }

    public static string ToString(object value)
    {
        if (value == null) return null;
        if (Convert.IsDBNull(value)) return null;
        return (string)value;
    }
}

This works for the string and the usual primitive value types you will encounter (int, decimal, double, bool, DateTime).

It's slightly different from your example in that it casts rather than converts - but personally I prefer this. I.e. if the database column is NUMERIC (decimal), I'd rather be explicit if I wanted to convert the value to int, e.g.:

int? myIntValue = (int?) MyHelper.ToNullable<decimal>(reader["MyNumericColumn"]);

Answer 2

You could introduce simple model classes and map between them.

For example:

public class Customer
{
   public Customer(DataRow row) 
   {
      Name = row["Name"];
   }
   public Name { get; private set; }
}

Of course, to reduce duplicate code, you could create a base class for your model data classes.

Depending on effort you want to spend, you could go and use an ORM mapper NHibernate.