How to safely assign DB values to object properties?

Question

I have some (a bit more than a little) code where object values from a DataReader are assigned to business object properties. This code is quite untidy to look at with lots of null coalescing, parsing, etc. I was wondering how to implement a tidier means of converting the object values from the DataReader to the appropriate values for the object properties.

One option is to have discrete conversion functions for each data type, where the data types on my object properties are limited to a small set of CLR primitive types. I don't use nullable types, as then I'll just be moving the untidiness to where these values are used elsewhere.

My second option is to create an extension method for each of the four or so data types used, limited to the niche namespace in which these assignments take place. Something like this rather simplistic example:

    public static void SafelyAssign(this string target, object value)
    {
        if (value is string)
        {
            target = (string)value ?? "";
        }
        else
        {
            target = value.ToString();
        }
    }

What other options do I have?

Answer 1

Rather than re-inventing the wheel, I would simply use.

oReader.GetString(oReader.GetOrdinal("MyColumn"))

which will get your value as the specific type. It is important to note though that you can still have problems with this if you have values that are nulls with this process.

Answer 2

A solution I liked was adopted from the CSLA.Net framework. Create a SafeDataReader which implements IDataReader. I will try and find a link but here's an example for how to get a safe int. This solution does not require the CSLA.Net framework, this is just how he solves the problem which I find really handy.

public class SafeDataReader : IDataReader, ISafeDataRecord
{
    /// <summary>
    /// Gets an integer from the datareader.
    /// </summary>
    /// <remarks>
    /// Returns 0 for null.
    /// </remarks>
    public int GetInt32(string name)
    {
        return GetInt32(_dataReader.GetOrdinal(name));
    }
    /// <summary>
    /// Gets an integer from the datareader.
    /// </summary>
    /// <remarks>
    /// Returns 0 for null.
    /// </remarks>
    public int GetInt32(int i)
    {
        if (_dataReader.IsDBNull(i))
            return 0;
        else
            return _dataReader.GetInt32(i);
    }

    public object this[string name]
    {
        get
        {
            object val = _dataReader[name];
            if (DBNull.Value.Equals(val))
                return null;
            else
                return val;
        }
    }
    public Nullable<T> GetNullable<T>(string name) where T:struct
    {
        int index = _dataReader.GetOrdinal(name);

        if (_dataReader.IsDBNull(index))
        {
            return null;
        }
        else
        {
            return new Nullable<T>((T)_dataReader[index]);
        }
    }
}

Edit: Ok so how do we use this safe data reader? So let's assume we have a cmd object which is all setup

   using (SafeDataReader reader=new SafeDataReader(cmd.ExecuteDataReader())
   {
       //Assume MyColumn was null here, this will still work because 
       //since a string can be null 
       string myString=reader["MyColumn"];

       //In this case myInt will be set to 0 if MyColumn is DBNull
       int myInt=reader.GetInt("MyColumn");

   }

You can also use the GetNullable method although, that is not part of his class, and I'm not sure where it came from, but it looks correct to me.

How portable is this? Well since it is implementing IDataReader it should be very portable; and should be able to be swapped in and out so long as the calling code doesn't expect DBNull.Value to be returned.

Answer 3

I would more likely extend SQLDataReader (example below for Int32)

// safe int32 assignment (using column name)
public static int GetInt32Safe(this SqlDataReader dr, string column)
{ 
    object value = dr[column];
    if (value != DBNull.Value)
    {
        return (int)value;
    }
    else
    {
        return 0;
    }
}

// safe int32 assignment (using column index)
public static int GetInt32Safe(this SqlDataReader dr, int colno)
{ 
    if (!dr.IsDBNull(colno))
    {
        return dr.GetInt32(colno);
    }
    else
    {
        return 0;
    }
}

Answer 4

I prefer using nullable types. The method below can be used for any type. You just need to supply the column name. If you are not using Nullable types, you can get rid of the IsNullable check.

public static bool IsNullableType(Type valueType)
{
    return (valueType.IsGenericType && 
        valueType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)));
}

public static T GetValue<T>(this IDataReader reader, string columnName)
{
    object value = reader[columnName];
    Type valueType = typeof(T);
    if (value != DBNull.Value)
    {
        if (!IsNullableType(valueType))
        {
            return (T)Convert.ChangeType(value, valueType);
        }
        else
        {
            NullableConverter nc = new NullableConverter(valueType);
            return (T)Convert.ChangeType(value, nc.UnderlyingType);
        }
    }            
    return default(T);
}

Answer 5

As an interim measure, I have created an extension method called AssignObjectValue on each of the five primitive types I assign to. This allows me to call the 'same' method on each assignment, regardless of the LHS type. I would have loved to be able to overload the = operator for this, but I'm happy for now. My code looks like something like this:

        cDoc.DocType.AssignObjectValue(dr["DocType"]);           // int
        cDoc.DocID.AssignObjectValue(dr["docID"]);
        cDoc.CustomerNo.AssignObjectValue(dr["customerNo"]);     // string
        cDoc.InvTitle.AssignObjectValue(dr["InvTitle"]);
        cDoc.TaxPercent.AssignObjectValue(dr["taxPercent"]);     // double
        cDoc.TaxTypeID = GetTaxTypeIDForDocType(cDoc.DocType);
        cDoc.Remarks.AssignObjectValue(dr["remarks"]);
        cDoc.Cost.AssignObjectValue(dr["cost"]);                 // double
        cDoc.InvDate.AssignObjectValue(dr["InvDate"]);           // date

Which is more or less where I wanted to be. I will eventually be doing the assignments in a loop, based on a mapping dictionary, instead of explicitly in code, so I think my 'half' solution is not bad for the time invested in a temporary fix.

Answer 6

How about this as an extension method for SqlDataReader:

public static T GetValueNotNull<T>(this SqlDataReader reader, object checkValue)
    {
        T outValue;
        if (checkValue == DBNull.Value)
            outValue = default(T);
        else
            outValue = (T)checkValue;
        return outValue;
    }

Rather than one for each primitive type you use a generic and if the value is DBNull you send back the default for the type specified. I would think this can be adjusted for any type of DataReader rather than SqlDataReader if necessary.