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?

marc_s · Accepted Answer · 2009-10-03 14:29:52Z

20

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

edited Oct 3, 2009 at 14:29

answered Oct 3, 2009 at 10:38

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mmmmmm Over a year ago

or catch the exception and deal with it

marc_s Over a year ago

Yes, you could do that too - but avoiding an exception is better than catching and handling one (in general)

MRG Over a year ago

@marc_s :Agreed with your comment on my answer. Deleting it. Thanks for making it clear. Your comment was "I don't tiink this will work, since if the db column is NULL, the .GetInt32() call will fail with an exception - you won't get back a NULL value which you can then feed into the "??" operator...."

Daniel Hilgarth Over a year ago

Nowadays you would probably return a nullable int: stackoverflow.com/a/53391141/572644

Kamran Khan · Accepted Answer · 2009-10-03 10:42:44Z

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"))))

answered Oct 3, 2009 at 10:42

Kamran Khan

9,9842 gold badges33 silver badges41 bronze badges

1 Comment

marc_s Over a year ago

well, string.Empty won't do much good if you're dealing with a int32 property......

Mike Hofer · Accepted Answer · 2009-10-03 13:20:48Z

1

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? :)

answered Oct 3, 2009 at 13:20

Mike Hofer

17.1k11 gold badges80 silver badges117 bronze badges

7 Comments

marc_s Over a year ago

I don't think this will work, since if the column in the database is NULL, then the call to GetInt32() will cause an exception. You cannot compare that GetInt32() call against DBNull.Value and react on that....

Mike Hofer Over a year ago

The reason it works is because we're using the short-circuiting ?: operator. If it's not null, we return it. Otherwise, we return the default value.

marc_s Over a year ago

actually, at least with SQL Server, it does NOT work - GetInt32() will throw an exception if the underlying db column contains NULL, and you cannot compare the GetInt32() call to DBNull.Value - VS2008 will complain and not even compile it.

marc_s Over a year ago

But I definitely like the idea of wrapping this up in an extension method - makes life a lot easier!

marc_s Over a year ago

I tried to use your approach and compare the output of reader.GetInt32() to DBNull.Value in SQL Server: all I get is this error message: Operator '!=' cannot be applied to operands of type 'int' and 'System.DBNull'

|

cyberenergy · Accepted Answer · 2012-11-07 10:54:29Z

1

This generic version may be of use:

    private T ValueOrDefault<T>(System.Data.IDataReader rdr, string columnName)
    {
        T vod = default(T);
        try
        {
            int idx = rdr.GetOrdinal(columnName);
            if (!rdr.IsDBNull(idx))
                return (T)rdr[idx];
        }
        catch (IndexOutOfRangeException) { }

        return vod;
    }

Could be extended to catch InvalidCastException, or use Convert.ChangeType instead of casting?

answered Nov 7, 2012 at 10:54

cyberenergy

3213 silver badges4 bronze badges

1 Comment

Blake Blackwell Over a year ago

I like this option except for the IndexOutOfRangeException catch statement. I would think that you'd want to know as quickly as possible if the schema structure has changed instead of pushing default values to other parts of your system.

ZXX · Accepted Answer · 2010-07-24 01:54:40Z

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 :-)

cdeszaq · Accepted Answer · 2011-12-21 14:18:00Z

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

Daniel Hilgarth · Accepted Answer · 2018-11-20 10:37:50Z

0

Nowadays, you probably want the null if the database returns it and as such you would use a Nullable<int>:

public static class Extensions
{
    public static int? GetNullableInt32(this SqlDataReader reader, int ordinal)
    {
        if (reader.IsDBNull(ordinal))
            return null;

        return reader.GetInt32(ordinal);
    }

    public static long? GetNullableInt64(this SqlDataReader reader, int ordinal)
    {
        if (reader.IsDBNull(ordinal))
            return null;

        return reader.GetInt64(ordinal);
    }
}

answered Nov 20, 2018 at 10:37

Daniel Hilgarth

175k41 gold badges346 silver badges450 bronze badges

Collectives™ on Stack Overflow

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

7 Answers 7

4 Comments

1 Comment

7 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

4 Comments

1 Comment

7 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related