How do I return a value to a sqldatareader if value is null?

Question

I am currently using a sql data reader (in vb.net) to extract an article object via a stored proc from a SQL Server 2008 database. Part of this object includes the two properties shown below:

theArticle.Truthfulness = ((myReader.GetInt32(myReader.GetOrdinal("Truthfulness"))))
theArticle.Relevance = ((myReader.GetInt32(myReader.GetOrdinal("Relevance"))))

My problem is that the Truthfulness and Relevance may return a null value and this is causing the function to fall over.

I think I understand why. I am asking for an integer value (getin32) and because null is returned it fails.

How do I accommodate the null value from the database so it does not fall over?

Answer 1

You can check whether or not a given ordinal position is null using .IsDBNull() and then do something - e.g. set your value to -1 or something:

int myOrdinal = myReader.GetOrdinal("Truthfullness");

if(myReader.IsDBNull(myOrdinal))
{
  theArticle.Truthfulness = -1;
}
else
{
  theArticle.Truthfulness = myReader.GetInt32(myOrdinal);
}

As Mike Hofer points out in his answer, you could also wrap all this logic into an extension method:

public static class SqlDataReaderExtensions 
{
    public static int SafeGetInt32(this SqlDataReader reader, 
                                   string columnName, int defaultValue) 
    {
        int ordinal = reader.GetOrdinal(columnName);

        if(!reader.IsDbNull(ordinal))
        {
           return reader.GetInt32(ordinal);
        } 
        else
        {
           return defaultValue;
        }
    }
}

and then just use that "SafeGetInt32" method instead:

  theArticle.Truthfulness = myReader.SafeGetInt32("Truthfullness", -1);

Marc