CODEWITHSUNDEEP
sqlsql-servervb.netsql-server-2008
Cunners
Cunners

Reputation: 1286

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

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?

Upvotes: 6

Views: 25025

Answers (7)

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174309

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);
    }
}

Upvotes: 0

cyberenergy
cyberenergy

Reputation: 321

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?

Upvotes: 1

Price Brattin
Price Brattin

Reputation: 1

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

Upvotes: 0

ZXX
ZXX

Reputation: 4762

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

Upvotes: 0

marc_s
marc_s

Reputation: 754468

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

Upvotes: 20

Mike Hofer
Mike Hofer

Reputation: 17012

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

Upvotes: 1

Kamran Khan
Kamran Khan

Reputation: 9986

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

Upvotes: 2

Related Questions