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

Answer 2

Did you check, SqlDataReader.IsDBNull Method? Probably something like:

if(myReader.IsDBNull(myReader.GetOrdinal("Truthfulness"))
theArticle.Truthfulness = string.Empty;
else
theArticle.Truthfulness = ((myReader.GetInt32(myReader.GetOrdinal("Truthfulness"))))

Answer 3

You know, I deal with this all the time in Oracle. To clean the code up, I wrote a set of extension methods to simplify the operation:

using System.Data.OracleClient;
public static class OracleDataReaderExtensions 
{
    public static int GetInt32(this OracleDataReader reader, string columnName, int defaultValue) 
    {
        return reader.GetInt32(reader.GetOrdinal(columnName)) != DbNull.Value ? 
               reader.GetInt32(reader.GetOrdinal(columnName)) : 
               defaultValue;
    }
}

Create a separate overload for each type you want to return. I primarily work with string, int, date, and decimal. Remember YAGNI (you don't need to work with every type supported by the reader, only those you actually use.)

An extension class like this for SQL Server is really easy to write, and will VASTLY simplify your work. Trust me on that. Would I lie to you? :)

Answer 4

Here is what we use on SQLServer and it works like a charm:

...

Dim X as Object = pbDr("TotAmt") 'dr is dim'ed as a DataReader

...

Public Function pbDr(ByVal drName As String) As Object

Dim SQLError As SqlClient.SqlException

Dim IsNull As Boolean

Dim Ordinal, DispNbr As Integer

Try
  Ordinal = dr.GetOrdinal(drName)
  IsNull = dr.IsDBNull(Ordinal)
  If IsNull Then
    Dim Dbtype As String = dr.GetFieldType(Ordinal).ToString
    If Dbtype = "System.String" Then
      Return ""
    ElseIf Dbtype = "System.Int32" _
     OrElse Dbtype = "System.Double" _
     OrElse Dbtype = "System.Decimal" _
     OrElse Dbtype = "System.Int16" Then
      Return 0
    Else
      MsgBox("Print This Screen And Send To Support" _
       & "pbdr-Object = " & Dbtype, MsgBoxStyle.Critical)
      Return ""
    End If
  Else
    Return dr(Ordinal)
  End If

Catch sqlerror
  Call DispSQLError(SQLError, "pbDr")
  pbDr = ""
End Try

End Function

Answer 5

IsDbNull(int) is usually much slower that using methods like GetSqlInt32 and then comparing to DBNull.Value or using it's own .IsNull Like:

    public static int Int32(this SqlDataReader r, int ord)
    {
        var t = r.GetSqlInt32(ord);
        return t.IsNull ? default(int) : t.Value;
    }

Tried a few template solutions but to no avail so far. The problem is that all Sql-types (SqlInt32 here) types are actually structs and while they all have .Value property C# doesn't have real templates to handle that. Also they have their own INullable interface which has only .IsNull and is not conpatible with Nyllable<>.

I suspect that one would need full set of Sql-types as C# templates or to add ICOnvertible to them in order to be able to have just one or two templated methods.

If someone has maybe an idea with a functional trick or two speak up :-)