Dealing with null values from DB

Question

The db I am querying from is returning some null values. How do I safeguard against this and make sure the caller gets some data back.

The code I have is:

Using DataReader

        while (dr.Read())
        {
            vo = new PlacementVO();
            vo.PlacementID = dr.GetString(0);

If I use dataset, I can do it like this.

 obj.email = (row["email"] == DBNull.Value) ? String.Empty : Convert.ToString(row["email"]);

Thanks

Answer 1

There is IsDBNull(int ordinal) if you are using ordinals (which you are).

So:

string email = reader.IsDBNull(0) ? null : reader.GetString(0);

If you are working with string column names, then to use this you'll have to call GetOrdinal first, for example:

string GetSafeString(this IDataReader reader, string name)
{
    int index = reader.GetOrdinal(name);
    return reader.IsDBNull(0) ? null : reader.GetString(0);
}

Of course, it is faster to only look up ordinals once, not once per row.

A similar approach can be used for int? etc, or using a default instead of a null.

Answer 2

Another way is to add isnull(column which can be null , replacement when it is null) to the actual SQL query. Less code and works without doing anything in the client code.

Answer 3

Use a wrapper class to perform this functionality. An example can be found here. The CSLA framework I believe also has this functionality